Post reply

Query from a list in an excel file

  • February 11, 2014 at 8:58 am

    #283551

    I have database tables for

    Stores

    StoreId, Name

    Products

    ProductId, Name

    Transactions

    TransactionId, StoreId, ProductId

    I was just given an excel file with a list of 300 Stores.

    I need to find out if these stores are selling our products and if they are , how many products they are selling.

    One way of doing this , that I can think of right now is individually querying the Transactions table for each of the store in the excel sheet and then copy the results output back to the excel sheet.

    Is there a way I can write a query against all the Store names from the excel file ? I need to get this done in the next few hours. Please let me know if there are easier approaches to this

    Thanks !!

  • February 11, 2014 at 9:14 am

    #1687962

    You could create a Linked Server for one but I would import the Data into SQL Server Tables.

    You can use the Import Wizard which is basically SSIS.

    You may need to tweak it so I recommend saving the SSIS Package.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 11, 2014 at 9:18 am

    #1687967

    I agree with Welsh Corgi; this sounds like a one time comparison, so there's no real need to add a linked server.

    I would use the Import wizard, just has Welsh suggested, bring the table into teh database, adn join it to your transactions table.

    then you can grab hte data and paste it into an excel and report back really quickly.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 11, 2014 at 12:53 pm

    #1688081

    Great !! Thank you !

  • February 12, 2014 at 9:37 am

    #1688465

    Bah; no need for a wizard for a few hundred rows on a one-off import when a simple EXCEL formula creates your INSERT statements for you.

    =CONCATENATE("INSERT INTO #YourTable VALUES ('",A1,"')")

    Grab the handle on the bottom right and drag the formula down.

  • February 12, 2014 at 10:33 am

    #1688500

    Nadrek (2/12/2014)

    Bah; no need for a wizard for a few hundred rows on a one-off import when a simple EXCEL formula creates your INSERT statements for you.

    =CONCATENATE("INSERT INTO #YourTable VALUES ('",A1,"')")

    Grab the handle on the bottom right and drag the formula down.

    IMHO, there is nothing wrong with using or at least being familiar with the Import/Export Wizard.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 12, 2014 at 11:01 am

    #1688513

    Welsh Corgi (2/12/2014)

    IMHO, there is nothing wrong with using or at least being familiar with the Import/Export Wizard.

    Upgrading hundreds of import/export packages to a new version of SQL Server?

  • February 12, 2014 at 11:05 am

    #1688514

    Nadrek (2/12/2014)

    Welsh Corgi (2/12/2014)

    IMHO, there is nothing wrong with using or at least being familiar with the Import/Export Wizard.

    Upgrading hundreds of import/export packages to a new version of SQL Server?

    What? :hehe:

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 12, 2014 at 12:03 pm

    #1688532

    Welsh Corgi (2/12/2014)

    Nadrek (2/12/2014)

    Welsh Corgi (2/12/2014)

    IMHO, there is nothing wrong with using or at least being familiar with the Import/Export Wizard.

    Upgrading hundreds of import/export packages to a new version of SQL Server?

    What? :hehe:

    Once you start down the path to SSIS, forever will it dominate your destiny.

    The import/export wizard is quicker, easier, more seductive; but it is not more powerful than the SQL.

  • February 12, 2014 at 12:27 pm

    #1688540

    Nadrek (2/12/2014)

    Welsh Corgi (2/12/2014)

    Nadrek (2/12/2014)

    Welsh Corgi (2/12/2014)

    IMHO, there is nothing wrong with using or at least being familiar with the Import/Export Wizard.

    Upgrading hundreds of import/export packages to a new version of SQL Server?

    What? :hehe:

    Once you start down the path to SSIS, forever will it dominate your destiny.

    The import/export wizard is quicker, easier, more seductive; but it is not more powerful than the SQL.

    I do not agree with your opinion.

    It is an option. If you do not like it and choose to not use it then that is your prerogative.

    Perhaps Microsoft should remove the Import/Export Wizard.

    Maybe they should ban SSIS as well.:crazy:

    I never made a statement about what is more powerful.

    It is about knowing what tool is the best option based on what you are doing.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 10 posts - 1 through 9 (of 9 total)

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