I store csv strings in a datatable. I later need to create a temporary table from that csv, but BULK INSERT only offers a filename as datasource.
Is there any possibility to import from a string?
Thank you and regards
Gabriel
-
1I assume you mean you want to parse out the delimited data into rows? The best way to deal with that is to not store delimited data. It violates the very concept of relational data.Sean Lange– Sean Lange2017-11-07 15:51:30 +00:00Commented Nov 7, 2017 at 15:51
-
A small sample would be helpful. Is your csv muli-line? Does it contain headers,John Cappelletti– John Cappelletti2017-11-07 16:10:35 +00:00Commented Nov 7, 2017 at 16:10
Add a comment
|
1 Answer
In general, it is not desirable to store unnormalized CSV data in a SQL Server table. It makes it very hard to query and work with the data. That being said, sometimes we have to live with bad design decisions. That having been said, you could try writing your CSV column to file. From the query menu of SSMS choose SQLCMD mode, and then type the following:
:OUT c:\path\to\your\file.csv
SET NOCOUNT ON;SELECT csv_column FROM dbo.yourTable
Now that you have a bona fide CSV file, you should be able to use BULK INSERT. Note that I have assumed here that the CSV data which you want to import is contained within a single column csv_column, and that the data is well formed (e.g. each record has the same number of commas etc.).
1 Comment
Brobic Vripiat
Is there a way to do this where entire CSV string is given through a VARCHAR(MAX) parameter passed to SP? Then do the bulk insert all in memory without saving CSV to physical file? In multi user environment you can see that a static file name can be stepped over by multiple users in concurrent scenario. I want to avoid devising a unique file naming scheme and then file cleanup system.