Exporting Data from SQL Server-->Need Help

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Exporting Data from SQL Server-->Need Help

Author

Topic

joy
Starting Member

5 Posts

Posted - 2005-05-30 : 07:09:37

I need to perform the folowing things in SQL server.
1)Export all data from a table in a ";" separated text file.
2)Need to run a simple sql script(from Cmd line) which will ask user where to store the data file and dump all those data file in that folder.(Something like Accept in Oracle)
3)The data file will have a header row in the first row
4)User will run a single script and it will generate N number of files for N number of tables.
I'm stuck with this thing. Any help will be appriciated.
Thanks in advance

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-30 : 23:23:21

1. explore bcp (check BOL)
2. create an exe, wherein the exe will accept the path and tablename and calls an sp that will perform the bcp out
3. handle this in number 2
4. this is the sp in number 2

if you're stuck, post what you have so far...

quote:
Originally posted by joy

I need to perform the folowing things in SQL server.
1)Export all data from a table in a ";" separated text file.
2)Need to run a simple sql script(from Cmd line) which will ask user where to store the data file and dump all those data file in that folder.(Something like Accept in Oracle)
3)The data file will have a header row in the first row
4)User will run a single script and it will generate N number of files for N number of tables.
I'm stuck with this thing. Any help will be appriciated.
Thanks in advance

--------------------
keeping it simple...

joy
Starting Member

5 Posts

Posted - 2005-05-31 : 00:59:08

The folowing is script that I used. Here I have assigned the @setdir with directory name.Which i need to take as user input.
My other problem is that i can't run any SP or exe to extract this data.It should be simple SQL. Following code will be in a file say a.sql. And user will run this script file with osql.

DECLARE @FileName varchar(50),
@bcpCommand varchar(2000),
@setdir varchar(1000),
@moduledir varchar(1000),
@command varchar(1000)
SET @setdir = 'c:\abc\PT'
SET @command = 'mkdir '+ @setdir
EXEC master..xp_cmdshell @command
-----------------------------------------------------------------------
--*********************************************************************
--Module specific --*********************************************************************

SET @FileName = @setdir+'PS_MC_DEFN.txt'

SET @bcpCommand = 'bcp "SELECT LTRIM(RTRIM(ARCHIVE_DT)),LTRIM(RTRIM(ARCHIVE_ID)),LTRIM(RTRIM(DTTM_STAMP)),LTRIM(RTRIM(MC_DEFN_ID)),LTRIM(RTRIM(MC_NULL_DT)),LTRIM(RTRIM(MC_NULL_DTTM_STAMP)),LTRIM(RTRIM(MC_NULL_TIME)),LTRIM(RTRIM(MC_TEMPLATE_ID)),LTRIM(RTRIM(MC_TYPE_ID)),LTRIM(RTRIM(OPRID)),LTRIM(RTRIM(PS_OWNER))FROM PSFIN88..PS_MC_DEFN" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -c -t ; -T'

EXEC master..xp_cmdshell @bcpCommand

------------------------------------------------------------------------

SET @FileName = @setdir + 'PS_OPR_DEF_TBL_FS.txt'

SET @bcpCommand = 'bcp "SELECT LTRIM(RTRIM(ALT_CHAR_ENABLED)),LTRIM(RTRIM(AS_OF_DATE)),LTRIM(RTRIM(BUSINESS_UNIT)),LTRIM(RTRIM(DR_CR_VISIBLE_FLG)),LTRIM(RTRIM(LC_CNTRY)),LTRIM(RTRIM(LEDGER)),LTRIM(RTRIM(LEDGER_GROUP)),LTRIM(RTRIM(NAME1)),LTRIM(RTRIM(OPRID)),LTRIM(RTRIM(SETID)),LTRIM(RTRIM(SOURCE))FROM PSFIN88..PS_OPR_DEF_TBL_FS" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -c -t ; -T'

EXEC master..xp_cmdshell @bcpCommand

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-31 : 01:11:17

then, read isql in BOL

quote:

isql Utility
The isql utility allows you to enter Transact-SQL statements, system procedures, and script files; and uses DB-Library to communicate with Microsoft® SQL Server™ 2000.

quote:
Originally posted by joy

My other problem is that i can't run any SP or exe to extract this data.It should be simple SQL.

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources