I have a table with identity column say:

                                                        
                                                            
                                                                create
                                                            
                                                            
                                                                 
                                                            
                                                            
                                                                table
                                                            
                                                            
                                                                 with_id 
                                                            
                                                            
                                                                (
                                                            
                                                            
                                                                 id int 
                                                            
                                                            
                                                                identity
                                                            
                                                            
                                                                (
                                                            
                                                            
                                                                1
                                                            
                                                            
                                                                ,
                                                            
                                                            
                                                                1
                                                            
                                                            
                                                                ),
                                                            
                                                            
                                                                 val varchar
                                                            
                                                            
                                                                (
                                                            
                                                            
                                                                30
                                                            
                                                            
                                                                )
                                                            
                                                            
                                                                 
                                                            
                                                            
                                                                );
                                                            
                                                        
                                                    

It's well known, that this

                                                        
                                                            
                                                                select
                                                            
                                                            
                                                                 
                                                            
                                                            
                                                                *
                                                            
                                                            
                                                                 
                                                            
                                                            
                                                                into
                                                            
                                                            
                                                                 copy_from_with_id_1 
                                                            
                                                            
                                                                from
                                                            
                                                            
                                                                 with_id
                                                            
                                                            
                                                                ;
                                                            
                                                        
                                                    

results in copy_from_with_id_1 with identity on id too.

The following stack overflow question mentions listing all columns explicitly.

Let's try

                                                        
                                                            
                                                                select
                                                            
                                                            
                                                                 id
                                                            
                                                            
                                                                ,
                                                            
                                                            
                                                                 val 
                                                            
                                                            
                                                                into
                                                            
                                                            
                                                                 copy_from_with_id_2 
                                                            
                                                            
                                                                from
                                                            
                                                            
                                                                 with_id
                                                            
                                                            
                                                                ;
                                                            
                                                        
                                                    

Oops, even in this case id is an identity column.

What I want is a table like

                                                        
                                                            
                                                                create
                                                            
                                                            
                                                                 
                                                            
                                                            
                                                                table
                                                            
                                                            
                                                                 without_id 
                                                            
                                                            
                                                                (
                                                            
                                                            
                                                                 id int
                                                            
                                                            
                                                                ,
                                                            
                                                            
                                                                 val varchar
                                                            
                                                            
                                                                (
                                                            
                                                            
                                                                30
                                                            
                                                            
                                                                )
                                                            
                                                            
                                                                 
                                                            
                                                            
                                                                );
                                                            
                                                        
                                                    

From Books Online

The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list. The IDENTITY property of a column is transferred except under the conditions defined in "Working with Identity Columns" in the Remarks section.

Down the page:

When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

  • The SELECT statement contains a join, GROUP BY clause, or aggregate function.
  • Multiple SELECT statements are joined by using UNION.
  • The identity column is listed more than one time in the select list.
  • The identity column is part of an expression.
  • The identity column is from a remote data source.

If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. If an identity column is required in the new table but such a column is not available, or you want a seed or increment value that is different than the source identity column, define the column in the select list using the IDENTITY function. See "Creating an identity column using the IDENTITY function" in the Examples section below.

So... you could theoretically get away with:

                                                                
                                                                    
                                                                        select
                                                                    
                                                                    
                                                                         id
                                                                    
                                                                    
                                                                        ,
                                                                    
                                                                    
                                                                         val 
                                                                    
                                                                    
                                                                        into
                                                                    
                                                                    
                                                                         copy_from_with_id_2 
                                                                    
                                                                    
                                                                        from
                                                                    
                                                                    
                                                                         with_id 
                                                                    
                                                                    
                                                                        union
                                                                    
                                                                    
                                                                         
                                                                    
                                                                    
                                                                        all
                                                                    
                                                                    
                                                                         
                                                                    
                                                                    
                                                                        select
                                                                    
                                                                    
                                                                         
                                                                    
                                                                    
                                                                        0
                                                                    
                                                                    
                                                                        ,
                                                                    
                                                                    
                                                                         
                                                                    
                                                                    
                                                                        'test_row'
                                                                    
                                                                    
                                                                         
                                                                    
                                                                    
                                                                        where
                                                                    
                                                                    
                                                                         
                                                                    
                                                                    
                                                                        1
                                                                    
                                                                    
                                                                         
                                                                    
                                                                    
                                                                        =
                                                                    
                                                                    
                                                                         
                                                                    
                                                                    
                                                                        0
                                                                    
                                                                    
                                                                        ;
                                                                    
                                                                
                                                            

It would be important to comment this code to explain it, lest it be removed the next time someone looks at it.

Inspired by Erics answer, I found the following solution which only depends on the table names and doesn't use any specific column name :

                                                                    
                                                                        
                                                                            select
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            *
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            into
                                                                        
                                                                        
                                                                             without_id 
                                                                        
                                                                        
                                                                            from
                                                                        
                                                                        
                                                                             with_id 
                                                                        
                                                                        
                                                                            where
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            1
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            =
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            
                                                                                Crossbody Medium Baggallini Pocket ebay cheap online for sale footlocker big discount sale online clearance cheapest price cheap popular UVyeUF
                                                                            
                                                                             0
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            union
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            all
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            select
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            *
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            from
                                                                        
                                                                        
                                                                             with_id 
                                                                        
                                                                        
                                                                            where
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            1
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            =
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            
                                                                                perfect sale online cheap popular Crossbody Medium Pocket Baggallini big discount sale online for sale footlocker shopping online original 5JnuXjW
                                                                            
                                                                             0
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            ;
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            insert
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            into
                                                                        
                                                                        
                                                                             without_id 
                                                                        
                                                                        
                                                                            select
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            *
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            from
                                                                        
                                                                        
                                                                            
                                                                                Medium Pocket Crossbody Baggallini buy cheap pay with paypal BfQiLd
                                                                            
                                                                             with_id
                                                                        
                                                                        
                                                                            ;
                                                                        
                                                                    
                                                                

Edit

It is even possible to improve this to

                                                                    
                                                                        
                                                                            select
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            *
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            into
                                                                        
                                                                        
                                                                             without_id 
                                                                        
                                                                        
                                                                            from
                                                                        
                                                                        
                                                                             with_id 
                                                                        
                                                                        
                                                                            union
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            all
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            select
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            *
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            from
                                                                        
                                                                        
                                                                             with_id 
                                                                        
                                                                        
                                                                            where
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            1
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            =
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            0
                                                                        
                                                                        
                                                                             
                                                                        
                                                                        
                                                                            ;
                                                                        
                                                                    
                                                                

You can use a join to create and populate the new table in one go:

                                                                        
                                                                            
                                                                                SELECT
                                                                            
                                                                            
                                                                                 t
                                                                            
                                                                            
                                                                                .*
                                                                            
                                                                            
                                                                                 
                                                                            
                                                                            
                                                                                INTO
                                                                            
                                                                            
                                                                                 dbo
                                                                            
                                                                            
                                                                                .
                                                                            
                                                                            
                                                                                NewTable 
                                                                            
                                                                            
                                                                                FROM
                                                                            
                                                                            
                                                                                 dbo
                                                                            
                                                                            
                                                                                .
                                                                            
                                                                            
                                                                                TableWithIdentity 
                                                                            
                                                                            
                                                                                AS
                                                                            
                                                                            
                                                                                 t 
                                                                            
                                                                            
                                                                                LEFT
                                                                            
                                                                            
                                                                                 
                                                                            
                                                                            
                                                                                JOIN
                                                                            
                                                                            
                                                                                 dbo
                                                                            
                                                                            
                                                                                .
                                                                            
                                                                            
                                                                                TableWithIdentity 
                                                                            
                                                                            
                                                                                ON
                                                                            
                                                                            
                                                                                 
                                                                            
                                                                            
                                                                                1
                                                                            
                                                                            
                                                                                 
                                                                            
                                                                            
                                                                                =
                                                                            
                                                                            
                                                                                 
                                                                            
                                                                            
                                                                                0
                                                                            
                                                                            
                                                                                 
                                                                            
                                                                            
                                                                                ;
                                                                            
                                                                        
                                                                    

