BULK INSERT from a table

Hi there.

I have a table that contains comma delimited text, and I am trying to convert this into another table

eg my target table looks like

Produce|Price|QuantityPerPrice

and my input table contains strings such as

"apples","7.5","10"

"pears","10","8"

"oranges","8","6"

Does anyone have any ideas on how to do this? I am after a solution that does them all at once: I am currently using charindex() to find each column, one at a time, but given the speed of BULK INSERT I would much rather do it as a table. The one solution that I don't want to resort to is to export the table with delimited strings to a data file, then BULK INSERT it...

Cheers

Neil

After reading the problem I am very interested to see the solution as I cant think of one without moving data to file or using charindex.|||Bulk insert or openrowset(bulk) (in SQL Server 2005) requires file as a data source. So what you are trying is not possible. You could however optimize some of the string splitting and do just insert. If there are large number of rows then perform the insert in batches in a transaction to get better performance. But I am confused as to why you would store such inefficient format in the database table and then try to manipulate it relationally. Why can't you store the data directly into the target table?|||

Thanks for the reply.

We are taking inputs with unknown numbers of fields and unknown delimiters, and working with them. We do this by importing the data into a single field and then analysing it. This leads to single-field tables with delimited data in them.

I think we are going to have to export with bcp, create a format file, then re-import with the known delimiter and fieldnames.

Which raises another question: is there a command line parameter I can pass to bulk insert so that it will take a delimiter and use the first row as fieldnames, or do I need a format file?

Regards,

Neil

|||I am still confused as to why you would first import the data into single column values and then try to do the parsing operation on the server. This seems inefficient to me. You could easily design DTS/SSIS packages or write your own scripting code to import the files directly in the first place. There is no facility in any of the bulk API to consider the first row as column names. You will have to skip it using the FIRSTROW parameter in BULK INSERT or similar option in BCP. There are also options to specify delimiter. Use of format files depends on the complexity of your data format. For the simple, delimiter case you may not require it. See BOL for more details on the command syntax and the BCP utility.