Batch Copy Process - on Sql Server & Oracle

Posted on 2013-11-11
Last Modified: 2016-02-11
I have two questions today. I hope someone can help.

I'm looking for some help on understanding what some of these arguments mean below.
i am using BCP to batch copy data from a sql server db into txt files and then from those txt files to another sql server db.

There are some commands being used on the script and i'm trying to understand them.
Can anyone please tell me what they might stand for?
Here is a snipit, the commands are at the end
-U sa

I believe the "-u sa" is username sa
I looked up the -w and i'm not sure if its logging the activity?
I believe the T argument is used in place of -p as its ran on a trusted site.
perhaps - e is exit? or is this specifying the format of the data coming out of the sql server db into this txt file?

the variable @cmd is declared as a varchar already...
The variables @db, @tbl and @path are declared already

SELECT @cmd = 'BCP "SELECT * FROM ' + rtrim( @db) + '..' + rtrim( @tbl) + ' WITH(NOLOCK)" QUERYOUT "' + rtrim( @path) + rtrim( @tbl) + '.txt" -w -S' + @@servername + ' -U sa -T -E'

The second question I have is when running this i call the master..xp_cmdshell to help export the data to the correct files.
Since i am running this on Sql Server. My question is, if i were to run this on another database type, such as Oracle, what would i use instead of the xp_cmdshell >?

is there a comparison to it on Oracle to enable BCP?

Thank you,
Question by:Putoch
  • 4
  • 3
  • 2
  • +2
LVL 29

Assisted Solution

MikeOM_DBA earned 125 total points
ID: 39639379
In Oracle the equivalent to xp_cmdshell would likely be: SQL*Plus.
You cannot run either BCP or "as is" the query you posted, but you could code and equivalent query that would execute under sqlplus.

As per the BCP parameters, refer to this link: bcp Utility
LVL 16

Assisted Solution

DcpKing earned 125 total points
ID: 39639474
In addition to what MikeOM_DBA says, have you thought of trying out SSIS? You should be able to pull data from the SQL Server system and put it straight into the Oracle system without going through text files. If you do have to go through text files, I personally find SSIS much easier to work with than BCP!


LVL 26

Assisted Solution

Zberteoc earned 125 total points
ID: 39639609
One thing though,

If you use -T switch, which means Trusted connection, then it is not compatible with the -U(user) sa switch. You should use:

SELECT @cmd = 'BCP "SELECT * FROM ' + rtrim( @db) + '..' + rtrim( @tbl) + ' WITH(NOLOCK)" QUERYOUT "' + rtrim( @path) + rtrim( @tbl) + '.txt" -w -S' + @@servername + ' -T -E'

Open in new window

Another thing is if you want to export the whole table then you can use just the name of teh table and OUT instead of QUERYOUT:

SELECT @cmd = 'BCP ' + rtrim( @db) + '..' + rtrim( @tbl) + ' OUT "' + rtrim( @path) + rtrim( @tbl) + '.txt" -w -S' + @@servername + ' -T -E'

Open in new window

BCP is a windows product and usually comes with SQL server. If you don't have SQL server on a box most likely you don't have BCP either.
LVL 29

Expert Comment

ID: 39639848
DcpKing's proposal to use SSIS is an excellent suggestion, you avoid many head-aches.
LVL 26

Expert Comment

ID: 39639890
"DcpKing's proposal to use SSIS is an excellent suggestion, you avoid many head-aches."

I beg to differ. When you try a simple export/import wizard in SQL >= 2005, which actually is an SSIS package that you execute instantly, you will run into varchar and nvarchar data types errors, which I expect will happen in an SSIS package as well. BCP is the simplest solution for this kind of operations and it will work with no issues.
LVL 16

Expert Comment

ID: 39640025
This looks like becoming an idealogical war, but hopefully a polite one.

When creating an SSIS package you can can indeed encounter the errors that Zberteoc refers to. These are most often associated with using Excel as an input source, as it is always treated as unicode. However, in this case you are dealing simply with a flat file, so you should encounter none of these problems.

If you decide to go directly into Oracle then you'll have to use Microsoft's OLD DB interface into Oracle, which may have problems with unicode strings, or it may not - I haven't used it enough to tell.

I'd suggest at least trying it out. Regarding the simplest data export with the wizard, you shouldn't have problems - I just tried it out with a table with an nvarchar field with no problems at all!


IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

LVL 26

Expert Comment

ID: 39640084
There is no ideological war here just simple experience.

It happened with CSV files as well and not only that, when importing from one database table to another database table on the same server! I am talking about wizards, which is very frustrating.

It is possible that SSIS will work.

What's wrong with BCP anyway, when it comes to such a trivial thing like exporting the content of a table? Why would you use SSIS, when maybe is not even installed, or maybe using SQL EXSPRESS version? We are talking about simple stuff here and not some complex ETLs.

Author Comment

ID: 39640206
Thanks all for the advice.
in my present situation I cannot use SSIS - which was why I was going to go the BCP route.
I would prefer to work with SSIS but purely because that is what I am more comfortable using. Since you can imagine when i'm here looking for the meaning of command arguments, I have more control with SSIS and feel I can do more .

regardless, thank you all for the info above.
I will look into other ETL solutions, but open source it will have to be. Has anyone any experience with Talend?
LVL 16

Expert Comment

ID: 39640377
I didn't know you weren't allowed to use SSIS, or I wouldn't have suggested it.

BCP parameters are

-w      Uses unicode throughout
-s       There isn't an "s" switch that I know of. There is an "S" one though, so .....
-S       The Server name. Can be "server" or "machine\server"
-U sa  U is the login ID, as you might have guessed. Here it's specifying "sa".
-T        Trusted connection - i.e. your Windows login, so you don't need -U and -P
-E        SQL Server allows you to define a column as being an identity column
           (i.e. autonumber). Using this tells bcp to write the values from the incoming file
            into the identity column. Otherwise SQL Server auto-generates.

Your code looked ok, excepting that you will need a -P to specify the password for the sa user that you specified.

bcp is a program that's really part of SQL Server. So far as I am aware you can't use it on other databases (except possibly Sybase). For Oracle you might look here for an explanation:


LVL 26

Expert Comment

ID: 39641319
Again, if you use -T, trusted connection, you don't need any user and/or password.
LVL 76

Accepted Solution

slightwv (䄆 Netminder) earned 125 total points
ID: 39641528
>>My question is, if i were to run this on another database type, such as Oracle

If you have a txt file and want to get it into an Oracle database: SQL Loader or External tables.

The online docs talk all about these:

Author Comment

ID: 39652684
Thank you all for the advice.
I will continue to read these articles.
It looks like I need to change the BCP SP that I had been using to export the data out from the SQL server - as the formatting is unreadable when inserting into our Oracle db.

thank you, p

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now