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!

wow - you have a table with 130 columns that will increase! Sorry but wow.... — Rippo, Jul 14 '10 at 18:22
I know, we should think about normalizing this table... Btw, they are 175 colums. // Thanks for all your comments — laurens, Jul 15 '10 at 10:52
possible duplicate of [SQL exclude a column using SELECT \* \[except columnA\] FROM tableA?](http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea) — zloctb, Jul 03 '15 at 05:56

score 2 · Answer 1 · answered Jul 14 '10 at 18:25

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.

score 2 · Answer 2 · answered Jul 14 '10 at 18:27

2

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

answered Jul 14 '10 at 18:27

Fosco

38,138
7
87
101

Assuming that `nc_id` is an `identity` column this won't work without `identity_insert` on. If you turn on `identity_insert` then you're tossing out all of the benefits of having an `identity` column (and would be a really bad idea). – Donnie Jul 14 '10 at 18:31
This is the general approach that I would suggest. Try setting nc_ID to null (in the temp table). BTW, SQL programming tends to be tedious rather than tricky. If you feel the need to be really clever, you are probably overlooking a more prosaic approach. – SeaDrive Jul 14 '10 at 19:09
1

On second thought, you may need to do a DROP COLUMN on the temp table to get rid of the identity column. – SeaDrive Jul 14 '10 at 19:16
1

This approach of creating a temp table, dropping the id column, and then inserting does work. I did it today. – SeaDrive Jul 16 '10 at 16:21

Martin Smith · Accepted Answer · 2010-07-14T19:43:33.467

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

This does exactly what I needed! Just inserted my TableName and it was done. Thanks!! Cheers — laurens, Jul 15 '10 at 10:53

score 0 · Answer 4 · answered Jul 14 '10 at 18:23

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.

The OP probably has a requirement for User-Defined fields. – Robert Harvey Jul 14 '10 at 18:31 — Robert Harvey, Jul 14 '10 at 18:31

score 0 · Answer 5 · answered Jul 14 '10 at 18:30

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.

duplicate row -- select all columns except one

5 Answers5