Solved

SQL 2005 import script / SSIS package to import csv file and check for existing records

Posted on 2010-09-15
7
814 Views
Last Modified: 2012-05-10
I've only had elementary exposure to building SSIS packages and BCP scripts to automate data import, so any suggestions with this would be greatly appreciated.

I'd like to be able to automate a csv file import into a SQL table (would not contain more than 2K records) but only insert if the record's primary key does not already exist.  If there is an existing record with the same primary key as the record in the csv file, then update the record.  

The need seems simple enough and must be something that is widely executed... I just don't know where to begin.

Thanks,
Mimi
0
Comment
Question by:c0fee
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33688404
Here is what you need to do:
1. Import the whole table into a staging table.
2. Once imported, validate the data and only import the rows that do not exist using a trivial SQL query.
0
 
LVL 7

Expert Comment

by:rashmi_vaghela
ID: 33688587
0
 
LVL 4

Expert Comment

by:timexist
ID: 33689117
this is my SSIS package to load detached email files to database.
it is a hard too explain them all in a few words.

it just gives you an idea what it can be.
ssis.jpg
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 2

Author Comment

by:c0fee
ID: 33691704
acperkins - for the SQL script, do I need to curse through the temp table records and pass the values in an "if exists then update else insert" statement?  If I'm doing this for 2K records, do I have to worry about performance, table locks, etc.?

rashm and timexist - thanks for the links and the diagram.  It will be a useful reference, but at this stage, I'm not so concerned about how to build a process.  But rather how to parse through the CSV file and determine whether I should execute an UPDATE vs. an INSERT- then incorporate into an automated SSIS package.
0
 
LVL 4

Assisted Solution

by:timexist
timexist earned 200 total points
ID: 33697461
Hi c0fee,

I think the process should include two steps,

the first step, you load the file in the staging table, in this step, you need to make sure, the file itself is not loaded multiple times,

the second step is processing the records in staging table which only the new records (let's say 200 records of total 3000 records are newly loaded records)will be processed, in these 200 records, based on the business rules, you decide which way they should go. After the process you flag these 200 records as processed.

all these two steps can be put in a stored procedure if you want to, they can be put in an Excute SQL Task .

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
ID: 33697603
>>I think the process should include two steps, <<
Right.  That was what I was suggesting here http:#a33688404, but I am glad you agree with me.  :)

>> for the SQL script, do I need to curse through the temp table records and pass the values in an "if exists then update else insert"<<
It is as simple as:

INSERT YourTableNameGoesHere (Col1, Col2, Col3, ...)
SELECT Col1, Col2, Col3, ...
FROM YourStagingTableGoesHere s
          LEFT YourTableNameGoesHere t on s.YourPrimaryKeyGoesHere = t.YourPrimaryKeyGoesHere
WHERE t.YourPrimaryKeyGoesHere IS NULL

0
 
LVL 2

Author Closing Comment

by:c0fee
ID: 33810120
Implemented the staging table to get this to work.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question