inserting into a table from a CSV file

[johnrg1]

Hi guys,

I want to insert records into a table from a csv file. the file currently hold 700+ records, and there are 2 new records added each week, but they need to entered seperatly into the database on seperate days.

I have seen the bulk insert and concidered deleting all the records and then insert all the records in the file but that just seems a waste of resourses. is there a way to specify which rcords to add. the primary key is the date.
is there a way to find the last records date, and add any records where the date is greater?

Cheers.

 

[SQLSister]

Sure use DTS to set up a package. YOu can do a data driven query or just write the SQL code to do what you want. OFten, we'll import the table into a temp table do some sort of data cleanup and then insert/update the records. DTS is very flexible, but if you are going to do this you might want ot get a book on DTS as Books Online isn't really very helpful on DTS. I was going to tell you the name of the book I use and I know Terry recommends another, but my book seems to have wandered away temporarily. But serach for DTS in the Tek-tips search and you should find a thread that list the book names.

 

[tlbroadbent]

I'd just truncate the table and import the entire file each time. The entire operation would take a couple of seconds. Checking the dates would add unnecessary complexity.

Here are two possible methods of importing only new rows.

1) Bulk insert into a holding table and then add to the permanent table using an INSERT statement and testing if the date already exists in the table.

Insert PermTable
Select * From HoldTable
Where not exists
(Select * From PermTable Where KeyCol=HoldTable.KeyCol)

2) Create a DTS package to import the file as SQLSister recommended. Get the latest date in the table and store in a Global variable. Create a data transformation that compares the date on the rows in the file to the global variable and skips those that are not to be loaded. See SQL BOL and the SQLDTS.com website for details.

For 700 static rows or even 7000, these methods will probably take longer than simply truncating the table importing the file.

I like the book, "Professional SQL Server 2000 DTS (Data Transformation Service)" by Mark Chaffin, Brian Knight, Todd Robinson. Published by WROX.
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[johnrg1]

Cheers Guys.

I think it will be the bulk insert then,

what is the quickest way to delete all the records?

 

[tlbroadbent]

Truncate table tablename

Note: Only the database owner or system admin can execute Truncate Table. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[johnrg1]

Your always there when your needed

What a star.

 

[johnrg1]

Now, this is the complicated bit, the file is located on a seperate webserver.

Is there a way for SQL server to access it directly ( if there is is is not doing) or is there a way to pass the file to the database from the program that is executing the command. The program can successfully copy the file. but again, the program can be run on any Pc, not just that with the database on it.

Thank for any help.