x

Insert comma seperated values into multiple column

How to insert comma seperated values into multiple columns of a single table? can't get the logic. Please help
more ▼

asked Nov 14, 2010 at 11:41 PM in Default

Elza gravatar image

Elza
11 1 1 1

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

3 answers: sort voted first

You can use a [BCP][1] utility or create a SSIS package for that purpose. If you need to transform the data in some way, then the SSIS will be the right choice.

[1]: http://msdn.microsoft.com/en-us/library/ms162802.aspx
more ▼

answered Nov 14, 2010 at 11:52 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Am working in SQL server 2005. Can you please help me with the code?
Nov 15, 2010 at 12:49 AM Elza
(comments are locked)
10|1200 characters needed characters left

I will second Pavel in saying that SSIS is probably the best way to go in general.

With that said, I want to point out that there are many, many ways to import CSV files into SQL Server. You can use bulk insert, BCP, DTS, or opendata source (I wrote an article about that here: http://www.sqlservercentral.com/articles/61552/).

You could also write a custom import routine in another language like C# or Python (http://www.simple-talk.com/sql/database-administration/python-for-the-sql-server-dba/). I have found writing custom import scripts in python handy on a couple of occassions, but those involved cases where I needed to do significant preprocessing of the data before it came into SQL Server.

There is another very similar question on this site at [http://ask.sqlservercentral.com/questions/3519/import-data-from-a-csv-file][3]

[3]: http://ask.sqlservercentral.com/questions/3519/import-data-from-a-csv-file
more ▼

answered Nov 15, 2010 at 10:00 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

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

Check out Using Import/Export, [Bulk Insert][2] from Pinal Dave.

If you are dealing with huge data, its better to switch on to SIMPLE RECOVERY MODE to restrict the log growth.

Remember you cannot do more complex operation with any of the methods above, Prefer SSIS when your do complicated data transforms or operations.

[2]: http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
more ▼

answered Nov 15, 2010 at 12:58 AM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

A very good answer, though I am leery of ever permitting a production database to touch simple recovery mode. Generally, I simply accept the log growth.

If I ever had to import something so massive that accepting the log growth on a production server was untenable, then I think I would look at breaking it into chunks and doing log backups in between rather than permit simple recovery mode, especially if there was any chance of any other concurrent activity.
Nov 15, 2010 at 10:57 AM TimothyAWiseman
(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.

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:

x109
x35

asked: Nov 14, 2010 at 11:41 PM

Seen: 1930 times

Last Updated: Nov 14, 2010 at 11:54 PM