SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query from a list in an excel file


Query from a list in an excel file

Author
Message
SqlServerNinja
SqlServerNinja
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 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 !!
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26150 Visits: 4895
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/
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69840 Visits: 40918
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!
SqlServerNinja
SqlServerNinja
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 197
Great !! Thank you !
Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4422 Visits: 2741
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.
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26150 Visits: 4895
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/
Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4422 Visits: 2741
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?
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26150 Visits: 4895
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/
Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4422 Visits: 2741
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.
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26150 Visits: 4895
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search