SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS data load
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

preetpalkapoor
Starting Member

India
6 Posts

Posted - 03/28/2013 :  03:53:52  Show Profile  Reply with Quote
Hi ,

I have been given an task to load a text file to a database which is simple but the file may contain the duplicate records which are already present in the DB table. So i want to load the correct records and keep other records which are not loaded to table in another text file.

Example:
text file with source data:
ID,Name
1,john
2,maya
3,amir
4,neha

Database table:
ID(primary key),name(varchar(50))
101,sneha
102,michael
1,john
4,neha

Now i want the rows in text file with ID=2,3 get loaded to database table and new text file is created with data 1,john and 4,neha...

Can you please help me in this .



Preetpal kapoor

visakh16
Very Important crosS Applying yaK Herder

India
52320 Posts

Posted - 03/28/2013 :  05:20:15  Show Profile  Reply with Quote
you can use several methods

one method is to use ssis and then use a lookup task to lookup records against table based on ID,name fields. Then attach nomatch output to your destination table.

ANother method is to use OPENROWSET and use query like

SELECT * 
FROM   OPENROWSET('MSDASQL',  
   'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=<path here>;', 
   'SELECT * FROM [filename.txt];' 
)  t
WHERE NOT EXISTS (SELECT 1 FROM Table WHERE ID = t.ID AND name = t.name)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000