Scripting - BCP out certain columns in table?

Question

Scripting - BCP out certain columns in table?

chrisph

Hall of Fame

Points: 3701
More actions
September 25, 2012 at 8:37 am

#268241

Hello, I was wondering if anyone had a script to script out particular columns from a table using BCP within SSMS to a CSV file. I see scripts for scripting out full tables but not specific columns.
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

well, building the command is just a join of sys.tables and sys.columns...so the question is how do you know which "certain columns" are to be exported?

if you know the names, you don't need to script them;

if it was something like "everything that is not varchar(max) or text data types, that would be easy;

with the code below, you could adapt it by adding the bcp query parameters, limiting the columns, etc.

SELECT DISTINCT

'SELECT ' + sq.Columns + ' FROM ' + t.name As cmd

FROM sys.tables t

JOIN (

SELECT OBJECT_ID,

Columns = STUFF((SELECT ',' + name

FROM sys.columns sc

WHERE sc.object_id = s.object_id

FOR XML PATH('')),1,1,'')

FROM sys.columns s

) sq ON t.object_id = sq.object_id

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!

chrisph Hall of Fame Points: 3701 More actions · Answer 2

I know the specific columns I am looking more for the syntax of the BCP command within SSMS. I'm a noob 🙂

Lowell SSC Guru Points: 323482 More actions · Answer 3

chrisph (9/25/2012)
I know the specific columns I am looking more for the syntax of the BCP command within SSMS. I'm a noob 🙂

well, bcp is actually a command line application.

to call it from SSMS, you need xp_cmdshell.

here's a basic syntax example of a query;

you might want to read up on the flags being used, since it's a command line.

you need to use three part names for the objects: database.schema.tablename like SandBox.sys.columns

-t"|" is my field terminator

-c is the character type

-T is trusted connection.

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT object_name(object_id) As TbLName,name as ColName FROM SandBox.sys.columns ORDER BY object_name(object_id), column_id" queryout C:\Data\Objects.txt -t"|" -c -T '

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!

chrisph Hall of Fame Points: 3701 More actions · Answer 4

I've tried several different ways to run the script example you showed and I constantly get a syntax error of "invalid object name" in regards to the database. schema.table. Any thoughts?

For example:

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM DB.DBO.Table" queryout C:\Data\Objects.txt -c -T'

Lowell SSC Guru Points: 323482 More actions · Answer 5

chrisph (9/25/2012)
I've tried several different ways to run the script example you showed and I constantly get a syntax error of "invalid object name" in regards to the database. schema.table. Any thoughts?
For example:
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM DB.DBO.Table" queryout C:\Data\Objects.txt -c -T'

well, the table/view used in the query object truly has to exist. did you obscure your command to make it generic? the details are important!

do you really have a database named [DB]?

do you really have a table inside that database named

?'

show us the exact command you ran.

the example i posted works perfectly if you have a database named Sandbox. you could change it to master to prove it works, since everyone ahs a master database.

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!

chrisph Hall of Fame Points: 3701 More actions · Answer 6

I changed the names for the example but I tried the command with exactly the same database and table name that reside on the server. The user I am under has sysadmin privileges. Tried it with other tables as well...

Lowell SSC Guru Points: 323482 More actions · Answer 7

chrisph (9/25/2012)
I changed the names for the example but I tried the command with exactly the same database and table name that reside on the server. The user I am under has sysadmin privileges. Tried it with other tables as well...

ok then check two things.

1 run the command you are tesitng in the master database.

SELECT * FROM DB.DBO.Table

does it returnd ata or an error?

second, the command you are using will connect to theh DEFAULT INSTANCE on the server! not the named instance you are thinking about.

that means if you THINK the server is MyMachine\SQL2008, your command is wrong!

it connects to MyMachine ' default, and not a named instance.

go back to the flags for BCP and review them!, and specify the instance name.

(need to add -S MyMachine\SQL2008 to the command)

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!

chrisph Hall of Fame Points: 3701 More actions · Answer 8

Yes that was the issue, I did not specify the instance.

Thank you Lowell!