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

select * into table2 from table1

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


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


if OBJECT_ID('table1') is not null    drop table table1 go  create table table1 ( id  int  , idd int, vvv varchar(100), primary key (id,idd)  --为了实验在主键有多个字段的情况，所有主键这里有2个字段 )  insert into table1 select 1,1,'a' union all select 1,2,'b' union all select 2,1,'a'  go   declare @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 t inner join sys.indexes i         on t.object_id = i.object_id inner join sys.index_columns ic         on i.object_id = ic.object_id            and i.index_id = ic.index_id inner join sys.columns c         on c.column_id = ic.column_id            and c.object_id = ic.object_id where i.is_primary_key = 1       and t.name = @old_table_name order by key_ordinal    select @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 /* id	idd	vvv 1	1	a 1	2	b 2	1	a */