Anyways to insert multiple rows into sqlserver?

Question

I am dealing with some sort of daily data and I need to parsing those data out from txt files and save those into sqlserver database. What I did right now is just to read from those txts line by line and insert into tables. But the problem is, for each single day, it has more than 2 million rows which is really time consuming.

I am just wondering, are there any ways I can make it more efficient? Any suggestion is welcome.

Thanks,

John Wiese · Answer

There are a number of ways you could do this depending on different factors. If you have the files in a comma delimited format you could doa Bulk Insert like this: BULK INSERT CSVTest FROM 'c:\csvtest.txt' WITH ( FIELDTERMINATOR = ''  ROWTERMINATOR = '
' ) GO You could also use something like SQL Server Integration Services as shown in this video: http://technet.microsoft.com/en-us/sqlserver/dd941934.aspx

Venkatesan Jayakantham · Answer

Yes john is correct you can put these scripts in the job and schedule it. In addition you can use SSIS Bulk Insert task to upload the data from text files. Cheers Venkatesan Prabu .J http://venkattechnicalblog.blogspot.com/