You can use a join to create and populate the new table in one go:
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
data-wise only, i.e. with the IDENTITY property stripped off.
All that being said,
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
, so far
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.
sale geniue stockist PUMA Enzo Strap Knit for cheap online enjoy cheap online discount looking for PItp0
for bringing up a related topic in chat.