SQL Error in Insert after 9th records

Question

Please help,

Actually my query is OK, but the problem is when after inserting the 9th record out of 10 records it says that there is an error on the 10th record, but if i run the query separately; 1st query have 9 records then the 2nd query has only 1 and both has no error.

below is the query that i used:

DECLARE @tblLkUp    VARCHAR(MAX),
        @tblLkUpCol VARCHAR(MAX),
        @strSQL     VARCHAR(MAX)

SET @tblLkUp = 
    (SELECT DISTINCT TOP 1 t.name AS TableName 
     FROM sys.tables t JOIN sys.indexes i ON i.index_id = t.schema_id  
     WHERE t.name LIKE '%$POS Lookup')

SET @tblLkUpCol = 
    (SELECT DISTINCT TOP 1 t.name AS TableName 
     FROM sys.tables t JOIN sys.indexes i ON i.index_id = t.schema_id  
     WHERE t.name LIKE '%$POS Lookup Columns')


SET @strSQL = 'IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUp) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'') = 0
                    BEGIN
                        INSERT INTO ' + QUOTENAME(@tblLkUp) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''Zoom Trans. SPO Line Details'', '''', 
                        ''ZOOM'', ''1'', ''99008981'', ''2'', ''10'', ''0'', ''0'', '''', ''0'', ''0'', ''0'', '''', '''', ''0'', ''0'', 
                        ''0'', ''0'', ''0'', ''0'', '''', ''1'', '''', '''', '''', '''', '''', ''0'', ''0'', ''0'', '''', '''', ''0'', ''0'', 
                        ''0'', ''0'', '''', '''', ''0'', ''0'', ''0'', ''0'', ''0'', ''0'', ''0'', ''0'')

                        IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUpCol) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'' AND [Table No_] = ''99008981'' AND [Column No_] = ''1'') = 0
                            BEGIN
                                INSERT INTO ' + QUOTENAME(@tblLkUpCol) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''99008981'', ''1'', ''2'', ''0'', ''0'', '''', ''0'', ''0'', '''', ''0'', ''0'', ''0'', '''')
                            END

                        IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUpCol) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'' AND [Table No_] = ''99008981'' AND [Column No_] = ''2'') = 0
                            BEGIN
                                INSERT INTO ' + QUOTENAME(@tblLkUpCol) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''99008981'', ''2'', ''5'', ''0'', ''0'', '''', ''0'', ''0'', '''', ''0'', ''0'', ''0'', '''')
                            END

                        IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUpCol) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'' AND [Table No_] = ''99008981'' AND [Column No_] = ''3'') = 0
                            BEGIN
                                INSERT INTO ' + QUOTENAME(@tblLkUpCol) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''99008981'', ''3'', ''10'', ''0'', ''0'', '''', ''0'', ''0'', '''', ''0'', ''0'', ''0'', '''')
                            END

                        IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUpCol) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'' AND [Table No_] = ''99008981'' AND [Column No_] = ''4'') = 0
                            BEGIN
                                INSERT INTO ' + QUOTENAME(@tblLkUpCol) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''99008981'', ''4'', ''10012700'', ''0'', ''0'', '''', ''0'', ''0'', '''', ''0'', ''0'', ''0'', '''')
                            END

                        IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUpCol) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'' AND [Table No_] = ''99008981'' AND [Column No_] = ''5'') = 0
                            BEGIN
                                INSERT INTO ' + QUOTENAME(@tblLkUpCol) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''99008981'', ''5'', ''10012701'', ''0'', ''0'', '''', ''0'', ''0'', '''', ''0'', ''0'', ''0'', '''')
                            END

                        IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUpCol) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'' AND [Table No_] = ''99008981'' AND [Column No_] = ''6'') = 0
                            BEGIN
                                INSERT INTO ' + QUOTENAME(@tblLkUpCol) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''99008981'', ''6'', ''10012702'', ''0'', ''0'', '''', ''0'', ''0'', '''', ''0'', ''0'', ''0'', '''')
                            END

                        IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUpCol) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'' AND [Table No_] = ''99008981'' AND [Column No_] = ''7'') = 0
                            BEGIN
                                INSERT INTO ' + QUOTENAME(@tblLkUpCol) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''99008981'', ''7'', ''10012703'', ''0'', ''0'', '''', ''0'', ''0'', '''', ''0'', ''0'', ''0'', '''')
                            END

                        IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUpCol) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'' AND [Table No_] = ''99008981'' AND [Column No_] = ''8'') = 0
                            BEGIN
                                INSERT INTO ' + QUOTENAME(@tblLkUpCol) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''99008981'', ''8'', ''10012704'', ''0'', ''0'', '''', ''0'', ''0'', '''', ''0'', ''0'', ''0'', '''')
                            END

                        IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUpCol) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'' AND [Table No_] = ''99008981'' AND [Column No_] = ''9'') = 0
                            BEGIN
                                INSERT INTO ' + QUOTENAME(@tblLkUpCol) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''99008981'', ''9'', ''10012705'', ''0'', ''0'', '''', ''0'', ''0'', '''', ''0'', ''0'', ''0'', '''')
                            END

                        IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUpCol) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'' AND [Table No_] = ''99008981'' AND [Column No_] = ''10'') = 0
                            BEGIN
                                INSERT INTO ' + QUOTENAME(@tblLkUpCol) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''99008981'', ''10'', ''10012750'', ''0'', ''0'', '''', ''0'', ''0'', '''', ''0'', ''0'', ''0'', '''')
                            END
                    END'