Because of the 1 = 0 condition, the right side will have no matches and thus prevent duplication of the left side rows, and because this is an outer join, the left side rows will not be eliminated either. Finally, because this is a join, the IDENTITY property is eliminated.

Selecting just the left side columns, therefore, will produce an exact copy of dbo.TableWithIdentity data-wise only, i.e. with the IDENTITY property stripped off.

All that being said, Max Vernon has raised a valid point in a comment that is worth keeping in mind. If you look at the execution plan of the above query:

you will notice that the source table is mentioned in the execution plan just once. The other instance has been eliminated by the optimiser.

So, if the optimiser can correctly establish that the right side of the join is not needed in the plan, it should be reasonable to expect that in a future version of SQL Server it may be able to figure out that the IDENTITY property need not be removed either, since there is no longer another IDENTITY column in the source row set according to the query plan. That means that the above query might stop working as expected at some point.

But, as correctly noted by ypercubeᵀᴹ , so far the manual has explicitly been stating that if there is a join, the IDENTITY property is not preserved:

Crossbody Pocket Baggallini Medium Inexpensive cheap price zldw5qZNey When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless [...] [t]he SELECT statement contains a join.

So, as long as the manual keeps mentioning it, we can probably rest assured that the behaviour will stay the same.

Kudos to sale geniue stockist PUMA Enzo Strap Knit for cheap online enjoy cheap online discount looking for PItp0
and ypercubeᵀᴹ for bringing up a related topic in chat.

Try this code..

                                                                            
                                                                                
                                                                                    SELECT
                                                                                
                                                                                
                                                                                     isnull
                                                                                
                                                                                
                                                                                    (
                                                                                
                                                                                
                                                                                    
                                                                                        perfect sale online Baggallini Medium Crossbody Pocket for sale footlocker xSkakM
                                                                                    
                                                                                     Tablename_old
                                                                                
                                                                                
                                                                                    .
                                                                                
                                                                                
                                                                                    IDENTITYCOL
                                                                                
                                                                                
                                                                                     
                                                                                
                                                                                
                                                                                    +
                                                                                
                                                                                
                                                                                     
                                                                                
                                                                                
                                                                                    0
                                                                                
                                                                                
                                                                                    ,
                                                                                
                                                                                
                                                                                     
                                                                                
                                                                                
                                                                                    -1
                                                                                
                                                                                
                                                                                    )
                                                                                
                                                                                
                                                                                     
                                                                                
                                                                                
                                                                                    AS
                                                                                
                                                                                
                                                                                     
                                                                                
                                                                                
                                                                                    'New Identity Column'
                                                                                
                                                                                
                                                                                     
                                                                                
                                                                                
                                                                                    INTO
                                                                                
                                                                                
                                                                                     dbo
                                                                                
                                                                                
                                                                                    .
                                                                                
                                                                                
                                                                                    TableName_new 
                                                                                
                                                                                
                                                                                    FROM
                                                                                
                                                                                
                                                                                     dbo
                                                                                
                                                                                
                                                                                    .
                                                                                
                                                                                
                                                                                    TableName_old 
                                                                                
                                                                            
                                                                        

The ISNULL call ensures that the new column is created with NOT NULL nullability.

share Crossbody Baggallini Medium Pocket for sale footlocker Z4GBUa | improve this answer

Just to show a different way:

You can use a linked server .

                                                                                
                                                                                    
                                                                                        SELECT
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        *
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        INTO
                                                                                    
                                                                                    
                                                                                         without_id 
                                                                                    
                                                                                    
                                                                                        FROM
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        [
                                                                                    
                                                                                    
                                                                                        linked_server
                                                                                    
                                                                                    
                                                                                        ].[
                                                                                    
                                                                                    
                                                                                        source_db
                                                                                    
                                                                                    
                                                                                        ].
                                                                                    
                                                                                    
                                                                                        dbo
                                                                                    
                                                                                    
                                                                                        .[
                                                                                    
                                                                                    
                                                                                        with_id
                                                                                    
                                                                                    
                                                                                        ];
                                                                                    
                                                                                
                                                                            

You can temporarily create a linked server to the local server using this:

                                                                                
                                                                                    
                                                                                        DECLARE
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        LocalServer SYSNAME 
                                                                                    
                                                                                    
                                                                                        SET
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        
                                                                                            Crossbody Baggallini Pocket Medium cheap popular o2nzftn
                                                                                        
                                                                                         LocalServer
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @@
                                                                                    
                                                                                    
                                                                                        SERVERNAME
                                                                                    
                                                                                    
                                                                                        ;
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        EXEC
                                                                                    
                                                                                    
                                                                                         master
                                                                                    
                                                                                    
                                                                                        .
                                                                                    
                                                                                    
                                                                                        dbo
                                                                                    
                                                                                    
                                                                                        .
                                                                                    
                                                                                    
                                                                                        sp_addlinkedserver 
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        server 
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                         N
                                                                                    
                                                                                    
                                                                                        'localserver'
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        ,
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        srvproduct 
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        ''
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        ,
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        provider 
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        'SQLNCLI'
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        ,
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        datasrc 
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        LocalServer
                                                                                    
                                                                                    
                                                                                        ;
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        EXEC
                                                                                    
                                                                                    
                                                                                         master
                                                                                    
                                                                                    
                                                                                        .
                                                                                    
                                                                                    
                                                                                        dbo
                                                                                    
                                                                                    
                                                                                        .
                                                                                    
                                                                                    
                                                                                        sp_addlinkedsrvlogin 
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        rmtsrvname
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                        N
                                                                                    
                                                                                    
                                                                                        'localserver'
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        ,
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        useself 
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                         N
                                                                                    
                                                                                    
                                                                                        'True'
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        ,
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        locallogin 
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        NULL
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        ,
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        rmtuser 
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        NULL
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        ,
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        rmtpassword 
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        NULL
                                                                                    
                                                                                    
                                                                                        ;
                                                                                    
                                                                                
                                                                            

At which point, you'd run the select * into code, referencing the localserver linked server four-part-name:

                                                                                
                                                                                    
                                                                                        SELECT
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        *
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        INTO
                                                                                    
                                                                                    
                                                                                         without_id 
                                                                                    
                                                                                    
                                                                                        FROM
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        [
                                                                                    
                                                                                    
                                                                                        localserver
                                                                                    
                                                                                    
                                                                                        ].[
                                                                                    
                                                                                    
                                                                                        source_db
                                                                                    
                                                                                    
                                                                                        ].
                                                                                    
                                                                                    
                                                                                        dbo
                                                                                    
                                                                                    
                                                                                        .[
                                                                                    
                                                                                    
                                                                                        with_id
                                                                                    
                                                                                    
                                                                                        ];
                                                                                    
                                                                                
                                                                            

After that completes, clean up the localserver linked server with this:

                                                                                
                                                                                    
                                                                                        EXEC
                                                                                    
                                                                                    
                                                                                        
                                                                                            buy cheap pay with paypal big discount sale online Pocket Medium Crossbody Baggallini WLuLrWfsG
                                                                                        
                                                                                         sp_dropserver
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        server 
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        'localserver'
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        ,
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        @
                                                                                    
                                                                                    
                                                                                        droplogins 
                                                                                    
                                                                                    
                                                                                        =
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        'droplogins'
                                                                                    
                                                                                    
                                                                                        ;
                                                                                    
                                                                                
                                                                            

Or, you could use OPENQUERY syntax

                                                                                
                                                                                    
                                                                                        SELECT
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        *
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        INTO
                                                                                    
                                                                                    
                                                                                         without_id 
                                                                                    
                                                                                    
                                                                                        FROM
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        OPENQUERY
                                                                                    
                                                                                    
                                                                                        ([
                                                                                    
                                                                                    
                                                                                        linked_server
                                                                                    
                                                                                    
                                                                                        ],
                                                                                    
                                                                                    
                                                                                         
                                                                                    
                                                                                    
                                                                                        'SELECT * FROM [source_db].dbo.[with_id]'
                                                                                    
                                                                                    
                                                                                        );
                                                                                    
                                                                                
                                                                            
Boot Contrast Schmenger amp Kennel Stitch recommend for sale eH3cW51TZu
| improve this answer