有关SQL中数据迁移的问题 [英] Question Regarding Data Migration in SQL

问题描述

我有一个数据迁移任务.大约有9个基本表，我必须使用这些表将数据加载到另一个数据库中的相应表.
例如，帐户表是我的基本表，有9列.
列C1，C2，C3必须存储在表1中，
C4，C5，C6必须存储在table2和
C7，C8，C9必须存储在表3中.
如果表1,2,3中已经存在数据，请不要上传它，否则请插入新记录

同样，我有9个基本表，我需要从这些基本表中将相应的数据迁移到24个表中.

您能建议我一种如何进行此迁移的方法吗?

首先，我想到了使用CASE语句在1个存储过程下调用3个merge语句，但这给了我一个语法错误.所以我现在必须为
创建3个存储的Proc 3合并报表.这将是如此之长，因为我也要休息24个桌子.

I got a task of data migration. There are around 9 base table and I have to load data using these table to respective table in another database.
eg Account table is my base table and has 9 column.
Column C1,C2,C3 has to store in table1,
C4,C5,C6 has to store in table2 and
C7,C8,C9 has to be stored in table3.
If data already exist in table 1,2,3 do not upload it otherwise insert new record

Similarly I have 9 base table from which I need to migrate respective data to 24 table.

Can you suggest me a way how to do this migration?

First I thought of calling 3 merge statement under 1 stored procedure using CASE statment but this is giving me a syntax error. so I now I have to create 3 Stored Proc for
3 merge statment. This will be so length as I have to this for rest 24 table too.

MERGE [INTO] <target table>
USING <source table or table expression>
ON <join/merge predicate> (semantics similar to outer join)
WHEN MATCHED <statement to run when match found in target>
WHEN [TARGET] NOT MATCHED <statement to run when no match found in target>

中

还有其他方法可以进行数据迁移吗?任何建议都将有所帮助.

Is there any other way to do data migration ? any suggestion will be helpful.

推荐答案

在源计算机上进行所有准备，并将所有新表复制到目标.
如果Table1不存在:

Do all preparation on your source machine and copy all the new tables to destination.

if Table1 does not exist:

SELECT C1, C2, C3 
  INTO [Table1] -- Assuming Source is SQL server !!!
FROM [Account Table]

如果Table1确实存在:

if Table1 does exist:

INSERT INTO [Table1](C1, C2, C3)
SELECT C1, C2, C3
FROM [Account Table] A
LEFT OUTER JOIN [Table1] T1 -- JOIN to check existing rows
ON A.C1 = T1.C1 -- check existing rows, modify according to your definition on duplicate
OR A.C2 = T1.C2  -- check existing rows, modify according to your definition on duplicate
OR A.C3 = T1.C3  -- check existing rows, modify according to your definition on duplicate
WHERE T1.C1 IS NULL OR T1.C2 IS NULL OR T1.C3 IS NULL -- block existing rows

现在再重复一次以获取表格...
将所有准备好的表复制到目标位置.

Now do repeat for reamaining tables...
Copy all such prepared tables to destination.

这篇关于有关SQL中数据迁移的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！