sql-server - 如何复制包含除标识列之外的每一列的行 (SQL Server 2005)

Question

我的代码:

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

错误是:

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.

我确实设置了

SET IDENTITY_INSERT CTABLE OFF
GO

Answer 1

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;

但是，构建以下 SQL 并完全避免使用 #temp 表似乎要容易得多:

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

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