Identifier that starts with is too long. Max length is 128

Question

I referenced a few other blogs and I can't isolate the issue to this logic. Code is below. Throws the error message attached.

            -- 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];

Chris Rolliston · Accepted Answer · 2019-08-17T19:44:14.230

This line

DECLARE @ColumnTerminator NVARCHAR(5) = CHAR(9)

makes the assumption that columns are tab-delimited (9 = ASCII code for a tab character). Later on, the header row is split using this loop:

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

Since the actual column delimiter is a comma, this loop is a no-op, so things fall through to the next line:

SET @CreateHeader = @CreateHeader + '[' + @Header + '] NVARCHAR(255)'

Here you are effectively attempting to define a single column named after the entire header row! Since the total number of characters in the header row is over the maximum length of a T-SQL identifier (128), the resulting CREATE TABLE statement raises an error.

To resolve the error for the file you have, the @ColumnTerminator assignment at the top just needs to be fixed to specify your actual column delimiter, i.e. a comma:

DECLARE @ColumnTerminator NVARCHAR(5) = N','

However, there are some other minor issues with your code, together with a small elephant in the room:

You declare using NVARCHAR not VARCHAR throughout, but use non-Unicode literals which makes me doubt you are really looking to support Unicode (Unicode literals in T-SQL are where you add the N prefix).
Nitpicky, but the traditional Windows max path length is 260 not 255; the inconsistent double slashing is also a bit odd.
You create permanent tables named XXXX_Temporary, which you then drop at the end. Why not create actual temporary tables in that case? (CREATE TABLE #TableName)
If you are on a more recent version of SQL Server, you can modernise the IF OBJECT_ID('TableName') IS NOT NULL DROP TABLE TableName statements to DROP TABLE IF EXISTS TableName
Similarly, the STRING_SPLIT function in a modern version provides a better way of simple splitting than your WHILE loop, which makes repeated small memory allocations.
At the moment you are blindly hardcoding square brackets. When you are hardcoding the name as well, and the name clearly does not need escaping, that's a bit pointless ('[dbo].[Something]'). Conversely, to properly escape unknown identifiers, you should use the QUOTENAME function instead, which will deal with any embedded square brackets properly.
Until SQL Server 2017 explicitly added a proper 'CSV' option, be warned that BULK INSERT does not handle 'real' CSV files, and instead parses data very simplistically. So, if you set FIELDTERMINATOR to a comma, then the following would not import properly:

"Flat 1, Bishopstoke Views","High Street",Newtown
If you can't rely on import files having set formatting settings (or put another way: if you need to be flexible over what your BULK INSERT settings are), then sticking to a pure T-SQL approach isn't the best one - you'd be better off doing the file and string parsing in another language more suited to the task. It's not that procedural T-SQL doesn't 'work', it's just that it's no where near state of the art for imperative scripting - you'd be better off writing something in Python or whatever, and only use T-SQL on the edges. In fact, I'd even prefer using VBA over pure T-SQL for this sort of job.

Identifier that starts with is too long. Max length is 128

1 Answers1