Incorrect syntax near ']'.?

I have done some debugging on my sql and I cant figure out the maddening error I am getting I have narrowed it down to a couple of lines which I cant see what the problem is, please someone give me some assistance.

I get this error

  • Select property from max in cross apply SQL
  • PDO does not throw exception with multiple queries
  • Speed up update of 185k rows in SQL Server 2008?
  • SQL Server: IF EXISTS ; ELSE
  • Could not load file or assembly 'VSLangProj80'
  • How to set SQL Server 2005 Job CmdExec Timeout
  • I am here2
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near
    ‘]’.
    I am here3

     Print 'I am here2'
                            SET IDENTITY_INSERT c365online_script1.dbo.tCompany ON
                            declare @cols2 varchar(max)
                            select @cols2 = (Select Stuff((Select '],[' + C.COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS As C Where C.TABLE_SCHEMA = T.TABLE_SCHEMA And C.TABLE_NAME = T.TABLE_NAME Order By C.ORDINAL_POSITION For Xml Path('')), 1, 2, '') As Columns From INFORMATION_SCHEMA.TABLES As T WHERE T.TABLE_NAME = @tablename)
                            EXEC('INSERT INTO [' + @Destination_Database_Name + '].[dbo].[' + @tablename + '] (' + @cols2 + ']' + ') SELECT ' + @cols2 + ']' + ' FROM [' + @Source_Database_Name + '].[dbo].[' + @tablename + ']');
                             Print 'I am here3'
    

    3 Solutions collect form web for “Incorrect syntax near ']'.?”

    You’re missing an opening square bracket here:

    ... ') SELECT ' + @cols2 + ']' + ' FROM ...
    

    Furthermore I would recommend you switch to using the QuoteName() function instead:

    ... ') SELECT ' + QuoteName(@cols2) + ' FROM...
    

    You are missing about 3 opening brackets as far as i can tell at

    select @cols2 = (Select Stuff((Select '],[ 
    

    and

     (' + @cols2 + ']' // -> either [' + @cols2 + ']' or (' + @cols2 + ')'
    

    and

     + ') SELECT ' + @cols2 + ']'  -- -> either ') SELECT [' + @cols2 + ']'  or ') SELECT (' + @cols2 + ')' 
    
    declare @cols2 nvarchar(max)
    select @cols2 = (Select Stuff((Select ',[' + C.COLUMN_NAME  + ']'
                    From INFORMATION_SCHEMA.COLUMNS As C 
                    Where C.TABLE_SCHEMA = T.TABLE_SCHEMA 
                    And C.TABLE_NAME = T.TABLE_NAME 
                    Order By C.ORDINAL_POSITION For Xml Path('')), 1, 1, '') As Columns 
                    From INFORMATION_SCHEMA.TABLES As T 
                    WHERE T.TABLE_NAME = @tablename)
    

    Edit

    DECLARE @Destination_Database_Name NVARCHAR(128) = 'Trg_DataBaseName'
    DECLARE @tablename NVARCHAR(128) = 'AgressoIFCGLItems'
    DECLARE   @Source_Database_Name NVARCHAR(128) = 'Configsandpit'
    
    Print 'I am here2'
    
    --SET IDENTITY_INSERT c365online_script1.dbo.tCompany ON
    declare @cols2 varchar(max)
    select @cols2 = (Select Stuff((Select  ',' + QUOTENAME(C.COLUMN_NAME)  [text()]
                    From INFORMATION_SCHEMA.COLUMNS As C 
                    Where C.TABLE_SCHEMA = T.TABLE_SCHEMA 
                    And C.TABLE_NAME = T.TABLE_NAME 
                    Order By C.ORDINAL_POSITION For Xml Path('')), 1, 1, '') As Columns 
                    From INFORMATION_SCHEMA.TABLES As T 
                    WHERE T.TABLE_NAME = @tablename)
    DECLARE @sql NVARCHAR(MAX) 
    SET @sql = 'INSERT INTO ' + QUOTENAME(@Destination_Database_Name) + '.[dbo].' + QUOTENAME(@tablename) + ' (' + @cols2 +   ') SELECT ' + @cols2  + ' FROM ' + QUOTENAME(@Source_Database_Name) + '.[dbo].' + QUOTENAME(@tablename) + '';
    
    EXECUTE sp_Executesql @sql 
    

    Your need to change you @Sql Statement after you have used QOUTENAME function as it adds the square backets for you, you do not need to concatinate square barckets in your Sql statement.

    Result

    INSERT INTO [Trg_DataBaseName].[dbo].[TableName] ([Col1],[Col2],[Col3],[Col4],[Col5]) SELECT [Col1],[Col2],[Col3],[Col4],[Col5] FROM [Src_Database].[dbo].[TableName]
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.