Export data to CSV text file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Export data to CSV text file

Author

Topic

robert693
Starting Member

42 Posts

Posted - 2010-09-27 : 10:17:14

Hello,

I would like to export data from a table in our database to a comma delimited file that will be imported later into Outlook. The data is from a table called Schedule that has a Date field and a Type field. The export would include all data from a given date range and with a Type "MTG." I am unfamiliar with the syntax of a BCP command and if sombody could help me I would greatly appreciate it!

Thank you!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-27 : 10:31:16

Google is your friend. There are lots of examples out there:

Here's a quick one from me


bcp "SELECT * FROM ReFlex_Main.dbo.person" QUERYOUT c:\text.csv -S127.0.0.1 -Usa -Pmuppet -c

This SELECTS everything from the 'person' table in the ReFlex_Main database, which is on my local machine (-S127.0.0.1) -c indicates the output should be character type.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

robert693
Starting Member

42 Posts

Posted - 2010-09-27 : 10:35:30

Thank you,

I have been looking at Google as well. I will try your example.

robert693
Starting Member

42 Posts

Posted - 2010-09-27 : 11:07:05

I have put this into a query:
bcp
SELECT "Config"."ID1_Label", "Config"."Inst_ID", "Schedule"."Sch_Id", "Schedule"."App_DtTm", "IDENT"."Version", "Cpt"."Template", "IDENT"."IDENT_ID", "Schedule"."Version", "Schedule"."Pat_ID1", "Schedule"."Activity", "Staff"."Initials", (ISNULL(dbo.fn_GetActiveDeptName(),'')), (ISNULL(dbo.fn_getactivecopyrightnotice(),''))
FROM (((("MOSAIQ"."dbo"."Schedule" "Schedule" LEFT OUTER JOIN "MOSAIQ"."dbo"."CPT" "Cpt" ON "Schedule"."Activity"="Cpt"."Hsp_Code") LEFT OUTER JOIN "MOSAIQ"."dbo"."Patient" "Patient" ON "Schedule"."Pat_ID1"="Patient"."Pat_ID1") LEFT OUTER JOIN "MOSAIQ"."dbo"."Config" "Config" ON "Schedule"."Inst_ID"="Config"."Inst_ID") LEFT OUTER JOIN "MOSAIQ"."dbo"."Staff" "Staff" ON "Schedule"."Staff_ID"="Staff"."Staff_ID") LEFT OUTER JOIN "MOSAIQ"."dbo"."Ident" "IDENT" ON "Patient"."Pat_ID1"="IDENT"."Pat_Id1"
WHERE "Schedule"."Activity"='MTG' AND "Schedule"."Version"=0 AND "Cpt"."Template"=0 AND ("IDENT"."IDENT_ID" IS NULL OR "IDENT"."IDENT_ID"=0 OR "IDENT"."Version"=0) AND ("Schedule"."App_DtTm">={ts '2010-09-27 00:00:00'} AND "Schedule"."App_DtTm"<{ts '2010-09-28 00:00:00'})
ORDER BY "Schedule"."App_DtTm" QUERYOUT c:\ScheduleText.csv -mydatabase -pw -c

I get an error message that says Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'QUERYOUT'. Do I have to put something else in near Queryout?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-27 : 11:08:46

all the double quotes are meaning you can't parse it

replace the " with square parenthesis. This is more syntactically correct in sqlserver anyway.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-27 : 11:10:13

better yet -- put this code into a proc or a view and then select from the view / execute the proc rather than doing it inline in the BCP

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources