Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Comparing 2 tables and updating/inserting the main table

I have 2 tables: Main table with maybe 100,000+ rows and a Table1 with maybe 2000 rows.

I need to compare Table1 with the main table.
1.  If ID field in Table1 already exists in the Main table, then update that row in the Main table. 2. If ID in Table1 doesnt exists in Main table, then insert the row from Table1 into Main table.


Do I need to loop thru Main and Table1??
0
Camillia
Asked:
Camillia
2 Solutions
 
ralmadaCommented:
In sql 2005 you need to use one update and one insert:


Update a
set a.col1 = b.col1, a.col2 = b.col2, ... and so on... 
from Main a
inner join Table1 b on a.ID = b.ID

insert Main
select * from Table1 a
left join Main b on a.ID = b.ID
where b.ID is null

Open in new window


PS: Im assuming both tables structures are identical for the insert, if not you have to list them explicitly there in the select, so it should be

insert Main
select col1, col2, col3 from Table1 a
left join Main b on a.ID = b.ID
where b.ID is null
0
 
Jared_SCommented:
Don't loop, the joins will take care of that.
This will be your basic logic:

IF exists (select null from MainTable m where m.id = t1.id)
Begin
update m1 using records from table1 where the ID's match
End
Else
Begin
Insert into m1 (select rows from table1 where the ids aren't in the Main table)
End
0
 
CamilliaAuthor Commented:
yes, not sure why i thought of looping. Let me try
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
CamilliaAuthor Commented:
This line is not correct

IF exists (select null from MainTable m where m.id = t1.id)

I did this

IF EXISTS (SELECT ImagURL FROM Main WHERE ImagURL IN (SELECT ImageURL FROM Table1) )
0
 
ralmadaCommented:
excuse me, have you checked my comment? http:#a38410086

Also you don't have to do an IF ELSE there
0
 
CamilliaAuthor Commented:
yes, just noticed it. Let me try that as well.
0
 
Alpesh PatelAssistant ConsultantCommented:
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now