SQL Server remove trailing comma from XML path

Question

I have the following code that pulls what my columns are called within a given table of mine:

SELECT 
   column_name + ','
FROM   
   information_schema.columns 
WHERE  
   table_name = 'maintReq'
FOR XML PATH('')

And I am wanting to place that into my current query:

SET IDENTITY_INSERT maintReq ON; 
GO

INSERT INTO maintReq  
OUTPUT Inserted.ID 
VALUES ((SELECT ISNULL(MAX(id) + 1, 0) 
         FROM maintReq WITH(SERIALIZABLE, UPDLOCK)                   
        ),'MAYBE', 'true'); 

SET IDENTITY_INSERT maintReq OFF;

I've tried to do the following myself:

SET IDENTITY_INSERT maintReq ON; 
GO
INSERT INTO maintReq (
                      SELECT 
                         column_name + ','
                      FROM   
                         information_schema.columns 
                      WHERE  
                         table_name = 'maintReq'
                      for 
                         xml path('')
                     )
   OUTPUT Inserted.ID 
      VALUES (
              (
               SELECT                                       
                  ISNULL(MAX(id)+1,0) 
               FROM     
                  maintReq WITH(SERIALIZABLE, UPDLOCK)                   
              ),'MAYBE', 'true'
             ); 
SET IDENTITY_INSERT maintReq OFF;

But with that I am getting the error of:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'SELECT'.

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ')'.

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ','.

Not sure if that error is called by the extra comma that was added to the output of the XML path or if its something else?

My full stored procedure looks like this:

DECLARE @SQLQuery VARCHAR(MAX);
    SET @SQLQuery = 'SET IDENTITY_INSERT ' + @val1 + ' ON 
                     INSERT INTO ' +
                        @val1 + ' 
                        OUTPUT Inserted.ID 
                     VALUES ' + 
                        '(
                            (
                                SELECT 
                                    ISNULL(MAX(id)+1,0) 
                                FROM 
                                    ' + @val1 + ' WITH(SERIALIZABLE, UPDLOCK)
                            ),''' + @val2 + ''', ''' + @val3 + '''
                        ) ' +
                     'SET IDENTITY_INSERT ' + @val1 + ' OFF;'
    EXEC [dbo].[_chkQ] @SQLQuery

The above SP is what I am currently getting this error:

An explicit value for the identity column in table 'maintReq' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Thanks to @Pரதீப் this is the final working query code:

SET @SQLQuery = 'SET IDENTITY_INSERT ' + @val1 + ' ON 
                     INSERT INTO ' + @val1 + '(' + 
                        Stuff(
                               (SELECT 
                                    ',' + quotename(column_name) 
                                FROM  
                                    information_schema.columns 
                                WHERE 
                                    table_name = '' + @val1 + '' 
                                FOR xml path('')
                               ), 1, 1, ''
                             ) + 
                     ')
                        OUTPUT Inserted.ID 
                     VALUES ' + 
                        '(
                            (
                                SELECT 
                                    ISNULL(MAX(id)+1,0) 
                                FROM 
                                    ' + @val1 + ' WITH(SERIALIZABLE, UPDLOCK)
                            ),''' + @val2 + ''', ''' + @val3 + '''
                        ) ' +
                     'SET IDENTITY_INSERT ' + @val1 + ' OFF;'

You can't use the INSERT statement this way. You should make a dynamical query and then execute it. Of course, the extra comma has to be deleted. — Denis Rubashkin, Dec 27 '17 at 15:18
Your `Values` clause is fixed with 3 columns. Why do you need dynamic columns? — TriV, Dec 27 '17 at 15:22
Well, this entire thing seems like an XYProblem to me. Why do you need the column names from `information_schema.columns` anyway? Column names must be hard coded in the sql statement, or the entire statement must be dynamic SQL. — Zohar Peled, Dec 27 '17 at 15:27
@ZoharPeled if I do not have that then it get the error of **An explicit value for the identity column in table 'maintReq' can only be specified when a column list is used and IDENTITY_INSERT is ON.** — StealthRT, Dec 27 '17 at 15:28
Then specify the columns list. Also, why would you want to explicitly insert values into an identity column? That's usually a sign of bad design. — Zohar Peled, Dec 27 '17 at 15:30
@ZoharPeled I'm using the same stored procedure for all my tables which, of course, have different table and column names. — StealthRT, Dec 27 '17 at 15:33
"I'm using the same stored procedure for all my tables" - as I said - bad design. — Zohar Peled, Dec 27 '17 at 15:42
@ZoharPeled agree but in some cases (mine included) its a good thing to do since I do not want to alter all my stored procedures if I change a table column name or add or take away some columns from any table I am using this 1 SP with. — StealthRT, Dec 27 '17 at 15:46
Perhaps someone should write a book - "when bad designs are a good thing". I'll bet it would be a very short book. — Zohar Peled, Dec 27 '17 at 15:53

score 2 · Accepted Answer · answered Dec 27 '17 at 15:40

2

You need to use dynamic sql

DECLARE @col_list VARCHAR(8000)= ''

SET @col_list = Stuff((SELECT ',' + quotename(column_name) --"quotename" is to escape illegal characters
                       FROM   information_schema.columns
                       WHERE  table_name = 'maintReq'
                       FOR xml path('')), 1, 1, '')
SET IDENTITY_INSERT maintReq ON;

EXEC ('
INSERT INTO maintReq ('+@col_list+')
   OUTPUT Inserted.ID 
      VALUES (
              (SELECT                                       
                  ISNULL(MAX(id)+1,0) 
               FROM     
                  maintReq WITH(SERIALIZABLE, UPDLOCK)                   
              ),''MAYBE'', ''true''
             ); ')

SET IDENTITY_INSERT maintReq OFF;

answered Dec 27 '17 at 15:40

Pரதீப்

91,748
19
131
172

Thanks for the example. I updated my OP with my current stored procedure. – StealthRT Dec 27 '17 at 15:42
@StealthRT - I have even showed you how to remove the extra comma. Not sure what exactly is your question – Pரதீப் Dec 27 '17 at 15:45
I was just showing you that I am already using a dynamic SQL **@SQLQuery**. That's all. – StealthRT Dec 27 '17 at 15:47
Thanks, I finally got it working with my own SP code. I updated my OP to show the working code. – StealthRT Dec 27 '17 at 16:01

SQL Server remove trailing comma from XML path

1 Answers1