sql-server – 如何使用除标识列之外的每一列复制行(SQL Server

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

解决方法

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 = COALESCE(@c,'') + ',' + QUOTENAME(name)
    FROM sys.columns
    WHERE [object_id] = @o
    AND is_identity = 0;

SELECT @c = STUFF(@c,1,'');

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

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

PRINT @sql;
-- exec sp_executeSQL @sql;

但是,构建以下SQL并完全避免#temp表似乎要容易得多：

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

PRINT @sql;
-- exec sp_executeSQL @sql;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!