BULK INSERT from comma delimited string

Question

I have a table with the following data in one column:

abc,2,2,34,5,3,2,34,32,2,3,2,2
def,2,2,34,5,3,2,34,32,2,3,2,2

I want to take this data and insert it into another table, using the commas as delimiters, just like how you can specify the FIELDTERMINATOR in BULK INSERT statements.

Is there a way to do this using T-SQL?

Answer 1

You need to use a Split function to split your string into a table variable, and then insert those values into your table.

There are tons of those split functions out there, with various pros and cons and various number of parameters and so forth.

Here is one that I quite like - very nicely done, clearly explained.

With that function, you should have no trouble converting your column into individual entries for your other table.

Answer 2

I'm not sure if there is any direct way to do in the T-SQL , but if you want to use Bulk Insert you can use sqlcmd to export to CSV file and then Import the file back into server using Bulk Insert.

Create a dbo.Split Functionm, you can refer here split string into multiple record There are tons of good examples.

if you want to execute as batch process, You can execute sqlcmd and 'Bulk Insert'

sqlcmd -S MyServer -d myDB -E -Q "select dbo.Split(col1) from SomeTable" 
       -o "MyData.csv" -h-1 -s"," -w 700

-s"," sets the column seperator to 

bulk insert destTable
from "MyData.csv"
with 
(
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n'
)

Otherwise, You can manipulate directly in the T-SQL, but given you have the same identify of columns definition.

 INSERT INTO DestinationTable
 SELECT dbo.Split(col1) FROM SomeTable

Answer 3

EDIT: allow multiple char separators

This is how I solved it, with two functions to do the splitting into columns (if you want a more complete solution with line splitting as well, see my other post here). It involves:

• A scalar function (fSubstrNth) for extracting the n-th field of a line, given an separator
• A scalar function (fPatIndexMulti) for finding the n-th index of the separator
• (Optional) alternative Right function to accept negative values
• Finally, some specific code to use in your solution, since SQL doesn't allow dynamic table-function definitions (in other words, you can't SELECT from a function with dynamic columns)

Now, for the code snippets:

fSubstrNth

-- =============================================
-- Author:      Bernardo A. Dal Corno
-- Create date: 18/07/2017
-- Description: substring com 2 PatIndex limitando inicio e fim
-- =============================================
CREATE FUNCTION fSubstrNth
(
  @Text varchar(max),
  @Sep varchar(3),
  @N int --Nth campo
)
RETURNS varchar(max)
AS
BEGIN
  DECLARE @Result varchar(max)

  IF @N<1 RETURN ''
  IF @N=1
    SET @Result = substring(@Text, 1, dbo.fPatIndexMulti(@Sep,@Text,1)-1)
  ELSE
    SET @Result = substring(@Text, dbo.fPatIndexMulti(@Sep,@Text,@N-1)+LEN(@Sep), CASE WHEN dbo.fPatIndexMulti(@Sep,@Text,@N)>0 THEN dbo.fPatIndexMulti(@Sep,@Text,@N)-dbo.fPatIndexMulti(@Sep,@Text,@N-1)-LEN(@Sep) ELSE LEN(@Text)+1 END)

  RETURN @Result
END

fPatIndexMulti

-- =============================================
-- Author:      Bernardo A. Dal Corno
-- Create date: 17/07/2017
-- Description: recursive patIndex
-- =============================================
CREATE FUNCTION [dbo].[fPatIndexMulti]
(
  @Find varchar(max),
  @In varchar(max),
  @N tinyint
)
RETURNS int
AS
BEGIN
  DECLARE @lenFind int, @Result int, @Texto varchar(max), @index int
  DECLARE @i tinyint=1

  SET @lenFind = LEN(@Find)-1
  SET @Result = 0
  SET @Texto = @In
  WHILE (@i <= @N) BEGIN
    SET @index = patindex('%'+@Find+'%',@Texto)
      IF @index = 0 RETURN 0
    SET @Result = @Result + @index
    SET @Texto = dbo.xRight(@Texto, (@index + @lenFind)*-1)

    SET @i = @i + 1
  END
  SET @Result = @Result + @lenFind*(@i-2)

  RETURN @Result
END

xRight

-- =============================================
-- Author:      Bernardo A. Dal Corno
-- Create date: 06/01/2015
-- Description: Right inverso (para nros < 0)
-- =============================================
CREATE FUNCTION [dbo].[xRight] 
(
  @Texto varchar(8000),
  @Qntd int
)
RETURNS varchar(8000)
AS
BEGIN
  DECLARE @Result varchar(8000)

  IF (Len(@Texto) = 0) OR (@Qntd = 0)
    SET @Result = ''
  ELSE IF (@Qntd > 0) 
      SET @Result = Right(@Texto, @Qntd)
    ELSE IF (@Qntd < 0)
    SET @Result = Right(@Texto, Len(@Texto) + @Qntd)

  RETURN @Result
END

Specific code

SELECT 
     acolumn = 'any value',
     field1 = dbo.fSubstrNth(table.datacolumn,',',1),
     field2 = dbo.fSubstrNth(table.datacolumn,',',2),
     anothercolumn = 'set your query as you would normally do',
     field3 = (CASE dbo.fSubstrNth(table.datacolumn,',',3) WHEN 'C' THEN 1 ELSE 0 END)
FROM table

Note that:

• fSubstrNth receives the n-th field to extract from the 'datacolumn'
• The query can be as any other. This means it can be stored in a procedure, tabled-function, view, etc. You can extract some or all fields, in any order you wish, and process however you want
• If used in a stored procedure, you could create a generic way of creating a query and temp table that loads the string with dynamic columns, but you have to make a call to another procedure to use the data OR create a specific query like above in the same procedure (which would make it non-generic, just more reusable)