April 4, 2012 at 12:49 pm
trying to compare table A (target) with table B (source) and insert any records that don't exist in A that are in B by using a select statement--if possible without doing a stored procedure, because every example I've seen on the web is of a stored procedure. Anyways, if possible can someone use the below example to hammer out the correct syntax?
MERGE tableA USING
SELECT ITEM, DESCRIPTION FROM tableB
WHEN NOT MATCHED BY TARGET THEN
INSERT (ITEM, [DESCRIPTION])
SELECT ITEM, DESCRIPTION FROM tableB
April 4, 2012 at 12:51 pm
Is this also true of the examples in Books Online?
April 4, 2012 at 12:55 pm
Lynn Pettis (4/4/2012)
Is this also true of the examples in Books Online?
Nope.
http://msdn.microsoft.com/en-us/library/bb510625%28v=sql.105%29.aspx
Not really sure why it matters if it is inside a stored proc or not anyway. The syntax and logic are exactly the same thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 4, 2012 at 1:01 pm
MERGE tableA USING
(SELECT ITEM, DESCRIPTION FROM tableB) as src
on tableA.somecolumn = src.somecolumn
WHEN NOT MATCHED BY TARGET THEN
INSERT (ITEM, [DESCRIPTION])
SELECT ITEM, DESCRIPTION FROM tableB
First, the source either needs to be an object name, or needs to be a derived table with parentheses around it. Second, you need to match the target and source with an ON statement, so it knows which rows to compare to which other rows. I've made both of those modifications in your sample, above.
If an example is in the form of a stored procedure, you should be able to modify it into a script simply by removing the object creation parts and adding Declare in front of any parameters to turn them into variables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 4, 2012 at 1:02 pm
something like this passes the syntax check:
MERGE INTO dbo.tableA AS Target
USING(
SELECT ITEM, DESCRIPTION FROM dbo.tableB
) AS source
ON (target.ITEM = source.ITEM)
WHEN NOT MATCHED BY TARGET THEN
INSERT (ITEM, [DESCRIPTION])
VALUES (ITEM, DESCRIPTION);
Lowell
April 4, 2012 at 1:11 pm
gsquared--TY! however, i need to compare both columns in both tables and only then insert--neither field is unique however, the two combined are.
April 4, 2012 at 1:13 pm
then just change the join condition to
ON (target.ITEM = source.ITEM)
AND (target.DESCRIPTION= source.DESCRIPTION)
Lowell
April 4, 2012 at 2:05 pm
Thanks Everyone!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply