Geek asks and answers

BULK INSERT from comma delimited string

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 the data and insert it into another table using commas as separators, just like you can specify operators FIELDTERMINATORin BULK INSERT.

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

+3
source share
3 answers

You need to use a function Splitto split your row into a table, and then paste these values ​​into your table.

There are tons of these separated functions with various pros and cons and various numbers of parameters, etc.

, - , .

.

+3

, - T-SQL, Bulk Insert, sqlcmd CSV , , .

dbo.Split, .

, sqlcmd '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'
)

T-SQL, , .

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

EDIT: char

, ( , . ). :

  • (fSubstrNth) n-
  • (fPatIndexMulti) n-
  • () Right
  • , - , SQL ( , SELECT )

, :

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


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

, :

  • fSubstrNth n- 'datacolumn'
  • . , , tabled, view .. ,
  • If used in a stored procedure, you can create a general way to create a query table and temp that loads a row with dynamic columns, but you must make another procedure call to use the data or create a specific request as above in the same procedure (which would do its nonequivalent, just more reusable).
0
source

More articles:


All Articles