问题描述
我正在使用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.
这篇关于将一个表中的数据插入多个表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!