将一个表中的数据插入多个表中

I'm using SQL Server 2005.

我正在使用SQL Server 2005。

I am migrating data over from a current database (single table) to a new database (normalized - many tables). In the new database, I have a base table (let's call it "BaseTable"), and multiple other tables (let's call them "DependentA", and "DependentB"). Some of the data from the old database will go to BaseTable, and some will go to the other two. BaseTable has a one-to-one relationship with both DependentA and DependentB, using the Id of them as the foreign key.

我正在将数据从当前数据库（单个表）迁移到新数据库（规范化 - 许多表）。在新数据库中，我有一个基表（我们称之为“BaseTable”）和多个其他表（让我们称之为“DependentA”和“DependentB”）。来自旧数据库的一些数据将转到BaseTable，而另一些将转到另外两个。 BaseTable与DependentA和DependentB具有一对一的关系，使用它们的Id作为外键。

So here's my question. How should I migrate the data over? Here is a query I've been trying, which is working except for one thing: the foreign keys in BaseTable for the other two are identical, instead or having a different one each.

所以这是我的问题。我应该如何迁移数据？这是我一直在尝试的一个查询，除了一件事之外它是有效的：BaseTable中的其他两个外键是相同的，而不是每个都有不同的。

Begin SQL:

BEGIN TRANSACTION

DECLARE @dep1Id int

DECLARE @dep2Id int

INSERT INTO DependentA (column1, column2)
SELECT c1, c2
FROM OldDatabase.OldTable
SELECT @dep1Id = Scope_Identity()

INSERT INTO DependentB (column3, column4)
SELECT c3, c4
FROM OldDatabase.OldTable
SELECT @dep2Id = Scope_Identity()

INSERT INTO BaseTable (column5, dependentTable1Id, dependentTablr2Id)
SELECT c5, @dep1Id, @dep2Id
FROM OldDatabase.OldTable

COMMIT

3 个解决方案

#1

The problem is that @dep1Id and @dep1Id are scalar and are retaining the last value only from the two set based inserts.

问题是@ dep1Id和@ dep1Id是标量，只保留两个基于集合的插入的最后一个值。

Since it's a one off you should probably do it as a cursor

因为它是一次性的，你应该把它作为光标

DECLARE CURSOR @curs FOR
SELECT c1,c2,c3,c4,c5 FROM OldDatebase

open @curs
fetch next from @curs into
@c1,@c2,@c3,@c4,@c5 --declare these!

while @@fetch_status <> 0
BEGIN

INSERT INTO DependentA (column1, column2) VALUES @c1, @c2

SELECT @dep1Id = Scope_Identity()

INSERT INTO DependentB (column3, column4) VALUES @c3, @c4 

SELECT @dep2Id = Scope_Identity()

INSERT INTO BaseTable (column5, department1Id, department2Id) @c5, @dep1Id, @dep2Id    

fetch next from @curs into
@c1,@c2,@c3,@c4,@c5
END
close @curs
deallocate @curs

My cursor syntax is probably riddled with errors, but you get the idea.

我的游标语法可能有很多错误，但你明白了。

#2

To avoid a cursor for large data sets, temporarily include the OldTable_id in the new tables.

要避免使用大型数据集的游标，请在新表中临时包含OldTable_id。

BEGIN TRANSACTION

INSERT INTO DependentA (OldTable_id, column1, column2)
SELECT ot.id, ot.c1, ot.c2
FROM OldDatabase.OldTable ot

INSERT INTO BaseTable (OldTable_id, column5)
SELECT ot.id, ot.c5
FROM OldDatabase.OldTable ot

UPDATE BaseTable 
    SET BaseTable.dependentTable1_id = DependentA.id
    FROM BaseTable
    INNER JOIN DependentA on DependentA.OldTable_id = BaseTable.OldTable_id

COMMIT

Do the same for DependentB table and any other tables being normalized out of the OldTable.

对DependentB表和从OldTable规范化的任何其他表执行相同的操作。

Delete OldTable_id after the data migration.

数据迁移后删除OldTable_id。

#3

[enter image description here][1]ZeorOne is the main table from which you want to get data and insert it into zero and one table respectively.

[在此输入图像描述] [1] ZeorOne是您想要获取数据并将其分别插入零和一个表的主表。

select idzero,namezero,idone,nameone from zeroone

insert into zero 
select idzero,namezero from zeroone

insert into one
select idone,nameone from zeroone

or you want to use cursor to insert data with selected columns from Zeroone into to two tables the query is here

或者您希望使用游标将包含从Zeroone中选定列的数据插入到查询所在的两个表中

Declare @idzero int
Declare @namezero varchar(50)
Declare @idone int
Declare @nameone varchar(50)

Declare Cur Cursor  for
select idzero,namezero,idone,nameone from zeroone

open Cur

fetch Cur into @idzero,@namezero,@idone,@nameone

While @@fetch_status = 0
begin 

    insert into zero 
    select @idzero,@namezero 

    insert into one
    select @idone,@nameone 

    fetch Cur into @idzero,@namezero,@idone,@nameone

end 

close Cur
Deallocate Cur

#1