我的代码：
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;
