duplicate row -- select all columns except one

Question

I know this question could have passed a few times here but I haven't really found a workaround whatsoever. Also could my question be a little different.

Situation is: I have a table with 130 columns (+100.000 rows), and the number of columns will even increase in the future. For this reason I would like to define the selection of the columns as [select all but one]

AS I want to duplicate a row with [select all] I get an error on the primary key -> the nc_ID because -of course- it tries to duplicated this value also instead of increasing it by one.

It is obvious that the column that I want to excluded from the selection is the first one, the nc_ID. I read and heard that this kind of [select all but one] solutions can only be accomplished with dynamic sql. If so could someone explain me via a piece if sql code?

INSERT into TableName (all columns except the first *nc_ID*) Select * From TableName Where nc_ID=12345;

Thanks in advance!

Answer 1

Were you asking how to do this in dynamic SQL? - Something like the following should work.

(Obligatory link to The Curse and Blessings of Dynamic SQL)

DECLARE @TableName varchar(500)
DECLARE @nc_ID INT
SET @nc_ID = 12345
SET @TableName = '[dbo].[TableName]'

DECLARE @Dynsql nvarchar(max)

SELECT @Dynsql = ISNULL(@Dynsql + ',','') + QUOTENAME(name) FROM sys.columns
WHERE object_id = object_id(@TableName) and is_identity = 0 and is_computed = 0
ORDER BY column_id

IF @@ROWCOUNT=0
RAISERROR('%s not found in sys.columns',16,1, @TableName)

SET @Dynsql = 'INSERT INTO  ' + @TableName + '
           ('+ @Dynsql +')
SELECT '+ @Dynsql +'
  FROM ' + @TableName + '
WHERE  nc_ID = @nc_ID'

EXEC sp_executesql @Dynsql, N'@nc_ID int',@nc_ID=@nc_ID

Answer 2

You will need to enumerate the list of columns.

You will then have to identify the identity column, so that you can exclude it from the list of columns.

For safety reasons (to avoid SQL injection), you should use the remaining list of columns to build up a parameter set to execute your insert.