script to get export specifice tables | delimited

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

General SQL Server Forums

New to SQL Server Programming

script to get export specifice tables | delimited

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 04:47:20

Hi All,

Can you help me in writing a script which generate an export
for only 6 tables in a database, to C:\.. (any folder) data
with pipe delimited.

I am using SQL Server 2005 DB table names say A B C D & E ( for e.g.)
database name "ProjectN"

I would request the client to run this script, to get the output of tables in the txt file as | delimited.

Regards,
aakcse

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 05:58:07

I am trying out the below code,

declare @sql varchar(8000)select @sql = 'bcp ProjectN..a out                c:\bcp\tab_a.txt -c –t^ -T -S' + @@servernameexec master..xp_cmdshell @sqlselect @sql = 'bcp ProjectN..b out                c:\bcp\tab_b.txt -c –t^ -T -S' + @@servernameexec master..xp_cmdshell @sql

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 06:18:08

This worked.... thanks all..

/*

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

*/

DECLARE @OutputFile NVARCHAR(4000)
,@FilePath NVARCHAR(1000)
,@bcpCommand NVARCHAR(4000)

SET @bcpCommand = 'bcp "SELECT * FROM [databaseName].dbo.table_name" queryout "'

SET @FilePath = 'C:\'
SET @OutputFile = 'file_name.txt'
SET @bcpCommand = @bcpCommand + @FilePath+ @OutputFile + '" -c -T -t"|"'

EXEC master..xp_cmdshell @bcpCommand

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 06:25:08

will the above works on 2000, I mean "EXEC master..xp_cmdshell @bcpCommand" commands works fine with sqlserver2000.

Regards,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-16 : 07:00:33

xp_cmdshell is also known in sql server 2000:
http://msdn.microsoft.com/en-us/library/aa260689(SQL.80).aspx

Give it a try...

No, you're never too old to Yak'n'Roll if you're too young to die.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 07:11:12

Thanks Webfred,

How can I include the column (header info) names as well in the exported file, right now it only gives me the data pipe separated,

also if I want to add the text qualifier(this one not compulsory however)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-16 : 07:19:42

run a dos window and perform: bcp /?
You will see a short description on parameters for bcp.
There you can see how to give text qualifier.
Including column header I don't know now...

No, you're never too old to Yak'n'Roll if you're too young to die.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-06-16 : 07:25:57

hmm may I have an option to include this in code, as this I will be giving to client to run on their machine, so they may not be aware of all this.

I have included this in sp with 3 parameter 1) db name 2) table name 3) path.

Regards,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources