Solved

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

Posted on 2010-09-15
7
807 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

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.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

803 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