INSERT INTO OPENROWSET Syntax with Dynamic T-SQL

21,656

Try this:

    SET @sql = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database='+@FullFileName+''',''SELECT * FROM [Sheet1$]'') SELECT * FROM dbo.CMLTrial WHERE Officer LIKE '''+@OfficerName+''''

I always do a PRINT @sql when working with dynamic scripts to see how the actual query will look like. It's easier to troubleshoot and saves me plenty of grief later on.

Share:
21,656

Related videos on Youtube

BigDevJames
Author by

BigDevJames

I'm a software engineer and cloud architect. I enjoy listening to problems and helping people find ways to solve them. Sometimes that is with technology, sometimes that is with establishing processes. I try to be very brand-name agnostic even though I have a stronger background with Microsoft, Azure, and .NET. In the past, I've also done a lot of data analysis and report/dashboard building. I don't want to be tied into just one type of problem that I am good at solving. I also have my sights set on being a technical leader. There wasn't a clear path for me into technology when I left the Navy. I want to help clear the way for others to grow and flourish.

Updated on January 08, 2020

Comments

  • BigDevJames
    BigDevJames almost 4 years

    I feel like I have read every single page online about how to dynamically create an Excel output from a T-SQL script. Here is what I have:

    Use Master
    GO
    
    EXEC master.dbo.sp_configure 'show advanced options', 1
    RECONFIGURE WITH OVERRIDE
    GO
    
    EXEC master.dbo.sp_configure 'xp_cmdshell', 1
    RECONFIGURE WITH OVERRIDE
    GO
    
    EXEC sp_configure 'ad hoc distributed queries', 1
    RECONFIGURE
    GO
    
    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
    GO
    
    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
    GO
    
    USE CommercialLending
    GO
    DECLARE @LoopCounter TINYINT = 1
    DECLARE @LoopMaxCount TINYINT = (SELECT COUNT(DISTINCT OFFICER)
                                    FROM CommercialLending.dbo.CMLTrial)
    WHILE (1=1)
    BEGIN
    DECLARE @OfficerName VARCHAR(4000) = (
                                        SELECT OFFICER 
                                        FROM (SELECT DISTINCT OFFICER, ROW_NUMBER() OVER (ORDER BY OFFICER) AS rownumber 
                                                FROM CommercialLending.dbo.CMLTrial GROUP BY Officer) AS OFFICER 
                                        WHERE rownumber = @LoopCounter)
    DECLARE @FileName varchar(400) = @OfficerName+ '.xlsx'
    DECLARE @FullFileName varchar(400) = 'O:\MIS\Python\Programs\CommercialLending\'+@FileName
    DECLARE @CopyFile varchar(800) = 'copy O:\MIS\Python\Programs\CommercialLending\Template.xlsx copy O:\MIS\Python\Programs\CommercialLending\' + @FileName
    EXEC xp_cmdshell @CopyFile
    DECLARE @sql nvarchar(4000)
    SET @sql = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',Excel 12.0;Database='+@FullFileName+';'',''SELECT * FROM [Sheet1$])'' SELECT * FROM dbo.CMLTrial WHERE Officer='''+@OfficerName+''
    EXEC (@sql)
    SET @LoopCounter = @LoopCounter+1
    IF (@LoopCounter > @LoopMaxCount)
        BREAK;
    END
    
    Use Master
    GO
    
    EXEC master.dbo.sp_configure 'xp_cmdshell', 0
    RECONFIGURE WITH OVERRIDE
    GO
    
    EXEC sp_configure 'ad hoc distributed queries', 0
    RECONFIGURE
    GO
    
    EXEC master.dbo.sp_configure 'show advanced options', 0
    RECONFIGURE WITH OVERRIDE
    GO
    
    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0
    GO
    
    EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0
    GO
    

    I know that my problem is in the line that assigns the INSERT INTO OPENROWSET string to the @sql variable:

    SET @sql = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',Excel 12.0;Database='+@FullFileName+';'',''SELECT * FROM [Sheet1$])'' SELECT * FROM dbo.CMLTrial WHERE Officer='''+@OfficerName+''
    

    For the life of me I cannot figure out where my syntax error is. Can someone help me figure it out, and also, if possible, provide a guide for figuring out the standard syntax for using the INSERT INTO OPENROWSET as a string in a variable?

Related