我参考了其他一些博客，我无法将问题隔离到这个逻辑。代码如下。抛出附加的错误消息。
-- 1. Import Multiple Delimited Text Files into a SQL Database
-- 1.1 Define the path to the input and define the terminators
USE [Openair_Integration]
DECLARE @Path NVARCHAR(255) = 'C:\\Users\nicolas.gutierrez.su\Downloads\\'
DECLARE @RowTerminator NVARCHAR(5) = CHAR(13) + CHAR(10)
DECLARE @ColumnTerminator NVARCHAR(5) = CHAR(9)
-- 1.2 Define the list of input and output in a temporary table
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, 'booking.csv', 'dbo.booking'
-- 1.3 Loop over the list of input and output and import each file to the correct table
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 + '''
)'
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 + '''
)'
EXEC(@SQL)
SET @Counter = @Counter + 1
END;
-- 1.4 Cleanup temporary tables
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];
