在单个SQL查询中插入和关联记录?

DECLARE @id INT
DECLARE @col_a VARCHAR(50)
DECLARE db_cursor CURSOR FOR 
    SELECT id, col_a
    FROM Table_Source

OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @id, @col_a

WHILE @@FETCH_STATUS = 0  
BEGIN  
    INSERT INTO Table_2 (col_a) VALUES (@col_a)
    INSERT INTO Table_1 (id, fk) VALUES (@id, @@IDENTITY)

    FETCH NEXT FROM db_cursor INTO @id, @col_a
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

假设数据库“Old”和“New”具有上述表结构,假设两个数据库都存在于同一个SQL实例中,假设旧表1中的每个“ColA”值都存在于新表2中,并且假设您是第一次加载数据,则一个版本为:

INSERT New.dbo.Table1 (Id, A_Id_FK)
 select Old.Id, New2.Id
  from Old.dbo.Table1 Old
   inner join New.dbo.Table2 New2
    on New2.ColA = Old.ColA

听起来你可能已经填充了新的..表1?如果是这样,您需要某种update语句将该表与Old..Table1和New..Table2链接起来。

我假设表2中的Id是下面代码中的一个标识。

insert into Table2
    (ColA)
    select distinct ColA
        from OldTable1

insert into Table1
    (Id, A_Id_FK)
    select ot.Id, t2.Id
        from OldTable1 ot
            inner join Table2 t2
                on ot.ColA = t2.ColA