SELECT INTO and IDENTITY columns

Question

SELECT INTO and IDENTITY columns

dimp

SSC Veteran

Points: 247
More actions
June 18, 2003 at 8:28 pm

#81072

Hi All,
I'm trying to replicate a table using SELECT INTO and then add an IDENTITY column to the replicated table. Problem is though that if the original table contains an IDENTITY column that this is also inherited by the replicated table and then the addition of the new IDENTITY column causes a failure (no more than 2 IDENTITY cols per table).
Ideally I would like to keep all original columns but drop their IDENTITY properties to allow the new IDENTITY column to be added.
Any ideas short of creating a cursor that loops through the original tables metadata column by column to create a copy would be greatly appreciated?
Cheers
Jim

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

neder SSC Eights! Points: 888 More actions · Answer 1

you may want to change the way you are recreating your table. try creating the target table schema with your additional identity column first, then move the data using 'insert into' command.

hope this helps

dimp SSC Veteran Points: 247 More actions · Answer 2

neder,

I use dynamic SQL here and the procedure is designed to work by feeding any table name to it. To create a replica first I would require knowledge of each source column and its type then to dynamically build a create table statement. I'm trying to stay away from this.

The select into statement is a great way of copying table schemas without having to know anything about the original table so I'm hoping I can keep that level of transparency without getting too complicated.

Thanks for your idea though ... and it may be that I have to resort to this anyway.

Jim

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4105 More actions · Answer 3

--Try this Procedure

Create Procedure CreateTable (@Table sysname, @Target sysname, @Condition varchar(1000))

As

Declare @SQL varchar(8000)

Select @SQL =''

Select @SQL = @SQL +', '+

Case

when AutoVal is NOT NULL then 'cONVERT(int, ' + NAME+') as '+ Name

else name end

from SYScOLUMNS where id = object_id(@Table) order by ColId

Select @SQL = 'Select '+ SUBSTRING(@SQL, 3, LEN(@SQL)-2)

Select @SQL =@SQL + ' into '+ @Target +' From '+@Table

IF @Condition<>''

Select @SQL = @SQL + ' Where '+@Condition

Execute(@SQL)

--Print @SQL

Select @SQL ='Alter Table '+@Target +' Add Row_ID int NOT NULL Identity(1,1)'

Execute(@SQL)

--Print @SQL

GO

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

dimp SSC Veteran Points: 247 More actions · Answer 4

Preethi,

Yes! Of course!

Thankyou very much

Have a great day/evening

Jim