Solved

MERGE multiple tables

Posted on 2013-11-10
6
184 Views
Last Modified: 2013-11-12
Dear all,
I have more then 40 Tables with same structure , in the end of the day I synchronize those table with master-table ( same structure but one extra col "ProjectID"  to distinguee from where the data came )
I develop the following SP  :
MERGE TargetTable AS T
USING SourceTable AS S
ON (T.fld1 = S.fld2) 
WHEN NOT MATCHED BY TARGET 
    THEN INSERT(ProjectID, fld1 ,fld2,fld3 ,fld4  ) 
       VALUES(@ProjectID, S.fld1 ,S.fld2 ,S.fld3 ,S.fld4  )
WHEN MATCHED 
    THEN UPDATE SET T.fld1=S.fld1,
					T.fld2=S.fld2,
					T.fld3=S.fld3,
					T.f4=S.fld4

WHEN NOT MATCHED BY SOURCE 
    THEN DELETE  

Open in new window


When I run on First source-table it work fine ,
when I try to run with second source-table , it delete the old rows ( Came from another Table).
How can stop that?
I try to change Delete to
DELETE T where ProjectID=@ProjectID

Also try to add ON (T.fld1 = S.fld2 and ProjectID=@ProjectID )
but didn't work at all

Please help
thanks
0
Comment
Question by:ethar1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39637211
WHEN NOT MATCHED BY SOURCE

this is your problem, the rows entered from the first source table will not exist in the second so will be deleted.

you could combine your source tables first or add another column that marks the row as new row and exclude this from the delete clause when all the updates are complete update the new row column to null
0
 
LVL 13

Expert Comment

by:magarity
ID: 39637224
You could always do this in one step by substituting a single view for the 40 tables,
something like:

create view all_sources as
select '1' as projectid, fld1, etc from source1
union all
select '2' as projectid, fld1, etc from source2
union all
... from sourceN

Then use that in your merge statement. I think this will allow your delete to function properly but I'm unable just now to test it myself.  I don't know if this will be too big of a transaction to make that view. You've said 40 source tables but not how many rows are involved.

First alternative: you could do the deletes as a separate set of statements:

delete from target left outer join source on target.key = source.key where source key is null and target.projectid = 1
...
delete from target left outer join source on target.key = source.key where source key is null and target.projectid = N

Second alternative:
Why not just truncate the target and insert only from the sources? Then you don't even have to do the deletes. Since there are 40 sources, this might actually be faster even if there are a modest ratio of updates.

truncate target;
insert target
select '1' as projectid, fld1, etc from source1
union all
select '2' as projectid, fld1, etc from source2
union all
... from sourceN;
0
 

Author Comment

by:ethar1
ID: 39637388
The natural of source table not available at same time, each table has a policy to connect with.

each table I make a separate SP , to manage the its policy.

Also I cant truncate the Target , its contains extra fields which is populated by local application ( Notes , and Comments , Description , and actions)
I hope I can find a way to change the MERGE  statement.
Otherwise , I will cancel delete case and use :
delete from target left outer join source on target.key = source.key where source key is null and target.projectid = 1

Open in new window

I don't understand how it will delete not existing rows in source !
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Accepted Solution

by:
QuinnDex earned 500 total points
ID: 39637392
add new column to target table type bit default 0, when you update or enter new row set this column to 1, when all updates are complete delete all rows where this column = 0 then up date the column to 0 ready for the next update
0
 
LVL 13

Expert Comment

by:magarity
ID: 39637840
Here is an example I made to show my idea of deleting afterwards works, at least with my understanding of what you're asking about. The delete statement syntax had to be a little different because I was just going from memory before:

use test;
drop table targettable;
drop table sourcetable1;
drop table sourcetable2;
create table targettable (projectid int, keyval int, data1 nvarchar(1));
create table sourcetable1 (keyval int,data1 nvarchar(1));
 
insert into sourcetable1 (keyval,data1) values (1001,'A');
insert into sourcetable1 (keyval,data1) values (1002,'B');
 
merge targettable t using sourcetable1 s on (t.keyval = s.keyval)
when not matched then insert (projectid,keyval,data1) values (1,keyval,data1)
when matched then update set t.data1 = s.data1;
 
create table sourcetable2 (keyval int,data1 nvarchar(1));
insert into sourcetable2 (keyval,data1) values (2001,'P');
insert into sourcetable2 (keyval,data1) values (2002,'Q');
 
merge targettable t using sourcetable2 s on (t.keyval = s.keyval)
when not matched then insert (projectid,keyval,data1) values (2,keyval,data1)
when matched then update set t.data1 = s.data1;
 
select * from targettable;
 
delete sourcetable1 where keyval = 1001;
update sourcetable1 set data1 = 'C' where keyval = 1002;
 
merge targettable t using sourcetable1 s on (t.keyval = s.keyval)
when not matched then insert (projectid,keyval,data1) values (2,keyval,data1)
when matched then update set t.data1 = s.data1;
 
select * from targettable;
 
delete a from targettable a where a.projectid = 1 and a.keyval in
(select t.keyval from targettable t left outer join sourcetable1 s
on t.keyval = s.keyval and t.projectid = 1
where s.keyval is null);
 
select * from targettable;
0
 
LVL 13

Expert Comment

by:magarity
ID: 39643188
You do realize that if the 'delete where 0' step is ever accidentally run out of order then it will delete everything? It will work, just be careful.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question