IF not exist insert record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IF not exist insert record

Author

Topic

Nova114
Starting Member

2 Posts

Posted - 2012-04-22 : 07:58:44

Hi

I'm trying to create a query for the following

I have 2 tables, SourceTBl and my TargetTBL

The source table is data received from the client and needs to be split into appropriate tables

SourceTBL
Data1, Data2, ServID

TargetTBL
Data1, Data2, ServID

ServID is unique

My code updates the target table but doesn't insert the new records

My code is as follows

IF EXISTS ( SELECT SourceTBL.data1, SourceTBL.data2,SourceTBL.ServID,
TargetTBL.*

FROM SourceTBL$ CROSS JOIN
TargetTBL$

WHERE (SourceTBL.ServID = TargetTBL.ServID))
BEGIN
UPDATE TargetTBL$

SET TargetTBL.data1 = SourceTBL.data1,
TargetTBL.data2 = SourceTBL.data2,
TargetTBL.ServID = SourceTBL.ServID,

FROM SourceTBL$ CROSS JOIN
TargetTBL$

WHERE (SourceTBL.ServID = TargetTBL.ServID)

END

ELSE

BEGIN

INSERT INTO TargetTBL$ (Data1, Data2, ServID)

SELECT data1, data2, ServID

FROM SourceTBL$

WHERE (SourceTBL$.Data1='1' or SourceTBL$.Data1='2')

----Only Certain data should be inserted Into the Target the rest goes into another table
-----

END

Any help will be greatly appritiated

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-22 : 08:43:48

Assuming you are using Microsoft SQL Server, the syntax you are using seem a little unusual. (For example, you don't need to append table names with $ sign in SQL server).

What you want to do can be accomplished with two statements shown below:

--- update existing rows.
-- note 1: You don't need to update ServId, because that is what you are joining on.
-- note 2: No need to check whether the row exists in the target table, because only
-- such rows will be picked up when you do an inner join.
UPDATE t SET
	data1 = s.data1,
	data2 = s.data2
FROM
	SourceTbl s
	INNER JOIN TargetTbl t ON
		t.ServId = s.ServId;
		
-- insert new rows.		
INSERT INTO TargetTbl
( ServId, data1, data2)
SELECT
	ServId,
	data1,
	data2
FROM
	SourceTable s
WHERE NOT EXISTS (SELECT * FROM TargetTable t WHERE t.ServId = s.ServId);

If you are on SQL 2008 or higher, a better option perhaps would be to use the MERGE statement. If you are, and have difficulties with it, reply back. http://technet.microsoft.com/en-us/library/bb510625(v=sql.100).aspx

Nova114
Starting Member

2 Posts

Posted - 2012-04-22 : 09:17:54

Thanks Sunitabeck

The insert works now as per your code, been strugling for 4 hours and works perfectly for what I need to do

I am using '08 I tried using Merge and the query results just say 'command completed' and didn't change a thing

Thanks Again

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-22 : 12:38:45

If you want to use MERGE statement, it would be like this:

MERGE TargetTable t
USING SourceTable s
ON 
	s.ServId = t.ServId
WHEN MATCHED AND (s.data1 <> t.data1 OR s.data2 <> t.data2) THEN
	UPDATE SET 
		t.data1 = s.data1,
		t.data2 = s.data2
WHEN NOT MATCHED BY TARGET THEN
	INSERT (ServId, data1, data2)
	VALUES (s.ServId,s.data1,s.data2);

I wrote and then read through the code 3 times, and I think it is correct, but haven't tested it, so please test before you use it.

What I like about merge statement are at least the following:
a) The syntax is almost conversational. You don't need to know about the details of how to write a MERGE statement to understand what is written.
b) It is a single statement - so all the actions you requested - in your case, the update and the insert - will all happen in a single transaction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources