Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query from a list in an excel file Expand / Collapse
Author
Message
Posted Tuesday, February 11, 2014 8:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 5:22 PM
Points: 58, Visits: 197
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 !!
Post #1540261
Posted Tuesday, February 11, 2014 9:14 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
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/

Post #1540272
Posted Tuesday, February 11, 2014 9:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1540277
Posted Tuesday, February 11, 2014 12:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 5:22 PM
Points: 58, Visits: 197
Great !! Thank you !
Post #1540408
Posted Wednesday, February 12, 2014 9:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:46 AM
Points: 845, Visits: 2,331
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.
Post #1540864
Posted Wednesday, February 12, 2014 10:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
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/

Post #1540902
Posted Wednesday, February 12, 2014 11:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:46 AM
Points: 845, Visits: 2,331
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?
Post #1540917
Posted Wednesday, February 12, 2014 11:05 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
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?


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/

Post #1540918
Posted Wednesday, February 12, 2014 12:03 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:46 AM
Points: 845, Visits: 2,331
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?


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.
Post #1540938
Posted Wednesday, February 12, 2014 12:27 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:03 AM
Points: 4,052, Visits: 4,192
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?


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.

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/

Post #1540947
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse