Check for Existing records

Question

Check for Existing records

Mike Levan

SSChampion

Points: 14201
More actions
May 26, 2008 at 10:59 am

#182775

How can i check for existing records,add new records and update existing records if there is any change in table
Emp(EmpId,Empno,Empname,Jobno,Jobdesc,JobTitle)
from Table OldEmp(EmpId,Empno,Empname,Jobno,Jobdesc,JobTitle).
What is the easiest way to do this with tsql.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Not sure what you are asking for here. Can you provide DDL for the tables, sample data in each table, and examples of what should happen in each case?

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Mike Levan SSChampion Points: 14201 More actions · Answer 2

OldEmp(

EmpId=14556,14557,

Empno=467,345,

Empname=john,James,

Jobno=23,45,

Jobdesc=abcd,xyz,

JobTitle=TeamLead,ProjectLeader)

Emp(

EmpId=14556,14557,14567

Empno=467,345,435

Empname=john,James,Mike

Jobno=23,45,43

Jobdesc=abcd,xyz,dfgh

JobTitle=TeamLead,ProjectManager,Developer)

From the above data u can observe that 1 new record(Mike) was added and an existing record(James was changed from ProjectLeader to Project Manager) was updated.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Actually, I have no idea (yet) of what you are trying to accomplish. Please review the following article and see if you can put together an example I can work with:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

What table is getting inserted/updated, what is the expected results when it is inserted/updated?

Off the top of my head, I think you are looking at creating triggers - but I am not sure.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Mike Levan SSChampion Points: 14201 More actions · Answer 4

Sorry I confused you with table names.

Actually I am trying to insert,update into Emp table from the Old Emp.

I want this done with single tsql query.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

I'm sorry - I still don't understand what you are trying to accomplish. To insert into Emp from OldEmp it would be something like:

INSERT INTO Emp (EmpId,Empno,Empname,Jobno,Jobdesc,JobTitle)

SELECT EmpId

,Empno

,Empname

,Jobno

,Jobdesc

,JobTitle

FROM OldEmp oe

WHERE NOT EXISTS (SELECT * FROM Emp WHERE Empid = oe.Empid);

The above inserts into Emp all rows from OldEmp that do not exist in Emp. Now, to update there are several methods that really depend upon what you are trying to do. The first method is a simple update statement (run the update before the above insert).

UPDATE e

SET e.EmpId = oe.Empid

,e.Empno = oe.Empno

,e.Empname = oe.Empname

,e.Jobno = oe.Jobno

,e.Jobdesc = oe.Jobdesc

,e.JobTitle = oe.JobTitle

FROM Emp e

INNER JOIN OldEmp oe ON oe.Empid = e.Empid;

Or, you can perform a delete/insert operation as:

DELETE FROM Emp WHERE EXISTS (SELECT * FROM OldEmp WHERE Empid = Emp.Empid);

Then, run the above insert to insert all rows that do not exist in the Emp table.

All of the above is not tested, so I highly recommend testing this thoroughly before implementing or trying in a production environment.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Mike Levan SSChampion Points: 14201 More actions · Answer 6

IS this easy to do with SSIS pkg having a conditional spilt. I am trying to do it as a recurring job.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

we still don't know what you are trying to do.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

I tried - but now this is an SSIS process? Please read the article I linked to earlier and see if you can provide the information we need to help you with this process.

Since this sounds like an SSIS issue, the standard process for moving data from one system to another is:

1) Extract data from source system

2) Import data into staging tables

3) Scrub the data

a) Perform an Update/Insert operation

i) Update existing rows in destination table

ii) Insert new rows into destination table

Or b) Peform a Delete/Insert operation

i) Delete existing rows in destination table

ii) Insert rows extracted from source into destination table

4) Truncate staging tables

5) Perform any post processing needed.

This is just a WAG - so, until we get better information that is probably the best I can do.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Mike Levan (5/26/2008)
Sorry I confused you with table names.
Actually I am trying to insert,update into Emp table from the Old Emp.
I want this done with single tsql query.

It cannot be done in a single query... it always takes two... One to UPDATE rows that already exist, and one to INSERT rows that don't. The process is affectionately called an "UPSERT". Simply stated, you do an update of all rows that respond to an inner join and an insert of all rows that don't. Typically, and outer join or an IF NOT EXISTS is used to find the rows that don't already exist.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Avishkar Meshram SSC-Addicted Points: 414 More actions · Answer 10

You can use before insert and after insert and such triggers to store data in history and present table.