批量导入 T-SQL 忽略双引号 (")

Question

我正在使用很久以前找到的原始脚本，用于将许多CSV文件批量导入SQL数据库。与小的调整脚本是为我工作，但我不能摆脱双引号作为FIELDQUOTE。

DECLARE @Path NVARCHAR(255) = 'D:TEMP'
DECLARE @RowTerminator NVARCHAR(5) = CHAR(13) + CHAR(10)
DECLARE @ColumnTerminator NVARCHAR(5) = CHAR(44)
DECLARE @FieldQuote NVARCHAR(5) = CHAR(34)
--DECLARE @FieldQuote NVARCHAR(5) = '"'
IF OBJECT_ID('[dbo].[Files_Temporary]', 'U') IS NOT NULL
DROP TABLE [dbo].[Files_Temporary];
CREATE TABLE [dbo].[Files_Temporary]
(
[ID] INT
, [FileName] NVARCHAR(255)
, [TableName] NVARCHAR(255)
);
INSERT INTO [dbo].[Files_Temporary] SELECT 1,       'MyFile.txt',       'MyfileA'
INSERT INTO [dbo].[Files_Temporary] SELECT 2,       'MyFile.txt',       'MyFileB'
DECLARE @Counter INT = 1
WHILE @Counter <= (SELECT COUNT(*) FROM [dbo].[Files_Temporary])
BEGIN
PRINT 'Counter is ''' + CONVERT(NVARCHAR(5), @Counter) + '''.'
DECLARE @FileName NVARCHAR(255)
DECLARE @TableName NVARCHAR(255)
DECLARE @Header NVARCHAR(MAX)
DECLARE @SQL_Header NVARCHAR(MAX)
DECLARE @CreateHeader NVARCHAR(MAX) = ''
DECLARE @SQL_CreateHeader NVARCHAR(MAX)
SELECT @FileName = [FileName], @TableName = [TableName] FROM [dbo].[Files_Temporary] WHERE [ID] = @Counter
IF OBJECT_ID('[dbo].[' + @TableName + ']', 'U') IS NULL
BEGIN
PRINT 'Creating new table with name ''' + @TableName + '''.'
IF OBJECT_ID('[dbo].[Header_Temporary]', 'U') IS NOT NULL
DROP TABLE [dbo].[Header_Temporary];
CREATE TABLE [dbo].[Header_Temporary]
(
[Header] NVARCHAR(MAX)
);
SET @SQL_Header = '
BULK INSERT [dbo].[Header_Temporary]
FROM ''' + @Path + @FileName + '''
WITH
(
FIRSTROW = 1,
LASTROW = 1,
MAXERRORS = 0,
FIELDTERMINATOR = ''' + @RowTerminator + ''',
ROWTERMINATOR = ''' + @RowTerminator + ''',
FIELDQUOTE = ''' + @FieldQuote + '''
)'
EXEC(@SQL_Header)
SET @Header = (SELECT TOP 1 [Header] FROM [dbo].[Header_Temporary])
PRINT 'Extracted header ''' + @Header + ''' for table ''' + @TableName + '''.'
WHILE CHARINDEX(@ColumnTerminator, @Header) > 0
BEGIN          
SET @CreateHeader = @CreateHeader + '[' + LTRIM(RTRIM(SUBSTRING(@Header, 1, CHARINDEX(@ColumnTerminator, @Header) - 1))) + '] NVARCHAR(255), '
SET @Header = SUBSTRING(@Header, CHARINDEX(@ColumnTerminator, @Header) + 1, LEN(@Header)) 
END
SET @CreateHeader = @CreateHeader + '[' + @Header + '] NVARCHAR(255)'
SET @SQL_CreateHeader = 'CREATE TABLE [' + @TableName + '] (' + @CreateHeader + ')'
EXEC(@SQL_CreateHeader)
END
PRINT 'Inserting data from ''' + @FileName + ''' to ''' + @TableName + '''.'
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
BULK INSERT [dbo].[' + @TableName + ']
FROM ''' + @Path + @FileName + '''
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ''' + @ColumnTerminator + ''',
ROWTERMINATOR = ''' + @RowTerminator + ''',
FIELDQUOTE = ''' + @FieldQuote + '''            
)'
EXEC(@SQL)

SET @Counter = @Counter + 1
END;
IF OBJECT_ID('[dbo].[Files_Temporary]', 'U') IS NOT NULL
DROP TABLE [dbo].[Files_Temporary];
IF OBJECT_ID('[dbo].[Header_Temporary]', 'U') IS NOT NULL
DROP TABLE [dbo].[Header_Temporary];

我声明了一个变量，但是两者都没有给我正确的结果。导入成功，但是fieldquote没有被忽略。

DECLARE @FieldQuote NVARCHAR(5) = CHAR(34)
--DECLARE @FieldQuote NVARCHAR(5) = '"'

我使用的是SSMS V18.10

Answer 1

只需像下面这样再添加一个设置来处理双引号。

可以从SQL Server 2017开始使用

/p>

...
WITH (
FORMAT='CSV'
...