从一个表列的字符串中取以逗号分隔的ID，安插另外一个表
从一个表列的字符串中取以逗号分隔的ID，插入另外一个表
A表
CREATE TABLE #test_A(
[XH] [int] NOT NULL,
[BLR] VARCHAR(1000) NULL,
[sConet] VARCHAR(2000) NOT NULL
)
INSERT INTO #test_A
SELECT 333,'5,90,120,3,111','测试内容'
CREATE TABLE #test_B(
[GWXH] [int] IDENTITY(1,1) NOT NULL,
[XH] [int] NOT NULL,
[BLR] [int] NULL,
[DLR] [int] NULL,
[sConet] [nvarchar](20) NULL
)
DROP TABLE #test_A,#test_B
---希望#test_B 得出如下结果
---1,333,5,5,'测试内容'
---2,333,90,90,'测试内容'
---3,333,120,120,'测试内容'
---4,333,3,3,'测试内容'
---5,333,111,111,'测试内容'
------把A表的BLR列转为表，然后用游标或循环，一个个插入到B表？请各位大大帮忙
------解决方案--------------------
插入的就自己写吧
A表
CREATE TABLE #test_A(
[XH] [int] NOT NULL,
[BLR] VARCHAR(1000) NULL,
[sConet] VARCHAR(2000) NOT NULL
)
INSERT INTO #test_A
SELECT 333,'5,90,120,3,111','测试内容'
CREATE TABLE #test_B(
[GWXH] [int] IDENTITY(1,1) NOT NULL,
[XH] [int] NOT NULL,
[BLR] [int] NULL,
[DLR] [int] NULL,
[sConet] [nvarchar](20) NULL
)
DROP TABLE #test_A,#test_B
---希望#test_B 得出如下结果
---1,333,5,5,'测试内容'
---2,333,90,90,'测试内容'
---3,333,120,120,'测试内容'
---4,333,3,3,'测试内容'
---5,333,111,111,'测试内容'
------把A表的BLR列转为表，然后用游标或循环，一个个插入到B表？请各位大大帮忙
------解决方案--------------------
插入的就自己写吧
CREATE TABLE #test_A(
[XH] [int] NOT NULL,
[BLR] VARCHAR(1000) NULL,
[sConet] VARCHAR(2000) NOT NULL
)
INSERT INTO #test_A
SELECT 333,'5,90,120,3,111','测试内容'
select
ROW_NUMBER()OVER(ORDER BY GETDATE())id,
a.[XH],
SUBSTRING([BLR],number,CHARINDEX(',',[BLR]+',',number)-number) as [BLR] ,[sConet]
from
#test_A a,master..spt_values
where
number >=1 and number<=len([BLR])
and type='p'
and substring(','+[BLR],number,1)=','
/*
id XH BLR sConet
-------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 333 5 测试内容
2 333 90 测试内容