sql-serversql-server-2016insert-intoidentity-insert

SQL Server remove trailing comma from XML path


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

Solution

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