怎样用sql语句复制表table1到表table2的同时复制主键

来源：互联网发布：2017网络电影大全编辑：程序博客网时间：2024/05/16 05:07

在从table1表复制到table2的时候，我们会用语句：

select * into table2 from table1

但这个语句并不能在复制数据的同时，复制主键。

下面的代码通过动态语句，来实现在复制数据的同时，也会复制主键：

if OBJECT_ID('table1') is not null   drop table table1gocreate table table1(id  int  ,idd int,vvv varchar(100),primary key (id,idd)  --为了实验在主键有多个字段的情况，所有主键这里有2个字段)insert into table1select 1,1,'a' union allselect 1,2,'b' union allselect 2,1,'a' godeclare @old_table_name varchar(30)declare @new_table_name varchar(30)declare @is_clustered varchar(10)declare @sql varchar(1000)set @old_table_name = 'table1';set @new_table_name = 'table2';set @is_clustered = '';set @sql = '';select @is_clustered = i.type_desc,       @sql = @sql + ',' + c.name +              case when ic.is_descending_key = 0                        then ' asc'                   else ' desc'              end              from sys.tables tinner join sys.indexes i        on t.object_id = i.object_idinner join sys.index_columns ic        on i.object_id = ic.object_id           and i.index_id = ic.index_idinner join sys.columns c        on c.column_id = ic.column_id           and c.object_id = ic.object_idwhere i.is_primary_key = 1      and t.name = @old_table_nameorder by key_ordinalselect @sql = 'if object_id(''' + @new_table_name + ''') is not null' +              ' drop table ' + @new_table_name +';' +              'select * into ' + @new_table_name +               ' from ' + @old_table_name + ';' +                            'alter table ' + @new_table_name +              ' add primary key ' + @is_clustered +               '(' + stuff(@sql,1,1,'') + ')'select @sql  /*if object_id('table2') is not null    drop table table2;select * into table2 from table1;alter table table2 add primary key CLUSTERED(id asc,idd asc)*/              exec(@sql)select *from table2/*ididdvvv11a12b21a*/