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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 27

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.
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

LVL 29

Expert Comment

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

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!


LVL 27

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 27

Expert Comment

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

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

738 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