SQL -How to add an automatic incremental identifier in an automatically generated temporary table

Question

I have a query as shown below and it generates a temporary table automatically based on the parameter. Thus, the number of columns in this table may be different. Now I need to add the Auto incremental id column to this table. How am i doing this?

SELECT @SourceFields INTO ##StoreSourceInfo FROM testdb.dbo.@SourceTable 

Note: 1) The source field number and table name are passed using the @SourceFields & @SourceTable . 2) Thus, the number of columns can be changed in the table ## StoreSourceInfo.

Current result:

select * from ##StoreSourceInfo shows only the available column.

Expected result: select * from ##StoreSourceInfo will display an additional column with an automatic incremental identifier and all other columns available in the temp table.

I hope you catch me. Thanks in advance.

Answer 1

Use the identification function. See Link for an example. http://msdn.microsoft.com/en-us/library/ms189838.aspx

Answer 2

 SELECT IDENTITY(INT, 1, 1) AS id INTO #Temptable FROM User 

Answer 3

You can use row_number function

 Select ROW_NUMBER() over (order by T.field1) rownum , T.field1, T.field2 into #temp1 from @Table T 

Answer 4

You should try the following query to get your excluded result in order to add an additional auto-increase column:

 SELECT IDENTITY(INT, 1,1) AS Rank, @SourceFields INTO ##StoreSourceInfo FROM testdb.dbo.@SourceTable 

Uses the IDENTITY function ...