EXEC (@strSQL)

Thanks in advance.

`Msg 105, Level 15, State 1, Line 55 Unclosed quotation mark after the character string ''. Msg 102, Level 15, State 1, Line 55 Incorrect syntax near ''.` — Argel Joseph, Mar 06 '12 at 06:25
Do `print @strSQL` at the end and analyze the query that has been built. — Mikael Eriksson, Mar 06 '12 at 06:26
I did that `print @strSQL` Mikael, and it says i have an error on the 10th record..`IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUpCol) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'' AND [Table No_] = ''99008981'' AND [Column No_] = ''10'') = 0 BEGIN INSERT INTO ' + QUOTENAME(@tblLkUpCol) + ' VALUES(DEFAULT, '''', ''ZOOMSPODTL'', ''99008981'', ''10'', ''10012750'', ''0'', ''0'', '` — Argel Joseph, Mar 06 '12 at 06:35

score 1 · Accepted Answer · answered Mar 06 '12 at 06:45

1

The string you concatenate is truncated to 4000 characters. To fix this you can add cast('' as varchar(max)) first in the assignment to @strSQL.

SET @strSQL = cast('' as varchar(max)) + 
              'IF (SELECT COUNT(*) FROM ' + QUOTENAME(@tblLkUp) + ' WHERE [Lookup ID] = ''ZOOMSPODTL'') = 0
                    BEGIN
                        INSERT INTO .......

answered Mar 06 '12 at 06:45

Mikael Eriksson

136,425
22
210
281

^^, thanks Mikael,, it works.. but, can u tell me about this `CAST` and how it works..:) – Argel Joseph Mar 06 '12 at 06:53
@ArgelJoseph - Well... I remember seeing this before somewhere but I can't find it now. I think it has to do with type conversion when you concatenate the strings. The string literals is not `varchar(max)` and `quotename` isn't `varchar(max)`either so the result will not be a `varchar(max)`. Adding `cast('' as varchar(max))` makes sure that at least one string in there is a `varchar(max)` so the end result will be a `varchar(max)`. – Mikael Eriksson Mar 06 '12 at 06:59
Found another question on SO with a better explanation. http://stackoverflow.com/questions/1371383/for-nvarcharmax-i-am-only-getting-4000-characters-in-tsql – Mikael Eriksson Mar 06 '12 at 07:02

SQL Error in Insert after 9th records

1 Answers1