Sql – INSERT INTO a temp table, and have an IDENTITY field created, without first declaring the temp table

sqlsql server

I need to select a bunch of data into a temp table to then do some secondary calculations; To help make it work more efficiently, I would like to have an IDENTITY column on that table. I know I could declare the table first with an identity, then insert the rest of the data into it, but is there a way to do it in 1 step?

Best Answer

Oh ye of little faith:

SELECT *, IDENTITY( int ) AS idcol
  INTO #newtable
  FROM oldtable

http://msdn.microsoft.com/en-us/library/aa933208(SQL.80).aspx

Related Solutions

SQL Server: Is it possible to insert into two tables at the same time

In one statement: No.

In one transaction: Yes

BEGIN TRANSACTION
   DECLARE @DataID int;
   INSERT INTO DataTable (Column1 ...) VALUES (....);
   SELECT @DataID = scope_identity();
   INSERT INTO LinkTable VALUES (@ObjectID, @DataID);
COMMIT

The good news is that the above code is also guaranteed to be atomic, and can be sent to the server from a client application with one sql string in a single function call as if it were one statement. You could also apply a trigger to one table to get the effect of a single insert. However, it's ultimately still two statements and you probably don't want to run the trigger for every insert.

Sql-server – Select columns from result set of stored procedure

Can you split up the query? Insert the stored proc results into a table variable or a temp table. Then, select the 2 columns from the table variable.

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

Best Answer

Related Solutions

SQL Server: Is it possible to insert into two tables at the same time

Sql-server – Select columns from result set of stored procedure

Related Topic