x

Stored procedure for data importing from flat files. Need some tips.

I am currently working on creation of SP to automate the process of importing flat data into SQL Server.

I want you to give me some useful advice on the best way how to realise, taking into account things I need:

  1. File path and name, delimeter sign, table name as variables.

  2. Logging process of importing in one file for every import task (errors and output results like in bcp)

  3. Process flat files with different column order

  4. Check if data we are trying to import already exists in database (don't replicate)

  5. Batch import

  6. Error cathing

Okay, now my thoughts. I would use either bcp or BULK INSERT.

Declare delimeter, source file, table as variables.

To identify column order I think we should parse first row with names and use intermediate table from which we then INSERT...SELECT into destination table in the right order.

Check if data already EXISTS before importing.

Still don't know about logging in one file with timeline. Any other errors that may occur?

Any advice would be appreciated. Thank you in advance.
more ▼

asked Apr 19 at 10:59 AM in Default

KMaverickZR gravatar image

KMaverickZR
1 1 1 1

This site works by voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
Apr 23 at 09:59 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Refer to this forum post on exporting logging information to a flat file http://social.msdn.microsoft.com/forums/sqlserver/en-US/40b0334e-5c44-403d-9e9f-6cdf67a8f3ff/export-to-csv-file-using-tsql-from-ssms

As suggested earlier, SSIS would be the best option suited in this scenario. Refer to this tutorial on importing flat files: http://technet.microsoft.com/en-us/library/ms169917.aspx
more ▼

answered Apr 22 at 04:18 AM

Venkataraman gravatar image

Venkataraman
1k 1 3

(comments are locked)
10|1200 characters needed characters left

You have a list of task to perform.

Please go ahead with SSIS package as performing all this in SQL Server will be bit complex. Using SSIS you can perform this and lot more with ease.
more ▼

answered Apr 21 at 01:12 PM

vivekyadav0212 gravatar image

vivekyadav0212
481 2 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x414
x290
x66
x42
x32

asked: Apr 19 at 10:59 AM

Seen: 704 times

Last Updated: Apr 23 at 09:59 AM