如何将字符串传递给批量插入而不是文件? [英] How to pass String to Bulk insert instead of file?
问题描述
我以前使用大容量插入命令来转换Csv文件的int表。现在我将CSV文件保存为VarBinary值在sql server中。现在我可以通过使用CAST和CONVERT函数将其类型转换为Varchar来从Varbinary文件中获取数据。但是现在我遇到一个问题,我无法使用批量插入将包含CSV内容的Varchar字符串转换为表。任何人都可以帮我
我的示例代码如下:
I used to use bulk insert command to Convert a Csv file int table.Resently i saved a CSV file as a VarBinary value in sql server.Now I can get data from Varbinary file by typecasting it to Varchar using CAST and CONVERT functions.But now i got an issue i cant convert this Varchar String containing csv content to table using bulk insert.Can any one help me My example code is given below:
--@String contains varchar value of CSV file content.
SET @sql = 'BULK INSERT TempCsv
FROM ''' + @String + '''
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\n'',
TABLOCK
)'
请帮帮我,有什么方法或替代方法可以将csv字符串中的数据插入表中。
Please help me.Is there any way or alternative to insert data from csv string to table.
推荐答案
编辑:允许使用多个字符分隔符
allow multiple char separators
这就是我的方法解决了。它涉及:
This is how I solved it. It involves:
- 用于拆分换行符的表值函数(
xftSplit
) (char(10))插入表格行 - 用于提取行的第n个字段的标量函数(
fSubstrNth
),给定分隔符 - 用于查找分隔符的第n个索引的标量函数(
fPatIndexMulti
) b - (可选)替代
向右
函数以接受负值 - 最后,在解决方案中使用一些特定的代码,因为SQL不允许动态表函数定义(换句话说,您不能从具有动态列的函数中进行
SELECT
)
- A table-valued function (
xftSplit
) for spliting line breaks (char(10)) into table lines - 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)
现在,用于代码段:
xftSplit
-- =============================================
-- Author: Bernardo A. Dal Corno
-- Create date: 15/07/2014
-- Description: Quebra valores a partir de caracteres e retorna lista em tabela
-- =============================================
CREATE FUNCTION [dbo].[xftSplit]
(
@Texto varchar(max),
@Splitter varchar(3)
)
RETURNS
@Lista TABLE
(
ValoresQuebrados varchar(8000)
)
AS
BEGIN
DECLARE @Pos Smallint
While len(@Texto)>0 BEGIN
SET @Pos = Patindex('%'+@Splitter+'%',@Texto)
IF @Pos > 0 BEGIN
INSERT INTO @Lista
SELECT left(@Texto, @Pos-1)
SET @Texto = right(@Texto, len(@Texto)-@Pos)
END
ELSE BEGIN
INSERT INTO @Lista
SELECT @Texto
SET @Texto = ''
END
End
RETURN
END
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(line,',',1),
field2 = dbo.fSubstrNth(line,',',2),
anothercolumn = 'set your query as you would normally do',
field3 = (CASE dbo.fSubstrNth(line,',',3) WHEN 'C' THEN 1 ELSE 0 END)
FROM (
SELECT line = ValoresQuebrados FROM dbo.xftSplit(@StringVariable, char(10))
) lines
请注意:
-
fSubstrNth
接收要从行中提取的第n个字段 -
xftSplit
接收到一个变量,该变量包含要从中进行批量存储的字符串(无论来源如何)以及char(10)
作为的分隔符\n
,但也可以是其他任何内容 - 查询可以与其他任何查询相同。这意味着它可以存储在过程,表函数,视图等中。您可以按所需的任何顺序提取一些或所有字段,并根据需要进行处理
- 如果使用在存储过程中,您可以创建一种创建查询和临时表的通用方法,该表将使用动态列加载字符串,但是您必须调用另一个过程以使用数据,或者像上面一样在上面创建特定的查询程序(这将使其变得非通用,只是可重用)
fSubstrNth
receives the n-th field to extract from the linexftSplit
receives a variable that contains the string you want to bulk from (whatever the source) and achar(10)
as the splitter of\n
, but it could be anything else- 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)
这篇关于如何将字符串传递给批量插入而不是文件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!