CODEWITHSUNDEEP
sqlsql-server-2008-r2identity-column
atiyar
atiyar

Reputation: 8305

copying data from one table to another except the Identity column

Is there any way to copy all column values from one table to another except the Identity column, without mentioning all the rest of the column names?

I have a table with 63 columns. I created a temporary table with - 

SELECT * INTO #TmpWide FROM WideTable WHERE 1 = 0

Now I want to copy some data from WideTable to #TmpWide. I need all the columns of WideTable except the Identity Id column, because I want the copied data to have their own sequential Id's in #TmpWide from 1 to onward. Is it possible without mentioning the (63-1) column names?

Upvotes: 0

Views: 1779

Answers (2)

sqlHippo
sqlHippo

Reputation: 101

There isn't a way to do that, but also it is a bad idea to use * in a situation like this. If WideTable changes you will be forced to change the stored procedures that SELECT * from it. I wrote hundreds of stored procs like this and all it did was create nightmares I'm still dealing with today. Good luck.

Upvotes: 1

lc.
lc.

Reputation: 116498

You could try dropping the column after the table is created:

SELECT * INTO #TmpWide FROM WideTable WHERE 1=0
ALTER TABLE #TmpWide DROP COLUMN [Id]

This does feel a little ugly or hack-y, but it should do the trick.

Upvotes: 1

Related Questions