I am maintaining an existing app that used Create Table statements to
generate temp tables (within sprocs). The columns defs are taken from the
actual tables in use at the time. As those tables change, the sprocs start
erroring.

My idea was to use a dummy Select INTO .. FROM statement to create the temp
tables instead. Since the original table had an identity column (used
everywhere in this app), I then tried to set IDENTITY_INSERT ON before
calling routines that poulate the temp table. I'm getting the error
"explicit value must be ID'd for Identity column...".

Anybody know how to work around this?

My code looks like:


DROP TABLE #temp
SELECT TOP 1 *
INTO #temp
FROM MyRealTable
WHERE 1=2

SELECT * FROM #temp
set identity_insert #temp on

INSERT INTO #DSLTemp
EXEC sproc_that_returns_table_row_with_idenity_column

Thanks for any tips

Stop mimicking magnetic tape scratch files in SQL. You have the
sequential record number mimicked with IDENTITY, a totally non-
relational "feature"; you are mimicking an empty magnetic tape by
defining the "file" on the fly at run time.

Re-write this mess with CTEs and derived tables. And the bum who
stuck you with it.

Mamba (gw (AT) nottoday (DOT) net) writes:
The answer will not be any different than those you got in .programming.

You will need to at some point to enumerate all columns, like it or not.

Using IDENTITY often means pain.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Yet again you need to be told.

Statistics are not held on CTE's nor derived tables so you can get a very
poor plan when using these constructs.

Temporary tables on the other hand have statistics and the optimiser can
recompile statements to give better plans.

You need to test both methods and use the one that gives the performance and
supportability you require.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]