有两个表:
tb1:(TaskID int,var_details nvarchar(50))
TaskID var_details
=================================
001 Q-1538,Q-0141,Q-1328,Q-1981,
002 Q-1322,Q-1193,tb2: (TaskID int,HRID nvarchar(50))
TaskID HRID
=============现在想要将tb1的资料拆分后Insert到tb2中,如下:
TaskID HRID
===============
001 Q-1538
001 Q-0141
001 Q-1328
001 Q-1981
002 Q-1322
002 Q-1193
...
该如何实现啊?...
tb1:(TaskID int,var_details nvarchar(50))
TaskID var_details
=================================
001 Q-1538,Q-0141,Q-1328,Q-1981,
002 Q-1322,Q-1193,tb2: (TaskID int,HRID nvarchar(50))
TaskID HRID
=============现在想要将tb1的资料拆分后Insert到tb2中,如下:
TaskID HRID
===============
001 Q-1538
001 Q-0141
001 Q-1328
001 Q-1981
002 Q-1322
002 Q-1193
...
该如何实现啊?...
Insert tb1 Select '001', 'Q-1538,Q-0141,Q-1328,Q-1981,'
Union All Select '002', 'Q-1322,Q-1193,'Create Table tb2(TaskID Char(3),HRID nvarchar(50))
GO
Select Top 1000 ID = Identity(Int, 1, 1) Into #T From Syscolumns A, Syscolumns BInsert tb2
Select
TaskID,
var_details = Substring(A.var_details, B.ID, CharIndex(',', A.var_details + ',', B.ID) - B.ID)
From tb1 A, #T B
Where Substring(',' + Left(a.var_details, Len(a.var_details) - 1), B.id, 1) = ','
Order By TaskID, CharIndex(',', A.var_details + ',', B.ID)Select * From tb2
Drop Table #T
GO
Drop Table tb1, tb2
--Result
/*
TaskID var_details
001 Q-1538
001 Q-0141
001 Q-1328
001 Q-1981
002 Q-1322
002 Q-1193
*/
create table tb1(TaskID int,var_details nvarchar(50))
go
insert tb1 select
001, 'Q-1538,Q-0141,Q-1328,Q-1981,'
union all select
002, 'Q-1322,Q-1193,'
go
create table tb2 (TaskID int,HRID nvarchar(50))
go--插入
declare @sql varchar(8000)
set @sql='insert tb2 select '
select @sql=@sql+cast(TaskID as varchar)+','''+replace(left(var_details,len(var_details)-1),',',''' union all select '+cast(TaskID as varchar)+',''')+''' union all select '
from tb1set @sql=left(@sql,len(@sql)-17)exec(@sql)--查看结果
select * from tb2--结果
askID HRID
----------- --------------------------------------------------
1 Q-1538
1 Q-0141
1 Q-1328
1 Q-1981
2 Q-1322
2 Q-1193(所影响的行数为 6 行)--清除环境
drop table tb1,tb2