transfer column identity with select into statement

Hello,

I see that when I transfer tables with select into statement, the column identity are not created.
How can I do this?

Thanks

bibi
bibi92Asked:
Who is Participating?
 
blandyukConnect With a Mentor Commented:
As above, this is what you need as long as you don't have records in the destination table with the same PK IDs:

SET IDENTITY_INSERT [table] ON
INSERT INTO ....
SELECT ....
SET IDENTITY_INSERT [table] OFF
0
 
BanthorCommented:
Create the table.
Set identity_insert ON
Insert into NewTable
Select * from oldTable
Set identity_insert off
 
0
 
bibi92Author Commented:
declare @sql varchar(max)
How can I modify this :

declare @sql2 varchar(max)
CREATE TABLE stmt (sql varchar(max) )
set @sql = 'declare @sql varchar(max) select + ''SELECT * INTO '+ @SchDest + '.[''+TABLE_NAME+''] '
set @sql=@sql+' FROM ['+@Srvsource + '].'+ @Dbsource + '.' +@Schsource + '.[''+TABLE_NAME+''] '''
set @sql=@sql+' FROM ['+@Srvsource + '].'+ @Dbsource + '.INFORMATION_SCHEMA.TABLES '
set @sql=@sql+' WHERE TABLE_SCHEMA = '''+@Schsource+''' execute (@sql)'
insert into stmt execute (@sql)
SELECT @sql2 = coalesce(@sql2, '') + CAST (sql AS VARCHAR(MAX)) + ';' from stmt
print (@sql2)

Thanks

Bibi
exec (@sql2)
drop table stmt
0
 
Saqib KhanSenior DeveloperCommented:
you can create a new Variable just to Hold Identity Value

Select
       newIdentity = Identity(int,1,1),
       ColumnA
into TableNEW
From TableX
0
 
bibi92Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.