Loading

Anyways to insert multiple rows into sqlserver?

Yili Li

16y8.3k

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,

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

John Wiese16y ago
Accepted 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 = '\n'
)
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

0
Venkatesan Jayakantham16y ago
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/
0