Excel table referencing with INSERT INTO SELECT with connection to SQL Server

  • I have the following code, but am struggling with the syntax for the final part which needs to export the entire "tblTempFD" table data to the SQL Server "ES_RTR_Staging" Table.


    The connection string is correct and the connection opens, other code to set up the table prior to importing the Excel table all works, just the final part is an issue.



    Any help or pointers greatly appreciated.


    Also asked here: http://stackoverflow.com/quest…-connection-to-sql-server

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Excel table referencing with INSERT INTO SELECT with connection to SQL Server


    You need to provide the connection information for the Excel workbook using OPENROWSET or OPENDATASOURCE. There are some examples here: https://support.microsoft.com/…-from-excel-to-sql-server
    You'll want to to use the ACE.OLEDB.12 provider rather than JET.4 assuming you're not still on Office 2003.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Excel table referencing with INSERT INTO SELECT with connection to SQL Server


    Thanks Rory, that link is just giving me a blank page.


    I think (hope) I have it sorted by using:

    Code
    sSQL = "INSERT INTO " & sTblID & _
               " SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].tblTempFD"


    I cannot access the SQL Server at the moment, but will update here if the above works.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Excel table referencing with INSERT INTO SELECT with connection to SQL Server


    That's odd - the link works fine for me. Here is the relevant bit:



    The syntax you have works with Jet/ACE but I am not sure if it works for SQLOLEDB.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Excel table referencing with INSERT INTO SELECT with connection to SQL Server


    Hey Rory. I am working on this with the "Monkey Man" KjBox and unlike him, I do have a direct connection to the destination SQL Server table. Your link worked perfectly for me and I read the article. It was very informative. I tried to apply it but I now getting a Run-time '424' error" Object Required error with this code:


    Code
    'Export by using OPENROWSET and SELECT query.
        sSQL = "INSERT INTO " & sTblID & " FROM " & _
        "OPENROWSET('Microsoft.Ace.OLEDB.12.0', " & _
        "'Excel 12.0; HDR=NO; Database=" & ThisWorkbook.FullName & "' ," & _
        "'SELECT * FROM [TempFcast$].tblTempFD')"
        Debug.Print sSQL
        cn.Execute sSQL, lngRecsAff, adExecuteNoRecords
        Debug.Print "Records affected: " & lngRecsAff


    I'm pretty positive it's the way that I'm referencing the source table. I've tried it a bunch of different ways including: [TempFcast]tblTempFD, [TempFcast]tblTempFD, and just tblTempFD. Same "Object Required" error for all!


    This is what the Debug.Print sSQL looks like (to ease the strain on your eyes):


    SQL
    INSERT INTO [dbo].[fact_Forecast_RAW] FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0; HDR=NO; Database=C:\Users\sxjcf\Desktop\Stoplight_0plus12_Jones_1.2.3d.xlsm' ,'SELECT * FROM [TempFcast$]tblTempFD')


    Any help would be greatly appreciated!!!

  • Re: Excel table referencing with INSERT INTO SELECT with connection to SQL Server


    You wouldn't get a 424 error with that. If you're using the original code to set up the connection, you should be using cnn.execute and not cn.execute. You should also have Option Explicit at the top of your code module. ;)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Excel table referencing with INSERT INTO SELECT with connection to SQL Server


    Yeah. I usually use Option Explicit and Dim all my variables. That "cn" snuck in from a code paste from that link you provided. : ) So, you were right, that 424 error is gone now, but the same problem with that tables reference exists. But now the error is one that I've never seen before: "Incorrect syntax near the keyword 'FROM'".


    [ATTACH=CONFIG]71246[/ATTACH]


    The sSQL string variable currently looks like this:


    SQL
    INSERT INTO [dbo].[fact_Forecast_RAW] FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0; HDR=NO; Database=C:\Users\sxjcf\Desktop\Stoplight_0plus12_Jones_1.2.3d.xlsm' ,'SELECT * FROM [TempFcast$]tblTempFD')
  • Re: Excel table referencing with INSERT INTO SELECT with connection to SQL Server


    Ah, I somehow overlooked the fact you're trying to access a table. That won't work. Tables and dynamic ranges aren't visible to most providers. You have to do the whole sheet or a fixed range.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Excel table referencing with INSERT INTO SELECT with connection to SQL Server


    The monkey man is awake now!


    Thanks Rory & Jerid for continuing with this while I was in the Land of Nod.


    I made a couple of alterations to your code, Jerid

    Code
    sSQL = "INSERT INTO " & sTblID & " FROM " & _
        "OPENROWSET('Microsoft.Ace.OLEDB.12.0', " & _
        "'Excel 12.0; HDR=YES; Data Source=" & ThisWorkbook.FullName & "' ," & _
        "'SELECT * FROM [TempFcast$]')"


    Since the "TempFcast" sheet contains just the single Table the entire sheet can be used as the data for exporting.
    "HDR" should be "YES", this property of OPENROWSET is not asking "Do you want to include a header row in the data imported?", but rather "Does the data have a header row?"


    The resulting SL Query is

    SQL
    INSERT INTO [dbo].[fact_Forecast_RAW] FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0; HDR=YES; Data Source=C:\Users\charl\Dropbox\Charles.Johnson\Jones, Peggy\2017\Development\Stoplight_0plus12_Jones_1.2.3d.xlsm' ,'SELECT * FROM [TempFcast$]')


    The sample code I posted earlier was just a mock-up to illustrate the problem area, it was hard coded for connection string and the table names. The actual code uses variables and is just one procedure of a few in a particular Module, all Modules are "Option Explicit".

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Excel table referencing with INSERT INTO SELECT with connection to SQL Server


    Guys... I'm still getting the same error (see image). Do we maybe need to pass the contents of the table into a recordset variable and then send it to SQL Server? And Charles, you have to get rid of Option Explicit or dim the "xx" variable (and probably others). : )


    [ATTACH=CONFIG]71270[/ATTACH]

  • Re: Excel table referencing with INSERT INTO SELECT with connection to SQL Server


    I can't see that attachment.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Excel table referencing with INSERT INTO SELECT with connection to SQL Server


    In case either of you are interested, this is the working solution:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!