Search code examples
sql-servercsvinsertbulk

SQL Server : bulk insert from csv string stored in column

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

Solution

  • 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.).