Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jszulc
Yak Posting Veteran
66 Posts |
 Posted - 2011-10-24 : 11:35:13
|
| I am trying to write insert and update operation. I need to take all of the records from MACD_QUEUE that do not exist in QMASTER table and insert them to QMASTER table. Anytime new QUEUE is created in MACD_QUEUE table I would like to transfer all of these new records to QMASTER table. QMASTER will be edited through a portal when cross refrenced liek this:SELECT distinct QUEUE_ID, MACD_QUEUE.*, QMASTER.* from MACD_QUEUE LEFT JOIN QMASTERON MACD_QUEUE.NAME=QMASTER.QUEUE_QUEUE_NAMEit will always show data which is updated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-10-29 : 13:41:33
|
Hello Jszulc, Ok I will give you some Merge Code :but if we use MERGE=> Insert (NOT MATCHED situation) not happendquote:
example:—————————————————–MERGE INTO TesterTBL AS TargetUSING (SELECT id, val FROM TesterTBL where id=1) AS SourceON ( Target.id =Source.id )WHEN MATCHED THENUPDATE SET Target.val = “MyUpdate”WHEN NOT MATCHED BY TARGET THENINSERT (id, val)VALUES (2,’MyInsert’) ;
paul Tech   |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-29 : 13:53:36
|
quote:
Originally posted by paultech
Hello Jszulc, Ok I will give you some Merge Code :but if we use MERGE=> Insert (NOT MATCHED situation) not happendquote:
example:—————————————————–MERGE INTO TesterTBL AS TargetUSING (SELECT id, val FROM TesterTBL where id=1) AS SourceON ( Target.id =Source.id )WHEN MATCHED THENUPDATE SET Target.val = “MyUpdate”WHEN NOT MATCHED BY TARGET THENINSERT (id, val)VALUES (2,’MyInsert’) ;
paul Tech
how do you think it will happen in your case?you're using source as a subset of your target as it just selects rows with id=1 condition from target table (TesterTBL) itself . so in this case unmatched condition will never occur at all as you've all records (including id=1) already in target. so only updates will occur in above statement ,no inserts------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-10-29 : 19:21:12
|
| No i didn't meant that exactly ,that's just an example ! ,Of course according the parameter @id ,the case will changed ? I just gave an outline of idea ......thankspaul Tech |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-29 : 23:28:48
|
quote:
Originally posted by paultech
No i didn't meant that exactly ,that's just an example ! ,Of course according the parameter @id ,the case will changed ? I just gave an outline of idea ......thankspaul Tech
thats fine. I was replying on your below commentbut if we use MERGE=> Insert (NOT MATCHED situation) not happend------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
INSERT new records only