Post reply

Insert in Excel & Get in SQL table

  • September 18, 2008 at 11:49 am

    #126506

    Hello Friends,

    I have got a requirement in which I have an Excel Workbook (latest version) as my source and SQL Server table as my destination. The scenario is that I have similar column names in both - source and destination.

    So, now what I have to achieve is, if I insert some value in my excel sheet, then it should get updated in the SQL Server destination table. I have to avoid using SSIS for this.

    I am not even sure if there is anyway to do this. I am researching on it and I would appreciate your help if you can also let me know your ideas.

    Thank you,

    notes4we

  • September 19, 2008 at 3:46 am

    #874087

    Hi,

    You could set up your workbook as a linked server, or just use a distributed query

    INSERT INTO MySQLTable

    SELECT *

    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\MyWorkbook.xls', MyDatasheet$) xl

    HTH

    Kindest Regards,

    Frank Bazan

  • September 19, 2008 at 7:40 am

    #874206

    Hello Frank,

    Thank you for your reply.

    But, I did not understand properly.

    Could you explain me a bit in detail about where can I write this query or connect to a linked server?

    Thank you,

    notes4we

  • September 19, 2008 at 8:32 am

    #874266

    Hi,

    Setting up a linked server is a way in which you can execute queries against distributed databases.

    So if you have say an Oracle database that handles your call centre operational database, and you want to query a table in it and perhaps join the table to some data that you hold in SQL server HR database, you could do this quite easily.

    The downside is that the query is generally going to perform badly due to network bandwidth, the necessity to bring unrelated records to the local machine prior to logic being performed etc.

    A linked server and distributed query is essentially the same thing. The linked server is a defined within the master database metatables and the remote database tables can be referenced as though they were on the local server.

    In your management studio look under SERVER OBJECTS > LINKED SERVERS where you will find all the various providers that are supported.

    The distributed query does the same thing only you define the connection string within the query itself.

    Look up "Linked servers excel" in google and I'm sure you'll get a much better explanation.

    The query from my earlier post is T-SQL and would be executed anywhere that T-SQL can be executed. (After you correct the connection string for your own xl workbook).

    Hope this helps

    Kindest Regards,

    Frank Bazan

  • September 19, 2008 at 2:01 pm

    #874452

    Hello Frank,

    Thank you for your detailed explanation.

    I am planning to use VBA for achieving this.

    I would like to use a method where I do not have to move to another application, but I can stay in Excel and achieve my task.

    So, now I am researching on how can I use VBA to achieve this.

    Thank you,

    notes4we

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply