CODEWITHSUNDEEP
sql-servert-sqlssis
InTheWorldOfCodingApplications
InTheWorldOfCodingApplications

Reputation: 2616

Update Table With Join Condition

I have following scenario.

 Table A // Dev
 ID
 NAME
 Address

 Table A  // Prod
 ID
 NAME
 Address

While Deleting from Dev i need to check if it exists in prod and if it does then i need to restore it's values from prod and delete all those don't exist in Prod. ANY SQL help around this? Can anyone suggest a query?

Upvotes: 0

Views: 318

Answers (2)

praveen
praveen

Reputation: 12281

You should use Lookup

enter image description here

1.Source will be your development .

2.Drag a lookup and write a query to get the ID from the production table .Match the ID's from source and Production in Lookup and select the ID from Production as well as other columns in production

enter image description here

3.Drag a OLedb command and write a query to update the dev 

 update d set d.Col1 = ?, d.Col2 =?
  from dev.tableA d 
  where d.id = ?

4.Similary write the query for delete and map the columns selected fromn the lookup 

  delete from dev.tableA
  where id <> ?

Note: Oleb command executes for each row .Therefore it will be slow if you have too many rows . If performance is a main concern then you can dump all the data after lookup into a table in your development server and then use Merge syntax in a Execute SQL task to perform update and delete operation

Upvotes: 1

Kaf
Kaf

Reputation: 33839

If both databases are in the same instance then you could try following queries:

--update query
update d set d.name = p.name, d.address = p.address
from dev.dbo.tableA d 
        join prod.dbo.tableA p on d.id = p.id

--delete query
delete dev.dbo.tableA
where id not in (select id from prod.dbo.tableA)

Upvotes: 1

Related Questions