怎样用sql语句复制表table1到表table2的同时复制主键
在从table1表复制到table2的时候，我们会用语句：
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*/