将一张表中的数据插入到多张表中
我正在使用 SQL Server 2005。
我正在将数据从当前数据库(单个表)迁移到新数据库(标准化 - 许多表)。在新数据库中,我有一个基表(我们称之为“BaseTable”)和多个其他表(我们称之为“DependentA”和“DependentB”) 。旧数据库中的一些数据将转到 BaseTable,有些将转到另外两个。 BaseTable 与 DependentA 和 DependentB 都具有一对一的关系,使用它们的 Id 作为外键。
这是我的问题。我应该如何将数据迁移过来?这是我一直在尝试的一个查询,它正在工作,除了一件事:其他两个的 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
I'm using 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.
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
问题在于 @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
My cursor syntax is probably riddled with errors, but you get the idea.
为了避免大型数据集出现游标,请暂时将 OldTable_id 包含在新表中。
对 DependentB 表和从 OldTable 规范化的任何其他表执行相同的操作。
数据迁移后删除OldTable_id。
To avoid a cursor for large data sets, temporarily include the OldTable_id in the new tables.
Do the same for DependentB table and any other tables being normalized out of the OldTable.
Delete OldTable_id after the data migration.
[在此处输入图像描述][1]ZeorOne 是主表,您要从中获取数据并将其分别插入到零表和一表中。
或者您想使用光标插入包含 Zeroone 中选定列的数据
查询位于两个表中
[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.
or you want to use cursor to insert data with selected columns from Zeroone
into to two tables the query is here