A: A1 B: B1 B2 B3 B4 B5 a a b c d e b 将A1中元素插入B c d
declare @sql varchar(8000) select @sql=@sql+','''+A1+'''' from A set @sql='select '+stuff(@sql,1,1,'') exec('insert into B select ' +@sql)
declare @sql varchar(8000) set @sql='' select @sql=@sql+','''+A1+'''' from A set @sql='select '+stuff(@sql,1,1,'') exec('insert into B select ' +@sql)
declare @sql varchar(8000) set @sql='' select @sql=@sql+','''+A1+'''' from A set @sql='select '+stuff(@sql,1,1,'') exec('insert into B ' +@sql)sorry...
create table A1(col char(1)) insert A1 select 'a' insert A1 select 'b' insert A1 select 'c' insert A1 select 'd' insert A1 select 'e'create table B(B1 char(1), B2 char(1), B3 char(1), B4 char(1), B5 char(1))declare @sql varchar(8000) set @sql='insert B select ' select @sql=@sql+quotename(col)+'='+quotename(col, '''')+',' from A1 select @sql=left(@sql, len(@sql)-1) exec(@sql)select * from B--result B1 B2 B3 B4 B5 ---- ---- ---- ---- ---- a b c d e(1 row(s) affected)
create table a(a1 varchar(2)) create table b(b1 varchar(2),b2 varchar(2),b3 varchar(2),b4 varchar(2),b5 varchar(2)) insert into a select 'a' union all select 'b' union all select 'c' union all select 'd' union all select 'e' declare @str varchar(100) set @str='' select @str=@str+ ','''+a1+''' [' + a1+']' from a set @str=stuff(@str,1,1,'') set @str='insert into b select '+@str exec( @str)
a a b c d e
b 将A1中元素插入B
c
d
select @sql=@sql+','''+A1+'''' from A
set @sql='select '+stuff(@sql,1,1,'')
exec('insert into B select ' +@sql)
set @sql=''
select @sql=@sql+','''+A1+'''' from A
set @sql='select '+stuff(@sql,1,1,'')
exec('insert into B select ' +@sql)
set @sql=''
select @sql=@sql+','''+A1+'''' from A
set @sql='select '+stuff(@sql,1,1,'')
exec('insert into B ' +@sql)sorry...
create table A1(col char(1))
insert A1 select 'a'
insert A1 select 'b'
insert A1 select 'c'
insert A1 select 'd'
insert A1 select 'e'create table B(B1 char(1), B2 char(1), B3 char(1), B4 char(1), B5 char(1))declare @sql varchar(8000)
set @sql='insert B select '
select @sql=@sql+quotename(col)+'='+quotename(col, '''')+',' from A1
select @sql=left(@sql, len(@sql)-1)
exec(@sql)select * from B--result
B1 B2 B3 B4 B5
---- ---- ---- ---- ----
a b c d e(1 row(s) affected)
create table b(b1 varchar(2),b2 varchar(2),b3 varchar(2),b4 varchar(2),b5 varchar(2))
insert into a select 'a' union all select 'b' union all select 'c' union all select 'd' union all select 'e' declare @str varchar(100)
set @str=''
select @str=@str+ ','''+a1+''' [' + a1+']' from a
set @str=stuff(@str,1,1,'')
set @str='insert into b select '+@str exec( @str)