Copy the row to the same table

Question

Given the 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 list of columns? Is there no way to keep it universal? For example. if the developer adds a column later, that column will not go into this routine, and even it will fail if null is not allowed ...

Answer 1

Well, there is actually a more general 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) 

Hooray!

Answer 2

I am not an MS-SQL user, but in all the other databases that I worked with, I never saw a way that would not include manually listing all columns, but with automatic addition.

 insert into mytable (some, columns, but, not, the, id) select some, columns, but, not, the, id from mytable where id = someid 

Answer 3

• You need a list of columns
• Why insert the same value again?

Answer 4

A great help is to use the automatic script-creater in SQL-Manager:

In my case, there are more than 60 columns, it is a pain to write each of them. So, right-click on the table and select: "Script table as" → "Select for" → "New query window"