Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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
Edited 11-Sep-12 11:59am
v2
Comments
Kenneth Haugland at 11-Sep-12 18:24pm
   
WHat other database. MS Access, old version of SQL?

1 solution

Rate this: bad
good
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)
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.
  Permalink  
v3
Comments
sonal_1785 at 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 at 12-Sep-12 23:07pm
   
You may use triggers:
http://www.4guysfromrolla.com/webtech/091901-1.shtml

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

  Print Answers RSS
0 OriginalGriff 235
1 Sergey Alexandrovich Kryukov 120
2 Richard MacCutchan 100
3 kbrandwijk 85
4 Vinay Mistry 70
0 Sergey Alexandrovich Kryukov 9,056
1 OriginalGriff 8,041
2 CPallini 2,613
3 Richard MacCutchan 2,221
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 11 Sep 2012
Copyright © CodeProject, 1999-2014
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