一張表的數據導入到另一張表

Create table Tab(CI_ID int, CI_Keywords nvarchar(20))
Insert Tab
SELECT 10001,  'sky blue water' UNION ALL
SELECT 10002,  'book apple shirt' UNION ALL
SELECT 10005,  'cup yellow org' UNION ALL
SELECT 61245,  'box phone paper'
------生成测试数据
declare @i int

SQL code

set @i = 1
while @i <=15
begin
    insert into Tab select * from Tab
    set @i = @i + 1
end
------
Go
create table SSS(KeyID int identity(1,1),  KeyName varchar(20))
go

SET STATISTICS TIME ON

insert INTO SSS(Keyname)
select
substring(replace(a.CI_Keywords,' ',','),b.Number,charindex(',',replace(a.CI_Keywords,' ',',')+',',b.Number)-b.Number) AS COLS
from Tab a,master..spt_values b
where b.Number between 1 and len(a.CI_Keywords) and b.type = 'P'
and charindex(',',','+replace(a.CI_Keywords,' ',','),b.Number) = b.Number
order by CI_ID

SET STATISTICS TIME OFF

select TOP 10 * from sss


/*
keyID  keyname
1    sky
2    blue
3    water
4    book
5    apple
6    shirt
7    cup
8    yellow
9    org
10    box
11    phone
12    paper
-----
SQL Server Execution Times:
   CPU time = 16250 ms,  elapsed time = 16331 ms.

(393216 行受影响)

(10 行受影响)
*/