Importing data to SQL from flat file

Question

Importing data to SQL from flat file

Timothy Moore

SSC Veteran

Points: 264
More actions
March 26, 2007 at 9:36 am

#172596

Hello,
I am trying to import a flat file from a share on my server to my SQL database on a daily basis. I was wondering if anyone could point me in the right direction as to how to do this or if you know of any good resources to help me accomplish this task. Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

the BULK INSERT function in SQL is the most effecient. If you know the layout of the file will always be the same, and the data rows are the differences, I would recommend that first; huge files import much faster than using a programming language and doing row by row processing.

BULK INSERT can import delimited files or fixed with files; here's an example syntax, but BOL has a lot of good information on the definition:

BULK INSERT BULKACT FROM 'C:\Clients\YourDataFile.TXT'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n',

FIRSTROW = 2

)

If you cannot use BULK INSERT, you could use any programming language to do it; the basics are:

open a file.

open a recordset for the table in question.

read each row of the file, insert into the recordset. update teh recordset to place the data in the server's table.

close the file.

If you need to UPDATe existing tables witht he new data, I recommend inserting the data into a temp table first, then update the desired data from the temp table. that way it can be done as a single set based operation, in a quick transaction ; if you open a trasnaction in a programming language, and then do row-by-row-processing, it takes a long time, and could lead to blocking of other users.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff-67583 SSCertifiable Points: 5625 More actions · Answer 2

Hello,

We use DTS packages (which can be schedulked as a job) and/or stor procs (which can also be called by a job). We (DBA & Developers) have found this methodology to be visually easier to understand, edit, document and troubleshoot. Having said this, to avoid performance impacts, we run the jobs during low db usage periods for large imports/data manipulations.

Many thanks. Jeff

Timothy Moore SSC Veteran Points: 264 More actions · Answer 3

So i have a flat file that is uploaded into my share every day. I need to run this job everyday to import the new data. any sample coding or suggestions on how to check the existing database for new data, if data is not found, import new file?

thanks