Copy a row within the same table
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...
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)
I'm no MS-SQL user, but in all other databases I worked with, I've never seen a way that doesn't involve manually listing all columns but the auto incremented one.
insert into mytable (some, columns, but, not, the, id) select some, columns, but, not, the, id from mytable where id = someid
- You need a column list
- Why insert the same value again?
A big help is to use the automatic script-creater in SQL-Manager:
In my case have over 60 columns, its a pain to write every single one of them. So, right click on the table and select: "Script table as" -> "Select to" -> "New Query Window"