Utilising the export data wizard for top 100 rows for all tables in a database

Hi EE,

I have the following query:
select 'Select Top 100 * From ' + SCHEMA_NAME(schema_id) + '.' + name 
from sys.objects 
where type = 'U'

Open in new window


Running SQL server 2014.

It possible to use the SQL server data export wizard to insert the results all the queries generated from the query above into an excel file

Any assistance is welcome.

Thank you.
ZackGeneral IT Goto GuyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
I'm not clear on your direction to get only 100 records from each table..
Can you clarify that better as if you have Foreign keys, those will have issues while inserting back to the destination database tables..

If you want just sample records for all these tables, then you can use some third party tools like the one below:
https://www.ssmstoolspack.com/Features?f=9

We can try few options as listed below:
1. In SSMS generate scripts wizard, it is not feasible to limit just 100 records however, you can export all records and then just extract only few set of records from the output files and save it to new files.
2. using SQLCMD, we need to export the table names and then create a single SQLCMD to extract 100 records for all those tables one by one and then save those to targeted output files.
ZackGeneral IT Goto GuyAuthor Commented:
Hi Raja,

Any can link any resources on how to use SQLCMD in the manner you described?

Thank you.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Sample below, but we need to construct all these commands for all tables manually or via another dynamic sql scripts..

D:\test>sqlcmd -S localhost -d AdventureWorks2012 -E -Q “SELECT top 100 * FROM HumanResources.Employee” -o “CSVData.csv” -W -w 1024 -s”,”
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Mark WillsTopic AdvisorCommented:
Is it a one time thing ?

you can elect to have your query window results output to file.... CTRL+SHIFT+F
then run your query and you will be prompted for a file name

It will save as a fixed length / fixed format file, but your query is only outputting the single column "select top 100  * from <schema>.<tablename>"  so that shouldnt be a problem....

Oh and to get back to Grid View in SSMS CTRL+D

(or use the icons above the query pane, or click on Query menu bar, then scroll down to Results To then choose)
Mark WillsTopic AdvisorCommented:
OOps, might have misunderstood....

>> ... to insert the results all the queries generated from the query above into an excel file

Are you saying you want the results output for each one of those individual lines (ie output results for executing each of the "select top 100 * from <schema>.<table>" queries)

That is quite a different and onerous problem....

Oh, and I think you should also be adding in the DBname before the schema....
ZackGeneral IT Goto GuyAuthor Commented:
HI Guys,

Cheers for your assistance I will present you my solution to this problem:

- Write the query so it shows includes that table name in the results e.g.   SELECT  'DB_Table USERTABLE4' As title Select Top 10 * From dbo.USERTABLE4. Excel Concatenate functions can be used to replicate for tables in the DB.

- Use 'query options' to change result format: Text format -> Comma Delimited.

- Export query to file .rpt and relabel to .csv.

Thank you

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZackGeneral IT Goto GuyAuthor Commented:
Thank you for the assistance guys. Mark, I would assign points to but the new system doesn't seem to let me :-(
Mark WillsTopic AdvisorCommented:
Yeah, it is a bit tricky now days - I have heard there is a 'Classic View' (maybe ellipsis button at the question body bottom)

Otherwise : http://support.experts-exchange.com/customer/portal/articles/2527982-how-do-i-close-my-question-
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Mark, I would suggest to split the points as requested by Zack if you still can..
Mark WillsTopic AdvisorCommented:
Thanks Raja,

Yes, I can still do some things :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.