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

在从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   
         */

不想长大啊发布了416 篇原创文章 · 获赞 135 · 访问量 94万他的留言板关注

秒客网

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

相关文章