?
Solved

sql server print out table to text file

Posted on 2009-12-16
7
Medium Priority
?
602 Views
Last Modified: 2013-11-30
in sql server 2005
want to print out 3 tables to text file

could i print out column names
0
Comment
Question by:rgb192
  • 3
  • 2
  • 2
7 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 26067734
Open query analyzer. Go to Query and select Result to file... during execution, it will prompt a file name....
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26067760
or use sqlcmd utility

http://msdn.microsoft.com/en-us/library/ms162773.aspx

sqlcmd ....... > output.txt

sqlcmd -Q "select * from table1 order by 1; select * from table2 order by 2,3; select * from table3" ... > output.txt

if above does not work, create a batch file, put above line to that file save as SQL2File.bat
run as

SQL2File.bat > output.txt

on command line...
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 26067761
Make sure in the Options you have checked the box to include column names.
SQL2005-Options-OutputColumnName.png
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:rgb192
ID: 26067784
>>Make sure in the Options you have checked the box to include column names.

how to print 3 tables

would i use 3 select statements and run them at once
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26068071
select * from table1;
select * from table2;
select * from table3;

and run it
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26068091
Yes you can do all 3, if you want them all to the same flat file, set your output to File as shown.
Produced the following output.
PersonKey   Animal
----------- --------------------
1           Cat
1           Dog
1           Snake
2           Cat
2           Snake
3           Cat
3           Dog
3           Snake
3           Bird
4           Bird
5           Dog
5           Bird
6           Snake
7           Cat
8           Dog

(15 row(s) affected)

id          studentid   yearid      grade       increase    decrease
----------- ----------- ----------- ----------- ----------- -----------
1           1           1           2127        0           0
2           1           2           2152        25          0
3           1           3           2652        500         0
4           1           4           2652        0           0
5           2           1           1310        0           0
6           2           2           1300        0           -10
7           2           3           1300        0           0
8           2           4           1300        0           0
9           3           1           314         0           0
10          3           2           314         0           0
11          3           3           314         0           0
12          3           4           314         0           0

(12 row(s) affected)

JobNumber   ElectricalExperience
----------- --------------------------------------------------
1000        Journeyman
1000        Journeyman
1000        Apprentice
1002        Journeyman
1002        Apprentice
1002        Apprentice

(6 row(s) affected)

Open in new window

SQL-Output-to-File.png
0
 
LVL 1

Author Closing Comment

by:rgb192
ID: 31667079
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question