BCP QUERYOUT HELP - 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 2005 Forums

Transact-SQL (2005)

BCP QUERYOUT HELP

Author

Topic

toddmcdaniel
Starting Member

5 Posts

Posted - 2009-05-22 : 13:14:19

-- I need help creating an acceptable select statement for BCP
-- that will allow me to produce a Comma-delimited, quoted-identifier
-- text file WITHOUT using a format file. I've been told it can be
-- done, but I am pulling my hair out over here!!! See code below...
-- YOU SHOULD BE ABLE TO COPY AND PASTE THIS ENTIRE MESSAGE TO A
-- QUERY WINDOW FOR RUNNING/TESTING.

USE tempdb
go
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'TestTable')
DROP TABLE TestTable
go

CREATE TABLE TestTable (
TestCol1 VARCHAR(20)
,TestCol2 VARCHAR(20))
go

INSERT TestTable (TestCol1, TestCol2) VALUES ('123', 'abc,def')
INSERT TestTable (TestCol1, TestCol2) VALUES ('654' , 'zyx,wvu')

-- the following select statement retrieves the data the way I want
-- (quoted-identifier and comma-delimited)
select '"' + TestCol1 + '", "' + TestCol2 + '"' from TestTable

-- Now I want to use this same select statement in a BCP QUERYOUT
-- command, but it seems BCP gets confused.
--*****************************************************************
--* IS THERE A WAY TO DO THIS - CREATE A TEXT FILE THAT IS COMMA- *
--* SEPARATED and using QUOTED-IDENTFIER USING THE BCP COMMAND *
--* BUT WITHOUT USING A FORMAT FILE ?????? *
--*****************************************************************
declare
@sql varchar(400),
@cmd varchar(400)

set @sql = 'select '
set @sql = @sql + '''"'' + TestCol1 + ''", '
set @sql = @sql + '"'' + TestCol2 + ''"'''
set @sql = @sql + ' from TestTable'
print @sql
-- this execute command of the select statement works just fine!
exec (@sql)

-- But the following BCP command does NOT work!!!!!
set @cmd = 'bcp "' + @sql + '" queryout "C:\temp\test.csv" -c -t, -S'
set @cmd = @cmd + convert(varchar, serverproperty('servername')) + ' -T'
print @cmd
Exec Master..xp_Cmdshell @cmd

-- ANY SUGGESTIONS??? PLEASE HELP! (todd.mcdaniel@cbrands.com)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 13:45:39

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/bcp-export-data-to-text-file.aspx

toddmcdaniel
Starting Member

5 Posts

Posted - 2009-05-22 : 14:05:28

This article is not helpful at all. All this does is export a table to a text file, which is easy to do. For instance, replace the "@sql" string in the BCP call with "select * from TestTable" and I'd get the same results from the link you just specified.

My problem is making the text file Comma-delimited AND with Quoted-identifiers. Read through my original message and you will see what I am trying to do. Thanks.

toddmcdaniel
Starting Member

5 Posts

Posted - 2009-05-22 : 17:06:02

WHAT? THERE ARE NO SQL GURUS OUT THERE WHO CAN FIGURE THIS OUT?!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources