Solved

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

Posted on 2010-09-15
7
805 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

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.​
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.
Both in life and business – not all partnerships are created equal. Spend 30 short minutes with us to learn:   • Key questions to ask when considering a partnership to accelerate your business into the cloud • Pitfalls and mistakes other partners…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now