dba.table1:studentNo(主键,每次自增1),studentName,Sex,Point 现在要把table1的所有数据复制到dbb.table2。 我用select * into dbb.table2 from dba.table1(发现 dbb中已经有table2执行不了,于是先删了table2后才执行这个语句)
select 非自增列,identity(1,1) studentNo into 新表 from 源表
现在就是手动去一个个点击表然后设计主键的,有点烦,如果写sql语句就容易多了
那要拼接动态sql来实现咯,不过我没现成脚本
我做了一个实验,下面的语句可以select * into 的同时,建立主键,不过是动态生成的语句:if OBJECT_ID('table1') is not null drop table table1 gocreate 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_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 /* id idd vvv 1 1 a 1 2 b 2 1 a */
ps:如果不是自动递增的,请问如何补充生成?
现在要把table1的所有数据复制到dbb.table2。
我用select * into dbb.table2 from dba.table1(发现 dbb中已经有table2执行不了,于是先删了table2后才执行这个语句)
from 源表
drop table table1
gocreate 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_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
/*
id idd vvv
1 1 a
1 2 b
2 1 a
*/