CODEWITHSUNDEEP
sql-serversql-server-2005sql
Seth
Seth

Reputation: 578

How to copy a row with every column except identity column (SQL Server 2005)

My code:

SELECT * INTO #t FROM CTABLE WHERE CID = @cid   --get data, put into a temp table


ALTER TABLE #t
DROP COLUMN CID       -- remove primary key column CID


INSERT INTO CTABLE SELECT * FROM #t   -- insert record to table
DROP TABLE #t                                -- drop temp table

The error is:

Msg 8101,
An explicit value for the identity column in table 'CTABLE' can only 
be specified when a column list is used and IDENTITY_INSERT is ON.

And I did set 

SET IDENTITY_INSERT CTABLE OFF
GO

Upvotes: 12

Views: 13155

Answers (7)

anon
anon

Reputation: 

DECLARE 
    @cid INT,
    @o   INT,
    @t   NVARCHAR(255),
    @c   NVARCHAR(MAX),
    @sql NVARCHAR(MAX);

SELECT
    @cid = 10,
    @t   = N'dbo.CTABLE',
    @o   = OBJECT_ID(@t);

SELECT @c = STRING_AGG(QUOTENAME(name), ',')
    FROM sys.columns
    WHERE [object_id] = @o
    AND is_identity = 0;

SET @sql = 'SELECT ' + @c + ' INTO #t 
    FROM ' + @t + ' WHERE CID = @cid;

    INSERT ' + @t + '('+ @c + ') 
    SELECT ' + @c + ' FROM #t;' 

PRINT @sql;
-- exec sp_executeSQL @sql,
--  N'@cid int',
--  @cid = @cid;

However it seems much easier to just build the following SQL and avoid the #temp table altogether:

SET @sql = 'INSERT ' + @t + '(' + @c + ') 
    SELECT ' + @c + ' FROM ' + @t + '
    WHERE CID = @cid;';

PRINT @sql;
-- exec sp_executeSQL @sql,
--  N'@cid int',
--  @cid = @cid;

Upvotes: 11

komodosp
komodosp

Reputation: 3616

If using SQL Server Management Studio and your problems you have too many fields to type them all out except the identity column, then right click on the table and click "Script table as" / "Select To" / "New Query Window".

This will provide a list of fields that you can copy & paste into your own query and then just remove the identity column.

Upvotes: 1

Chandu
Chandu

Reputation: 82903

Try this:

SELECT * INTO #t FROM CTABLE WHERE CID = @cid
ALTER TABLE #t
DROP COLUMN CID

INSERT CTABLE --Notice that INTO is removed here.
SELECT top(1) * FROM #t
DROP TABLE #t

Test Script(Tested in SQL 2005):

CREATE TABLE #TestIDNT
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    TITLE   VARCHAR(20)
)

INSERT #TestIDNT
SELECT 'Cybenate'

Upvotes: 5

jveazey
jveazey

Reputation: 5458

Here's an example to dynamically build a list of columns - excluding the primary key columns - and execute the INSERT

declare @tablename nvarchar(100), @column nvarchar(100), @cid int, @sql nvarchar(max)
set @tablename = N'ctable'
set @cid = 1
set @sql = N''

declare example cursor for

select column_name
  from information_schema.columns
 where table_name = @tablename 
   and column_name not in (
        select column_name
          from information_schema.key_column_usage
         where constraint_name in (select constraint_name from information_schema.table_constraints)
           and table_name = @tablename
)

open example
fetch next from example into @column
while @@fetch_status = 0
begin
    set @sql = @sql + N'[' + @column + N'],'
    fetch next from example into @column
end
set @sql = substring(@sql, 1, len(@sql)-1)
close example
deallocate example
set @sql = N'insert into ' + @tablename + '(' + @sql + N') select top(1) ' + @sql + ' from #t'
--select @sql
exec sp_executesql @sql

Upvotes: 1

Derek
Derek

Reputation: 23228

You can't do this:

INSERT INTO CTABLE SELECT top(1) * FROM #t

Because the column listings aren't the same. You've dropped the PK column from #t, so you have 1 less column in #t than in CTABLE. This is the equivalent of the following:

INSERT INTO CTABLE(pk, col1, col2, col3, ...) 
select top(1) col1, col2, col3, ...
from #t

This wouldn't work for obvious reasons. Similarly, you aren't going to be able to specify the * wildcard to do the insert if you're not inserting all of the columns. The only way to do the insert without including the PK is to specify every column. You can generate a list of columns through dynamic sql, but you'll have to specify them one way or another.

Upvotes: 0

Andriy M
Andriy M

Reputation: 77677

Try invoking the INSERT statement with EXEC:

SELECT * INTO #t FROM CTABLE WHERE CID = @cid

ALTER TABLE #t
DROP COLUMN CID

EXEC('INSERT INTO CTABLE SELECT top(1) * FROM #t')
DROP TABLE #t

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85056

Try specifying the columns:

INSERT INTO CTABLE 
(col2, col3, col4)
SELECT col2, col3, col4
FROM #t

Seems like it might be thinking you are trying to insert into the PK field since you are not explicitly defining the columns to insert into. If Identity insert is off and you specify the non-pk columns then you shouldn't get that error.

Upvotes: 1

Related Questions