May 7, 2012 at 12:51 pm
Hi,
I have two tables say 'A' and 'B' and then have 'ID' field common in them. I want to write an Stored Procedure which will see if a record in Table 'A' has corresponding record in table 'B' (Putting a join on ID column) then Update record in table 'B' with values from record in Table'A'. If record in Table 'A" does not have any corresponding record in Table 'B' , then create a new record in Table'B' with similar values from table'A'. I am new to SQL. My record count in table A would be close to 10K. Shall I use cursor to do this?
Please suggest.
May 7, 2012 at 1:24 pm
It sounds like you want to use the MERGE command.
If you search for MERGE TSQL you will find lots of examples.
May 7, 2012 at 1:24 pm
Nope, not required. A INSERT + UPDATE stament could do it; else, as you are in SQL 2008, you can use MERGE syntax to do that in one shot.
Post your table strucute and some sample data (both mock-up) , we shall guide you from there. If you dont know what I am talking about, please refer here --> How to post questions[/url]
May 7, 2012 at 1:32 pm
Thanks for your replies. There will be 15-20 fields on nvarchar type. Any suggestions over Merge or Update/Insert. Which one would be better?
May 7, 2012 at 1:39 pm
MERGE
May 7, 2012 at 3:43 pm
ColdCoffee (5/7/2012)
MERGE
+1 with Merge you only have to run 1 query instead of 2 (one for insert one for update)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 8, 2012 at 6:06 am
SQL 2008 You can use MERGE keyword.
SQL 2005 Either use conditional split OR INSERT +UPDATE
EG:-
Method 1
IF --Insert chk
INSERT quiery
ELSE IF --Update Chk
--Update Query
Method 2
INSERT QUERY --IF ID does not exists
Update Query -- IF ID exists
May 8, 2012 at 7:12 am
Hello Try This Example --
create table #a ( id int, col varchar(5) )
insert into #a
values (1,'a'),(2,'b'),(5,'c')
create table #b ( id int, col varchar(5) )
insert into #b
values (1,'a'),(3,'b'),(4,'c')
merge into
#b as t
using #a as s
on t.id = s.id
when matched
then update set t.col = s.col
when not matched
then insert values (s.id,s.col);
select * from #a; select * from #b
May 8, 2012 at 7:12 am
Hello Try This Example --
create table #a ( id int, col varchar(5) )
insert into #a
values (1,'a'),(2,'b'),(5,'c')
create table #b ( id int, col varchar(5) )
insert into #b
values (1,'a'),(3,'b'),(4,'c')
merge into
#b as t
using #a as s
on t.id = s.id
when matched
then update set t.col = s.col
when not matched
then insert values (s.id,s.col);
select * from #a; select * from #b
May 9, 2012 at 2:16 pm
Is it Possible that If records do not match then , I can add Insert that record into some third table?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply