日期:2014-05-17 浏览次数:20438 次
--动态将一张表数据拆成一条数据是一张表
if object_id('A') is not null
drop table A
GO
CREATE TABLE A
(
ID INT PRIMARY KEY,
产品 nvarchar(10),
订购数量 int
)
go
insert into a
select 1,'AA',3 UNION ALL
select 2,'BB',2 UNION ALL
select 3,'CC',2 UNION ALL
select 4,'DD',1
go
declare @count int,@i int
select @count = count(*) from a
set @i=1
select @count
while (@i <= @count)
begin
declare @name nvarchar(10),@sql nvarchar(100),@sql1 nvarchar(100)
set @name = 'b'+convert(nvarchar(10),@i)
--动态创建表
set @sql = N'select * into '+@name+' from a where 1<>1' --复制表结构
--print @sql
exec sp_executesql @sql
--插人数据
set @sql1 = N'insert into ' + @name + ' select * from a where id =