与SQL Server批量插入，其中一列具有多个值，而其他所有列均具有预设值

sedavidw 发表于 Dev

塞达维德

我有一个表（Table1），其中有4列（ID1, ID2, Percent, Time, Expired）。我想在该表中插入一堆新行，该行ID1取自我拥有的另一个SQL查询，所有其他列均设置为某些指定值。

所以我有我的查询：

SELECT someID FROM other_tables WITH other_conditions

基本上我想做的是

FOR v in <above query>
     Insert New row into Table1 (v, some second id, some percent, some time, some expired value)

编辑我不反对不在循环中执行此操作，只是不知道插入数据的最佳方法是什么

希门

您可以使用游标并获取我认为要完成的任务。这是给您的贝壳...

带光标

DECLARE c CURSOR FOR
SELECT DISTINCT colName FROM Table1 JOIN Table2 ON <stuff> WHERE <other_stuff>

DECLARE @ID VARCHAR(4) --or what ever is needed

OPEN c
FETCH NEXT FROM c INTO @ID


WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO Table1 (ID, ID2, Percent, Time, Expired)
        VALUES (@ID, some second id, some percent, some time, some expired value)
        WHERE ID = @ID

        FETCH NEXT FROM c INTO @ID
    END

CLOSE c
DEALLOCATE c

交叉应用（虚拟数据）

if object_id('tempdb..#ids') is not null drop table #ids
if object_id('tempdb..#idDetails') is not null drop table #idDetails

create table #ids (id int)
insert into #ids (id) values
(1),(2),(3)


select i.*, d.*
into #idDetails
from #ids i cross apply (select 2 as id2 ,2.0 as per,'1/1/2016' as dt,'x' as x) d

select * from #idDetails

交叉申请（以您的桌子为例）

select i.someID, d.*
into #idDetails
from other_tables i 
cross apply (select 'some second id' as id2 ,'some percent' as [Percent],'1/1/2016 14:55:22' as [SomeTime] as dt,'SomeExpiredVal' as [ExpiredVal]) d

select * from #idDetails

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。