sql批量插入优化

[y_haoo]

第一张图是每个项目一个数据库字段，第二张图是统一个项目名称列，方便动态添加。
现将数据从图一批量插入到图二，大概有10W条左右，大家有什么更好或效率更高的办法吗...

[道素]

下面是每行生成1000*1000=100万条

;WITH t(ID,[NAME],mssql,mysql,sqlite)
AS(
	SELECT 1,'aaa',60,70,80 UNION ALL
	SELECT 2,'bbb',60,70,80
)
SELECT ROW_NUMBER() OVER (ORDER BY t.ID,sv1.number,sv2.number) AS ID,t.NAME,p.projec,abs(checksum(newid()))%100 + 1 AS Score
FROM t 
INNER JOIN [master].dbo.spt_values AS sv1 ON sv1.[type]='P' AND sv1.number BETWEEN 1 AND 1000
INNER JOIN [master].dbo.spt_values AS sv2 ON sv2.[type]='P' AND sv2.number BETWEEN 1 AND 1000
LEFT JOIN (VALUES(1,'mssql'),(2,'mysql'),(3,'sqlite')) p(id,projec) ON p.id=abs(checksum(newid()))%3 + 1

[卖水果的net]

create table test(id int, name varchar(10), mssql int, mysql int, sqlite int)
go
insert into test values(1,'aaa',60,70,80),(2,'bbb',60,70,60)
go
select * from test 
go
-- 效率你自己测试一下
select * from test unpivot(project for score in ([mssql],[mysql],[sqlite])) x
go
drop table test 
go

(2 行受影响)
id          name       mssql       mysql       sqlite
----------- ---------- ----------- ----------- -----------
1           aaa        60          70          80
2           bbb        60          70          60

(2 行受影响)

id          name       project     score
----------- ---------- ----------- --------------------
1           aaa        60          mssql
1           aaa        70          mysql
1           aaa        80          sqlite
2           bbb        60          mssql
2           bbb        70          mysql
2           bbb        60          sqlite

(6 行受影响)

[道素]

如果对于每条对于project产生均衡数据（每个project生成一行） 下面的语句对于每个name产生100*100*3=30000条

;WITH t(ID,[NAME],mssql,mysql,sqlite)
AS(
	SELECT 1,'aaa',60,70,80 UNION ALL
	SELECT 2,'bbb',60,70,80
)
SELECT sv1.number*sv2.number AS ID,t.NAME,p.projec,abs(checksum(newid()))%100 + 1 AS Score
FROM t 
INNER JOIN [master].dbo.spt_values AS sv1 ON sv1.[type]='P' AND sv1.number BETWEEN 1 AND 100
INNER JOIN [master].dbo.spt_values AS sv2 ON sv2.[type]='P' AND sv2.number BETWEEN 1 AND 100
CROSS APPLY (VALUES(1,'mssql'),(2,'mysql'),(3,'sqlite')) p(id,projec)

[道素]

我对原问题理解有误，用2楼的方法就可以