--if(不包含主键)---求这条语句 --则执行以下SQL语句 --SQL语句我暂时省略了我猜你是想动态生成表的字段然后插入吧?下面是动态生成表字段的语句,其实如果没有主键,直接select * 也可以,看你自己选择了,2天睡了5个小时,没精神帮你看:DECLARE @s NVARCHAR(4000) SET @s = '' SELECT @s = @s + ',' + QUOTENAME(name) FROM syscolumns WHERE id = OBJECT_ID('表名') AND name NOT IN ( '不希望显式的列' ) SET @s = STUFF(@s, 1, 1, '') EXEC('select '+@s+' from 表名 ')
使用 select * from sys.tables where schemaid=1 可以读出数据库中所有dbo表格的名称。 最外层再加个course 循环表名即可。
Set NOCOUNT ON select case when b.column_id is null then '' else ' set identity_insert '+a.name +' on ' end as IdentityOn, a.name, stuff((select ','+b.name from sys.syscolumns b where a.object_id = b.id order by b.id,b.colorder for xml path('') ) ,1,1,'') longname , case when b.column_id is null then '' else ' set identity_insert '+a.name +' off ' end as IdentityOff into #ta from sys.tables a left join sys.identity_columns b on a.object_id = b.object_id where schema_id = schema_id('dbo') declare @sql varchar(8000) WHILE exists(select * from #ta) BEGIN set @sql = '' select top(1) @sql = IdentityOn + ' insert into ' + name + '(' + longname + ') select ' + longname + ' From [服务名].数据库名.dbo.'+ name + IdentityOff from #ta order by name print @sql ;with cet as( select top(1)* from #ta order by name ) delete cet
END drop table #ta GO /* set identity_insert aaa on insert into aaa(id,b) select id,b From [服务名].数据库名.dbo.aaa set identity_insert aaa off insert into c1(a,b,c) select a,b,c From [服务名].数据库名.dbo.c1 insert into cangku(id,cangku,tmpA,tmpB,inPrice,allNum,allprice) select id,cangku,tmpA,tmpB,inPrice,allNum,allprice From [服务名].数据库名.dbo.cangku insert into huang(manIds) select manIds From [服务名].数据库名.dbo.huang insert into Novel(T) select T From [服务名].数据库名.dbo.Novel insert into Nums(n) select n From [服务名].数据库名.dbo.Nums */
--则执行以下SQL语句
--SQL语句我暂时省略了我猜你是想动态生成表的字段然后插入吧?下面是动态生成表字段的语句,其实如果没有主键,直接select * 也可以,看你自己选择了,2天睡了5个小时,没精神帮你看:DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME(name)
FROM syscolumns
WHERE id = OBJECT_ID('表名')
AND name NOT IN ( '不希望显式的列' )
SET @s = STUFF(@s, 1, 1, '')
EXEC('select '+@s+' from 表名 ')
最外层再加个course 循环表名即可。
select
case when b.column_id is null then '' else ' set identity_insert '+a.name +' on ' end as IdentityOn,
a.name,
stuff((select ','+b.name from sys.syscolumns b
where a.object_id = b.id
order by b.id,b.colorder
for xml path('') ) ,1,1,'') longname ,
case when b.column_id is null then '' else ' set identity_insert '+a.name +' off ' end as IdentityOff
into #ta
from sys.tables a left join sys.identity_columns b on a.object_id = b.object_id
where schema_id = schema_id('dbo')
declare @sql varchar(8000)
WHILE exists(select * from #ta)
BEGIN
set @sql = ''
select top(1) @sql = IdentityOn + ' insert into ' + name + '(' + longname + ') select ' + longname + ' From [服务名].数据库名.dbo.'+ name + IdentityOff
from #ta order by name
print @sql
;with cet as( select top(1)* from #ta order by name )
delete cet
END
drop table #ta
GO
/*
set identity_insert aaa on insert into aaa(id,b) select id,b From [服务名].数据库名.dbo.aaa set identity_insert aaa off
insert into c1(a,b,c) select a,b,c From [服务名].数据库名.dbo.c1
insert into cangku(id,cangku,tmpA,tmpB,inPrice,allNum,allprice) select id,cangku,tmpA,tmpB,inPrice,allNum,allprice From [服务名].数据库名.dbo.cangku
insert into huang(manIds) select manIds From [服务名].数据库名.dbo.huang
insert into Novel(T) select T From [服务名].数据库名.dbo.Novel
insert into Nums(n) select n From [服务名].数据库名.dbo.Nums
*/