Click here to Skip to main content
12,953,700 members (49,881 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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.
Posted 11-Sep-12 11:54am
Updated 11-Sep-12 11:59am
Kenneth Haugland 11-Sep-12 18:24pm
WHat other database. MS Access, old version of SQL?

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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]

if Table1 does exist:

INSERT INTO [Table1](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.
sonal_1785 12-Sep-12 15:19pm
Thanks Kuthuparakkal. By using this code I transfer all the data to respective tables.
Now my task is to capture changes made to the Base Table(Account) like deletion or updation. how to put those changes in respective tables. I try to use CDC but for to insert specific column in specific table.
eg Account table update C1 and C4 data . now the C1 update should be stored in Table1 and C4 update should stored in Table 2.
How to achieve this ???
Kuthuparakkal 12-Sep-12 23:07pm
You may use triggers:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,429
CHill60 3,490
Maciej Los 3,103
ppolymorphe 2,020
Jochen Arndt 1,975

Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 11 Sep 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100