Copy a row within the same table

Question

Given following SQL (MS SQL 2008):

SET IDENTITY_INSERT MyTable ON
GO
INSERT INTO MyTable
  SELECT * FROM MyTable
  WHERE Id = @SomeId
GO
SET IDENTITY_INSERT MyTable OFF

This results in: An explicit value for the identity column in table 'MyTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Well, do I really need to define a column list? Is there no way to keep it generic? E.g. if a developer adds a column later on, this column won't make it into this routine, and it even will fail if null is not allowed...

Answer 1

Well, there is actually a more generic way, this works well without knowing the columns:

DECLARE @Tablename NVARCHAR(255) = 'MyTable'
DECLARE @IdColumn NVARCHAR(255) = 'MyTableId'  -- Primarykey-Column, will be ignored
DECLARE @IdToCopyFrom VARCHAR(255) = '33'  -- Copy from this ID

DECLARE @ColName NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX) = ''

DECLARE Table_Cursor CURSOR FOR
SELECT [B].[Name]
FROM SYSOBJECTS AS [A], SYSCOLUMNS AS [B]
WHERE [A].[ID] = [B].[ID] AND A.name = @Tablename

OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @ColName 
WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- Loop through all columns and link them into the Sql-string (except the PK-column)
    DECLARE @SkipComma BIT = 0
    IF (@ColName <> @IdColumn)
        SET @SQL = @SQL + @ColName
    ELSE
        SET @SkipComma = 1
    FETCH NEXT FROM Table_Cursor INTO @ColName 
    IF (@SkipComma = 0 AND @@FETCH_STATUS = 0)
        SET @SQL = @SQL + ','
    END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor 

SET @SQL = 'INSERT INTO ' + @Tablename + '(' + @SQL + ')' +
    ' SELECT ' + @SQL + ' FROM ' + @Tablename +
    ' WHERE ' + @IdColumn + ' = ' + @IdToCopyFrom
PRINT @SQL
EXEC(@SQL)

Cheers!