Lookup/insert question - 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

Lookup/insert question

Author

Topic

RalphWiggum
Starting Member

13 Posts

Posted - 2011-03-31 : 15:03:54

Hi All. Sorry if this has been answered before, but I can't search in terms that have given me any useable results.

Background:
My main objective is to integrate 2 different systems.
System 1 has a SQL Server 2005 Standard backend.
System 2 is a SaaS system that allows me to export all data in CSV files. Each file is a "table", so to speak.
I want to import System 2's data into System 1's SQL Server backend.

What I've accomplished to integrate the 2 different systems:
I have created an SSIS package that imports System 2's data into SQL Server. It does not import any duplicate data from System 2's export, and it can determine which records are new, writes them to the SQL Server destination, and discards duplicates (as updating duplicate records is not a requirement in this scenario). The package runs as a SQL Server Agent job twice a week w/no data discrepancies. The reason I do not having System 2's data import directly into System 1 in the SSIS package is because the data needs to be "massaged" a bit before I can do that, espeically with respect to field constraints and data types. Come to think of it, I can probably convert the datatypes in the SSIS package, but I am not at the moment (I'm doing it in a stored proc instead).

So, now I have System 2's data successfully imported in SQL Server. We'll call this database/table SQLSystem2.table. I have a stored proc that does the following:
1. Converts the data types of the fields in SQLSystem2.table to match System 1's data (we'll call this database/table SQLSystem1.table)
2. Inserts data from SQLSystem2.table into SQLSystem1.table.

However, I can't find a great way to check for existing data in SQLSystem1.table. Essentially, I want the stored proc to insert data from SQLSystem2.table INTO SQLSystem1.table ONLY IF the data doesn't exist.

Here's my SP code. I try to catch existing records with IF EXISTS:

IF EXISTS(SELECT field from SQLSystem1.table)
BEGIN
SELECT 'RECORD EXISTS'
END
ELSE
BEGIN
INSERT INTO SQLSystem1.table(fields)
SELECT DISTINCT
SQLSystem2 fields get selected and converted here
FROM SQLSystem2.table with some joins on other tables
WHERE i apply some conditions
END
END

The code prior to the INSERT INTO statement is what's giving me some grief. Essentially, if a record already exists, NO data gets written, even if 1 or more records from SQLSystem2.table do not exist.

Any ideas? Sorry for the novel :(

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-31 : 15:35:41

Assuming T1 is your destination table and T2 is your source table, and, assuming that you want to insert a row only if the corresponding col1 value does not already exist in T1:

insert into T1 (col1,col2,col3)
select (col1,col2,col3)
from T2
where 1=1 -- and other where conditions
and not exists (select * from T1 where t1.col1=t2.col1)

RalphWiggum
Starting Member

13 Posts

Posted - 2011-03-31 : 16:04:59

quote:
Originally posted by sunitabeck

Assuming T1 is your destination table and T2 is your source table, and, assuming that you want to insert a row only if the corresponding col1 value does not already exist in T1:
insert into T1 (col1,col2,col3)
select (col1,col2,col3)
from T2
where 1=1 -- and other where conditions
and not exists (select * from T1 where t1.col1=t2.col1)

This works. Amazing. I've been flubbing with this for 2 days :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources