duplicate row -- select all columns except one

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 开发者_Go百科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!

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

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.

How about:

SELECT * INTO #MYTEMP FROM TableName WHERE nc_ID = 12345

UPDATE #MYTEMP SET nc_ID = nc_ID + 1;   -- or some other calculation or queried value

INSERT INTO TableName 
 SELECT * FROM #MYTEMP

DROP TABLE #MYTEMP

You have to write code to manually populate the column names. SQL has no support for "all columns except".

130 columns is verging on the insane, and the fact that the column count is increasing says to me that you should really sit down and consider your schema. This might be OK for a data warehouse-style denormalized table, but even still I strongly suggest sitting down and giving your schema a good think and making sure that there's not a (much) better way of going about things.

When using the query designer in Managment Studio, and you make a simple query like

SELECT * FROM Table

The management studio rewrites the SQL to explicitly name all of the columns. You can then delete the PK from that list.

If you make this your "select all but the PK" query, and use this from all other queries, then you have just one query to update.