CODEWITHSUNDEEP
sqlsql-servert-sql
Serdia
Serdia

Reputation: 4428

How to create sequential ID number when using SELECT * INTO

I have a complex stored procedure that is collecting data from many tables and inserting it into MyTable. It inserts over 1.5 M records.

What would be the most efficient way to create sequential ID number when populating MyTable

The structure of the table looks like this:

IF OBJECT_ID ('MyTable', 'U') IS NOT NULL 
  DROP TABLE MyTable;

SELECT * 
INTO MyTable 
FROM 
    (SELECT 
         col1, col2, col3
     FROM   
         Table1 
     INNER JOIN 
         Table2 ON...
     INNER JOIN 
         Table3 ON...
     INNER JOIN 
         Table4 ON...
     WHERE  
         Condition1,
         Condition2) T

Upvotes: 0

Views: 1255

Answers (3)

Alan Burstein
Alan Burstein

Reputation: 7918

As Gabri demonstrated, you can use IDENTITY with a SELECT INTO statement. This will make that column and IDENTITY column. If you don't want it to be an IDENTITY column you can use ROW_NUMBER; this will work on any SQL 2005+ system.

SELECT ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), * 
INTO MyTable FROM 
(
  SELECT 
          col1,
          col2,
          col3
  FROM    Table1 INNER JOIN Table2 ON...
          Table3 INNER JOIN Table4 ON...
  WHERE   Condition1, Condition2
) T;

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

I think simple row_number() is helpfull as below:

IF OBJECT_ID ('MyTable', 'U') IS NOT NULL 
   DROP TABLE MyTable;

SELECT * INTO MyTable FROM (
                        SELECT 
                                RowNum = Row_Number() over (order by (Select NULL)) --Instead you can generate based on any column in the table
                                col1,
                                col2,
                                col3
                        FROM    Table1 INNER JOIN Table2 ON...
                                Table3 INNER JOIN Table4 ON...
                        WHERE   Condition1,
                                Condition2
                            ) T

Upvotes: 2

Gabri T
Gabri T

Reputation: 454

SELECT ID = IDENTITY(INT, 1, 1),* INTO MyTable FROM (

                            SELECT 
                                    col1,
                                    col2,
                                    col3
                            FROM    Table1 INNER JOIN Table2 ON...
                                    Table3 INNER JOIN Table4 ON...
                            WHERE   Condition1,
                                    Condition2
                                ) T

ID = IDENTITY(INT, 1, 1) will create an identity ID that auto increments, i didnt test the code

Upvotes: 2

Related Questions