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...

Answers


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!


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

  1. You need a column list
  2. 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"


Need Your Help

Read xlsx file with POI (SXSSFWorkbook)

java apache-poi xlsx

I'm trying to do my first tests of reading large xlsx file with POI, but to do a simple test with a small file I fail to show the value of a cell.

Angular 4. Unexpected token export

javascript jquery node.js angular npm

Then i'm start the app by command ng serve in console i see error: