ChrisM@Work (8/17/2012)
Phil Parkin (8/17/2012)
Missing something, I'm afraid.SELECT ... INTO
always creates a new table. When it creates that table, uses the data types of the source columns, but not primary keys or identity definitions. So, in your example, Archive.MyTest.Id will not be an IDENTITY column, despite your comment to the contrary. It's just a bog-standard INT.
Sorry Phil but this is incorrect - here's the generated script for table Archive.MyTest:
CREATE TABLE [Archive].[MyTest]([Id] [int] IDENTITY(1,1) NOT NULL,
[Descript] [varchar](30) NULL
) ON [PRIMARY]
Archive.MyTest.Id is an IDENTITY column.
That will teach me to not try and answer questions before drinking coffee, thanks Chris.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.