将一个表中的数据插入多个表中 [英] INSERT Data From One Table Into Multiple Tables

问题描述

我正在使用SQL Server 2005。

I'm using SQL Server 2005.

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

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中其他两个的外键是相同的，而不是每个都相同。

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.

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

推荐答案

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

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

由于这是一个关闭，您可能应该这样做光标

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.

这篇关于将一个表中的数据插入多个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！