Question on BCP

Question

Post reply

Question on BCP

itmasterw 60042

SSCrazy

Points: 2858
More actions
March 24, 2021 at 6:12 pm

#3861736
I have been looking everywhere, and cannot find anything on this .
Can anyone tele me if you can used BCP and xp_cmdshell to insert data from one table to another?
and if this can be done please let me know where I can get information on this.
Thank you
- This topic was modified 3 years, 1 month ago by itmasterw 60042.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Michael L John One Orange Chip Points: 27038 More actions · Answer 1

Sure. Try it.

Some sample code:

DECLARE @cmd varchar(1000) = 'BCP DatabaseName.Schema.Table OUT C:\BCP\ExportStuff.txt /m 100 /b 100 /e ExportStuff.err /c /r\n /t, /S ServerName /T'

EXEC xp_Cmdshell @cmd

Why do you want to do this out of curiosity?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 2

Hi,

I appreciate the reply, but I am looking to go from table to table and not to a file first. This I have found everywhere, so maybe it cannot be done. We are currently using SSIS, but we have and for some reason SSIS will not work there any more. So I am trying to find alternatives. I do not want to go to a file because as it is this going over a link serve and I am concern it will take to long if it ahs to go to a file first.

Thank you

Michael L John One Orange Chip Points: 27038 More actions · Answer 3

BCP does not go from table to table. It is an import/export utility that goes to a file.

If you are going from table to table, why won't T-SQL work?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 4

Hi,

Thanks again for the reply, that saves me a lot of time from looking.

I can use this, however, I thought the Bulk copy worked better if you were moving a lot of data from one server to another. it is not an extrema amount, 5.7 millon records. Currently, I have ben able to do this by using SSIS on my local machine from Datatools, and that works but takes about an hour.

Thank you

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

You stated that 'for some reason' SSIS no longer works? What is that reason?

Using a linked server (assuming this is cross server access) can cause issues - with the transaction log, tempdb, etc. It all depends on how it is written and what is being extracted across the linked server.

For in memory type transfers (eg: database to database without a file) you options are going to be limited to either a linked server - or an ETL tool (SSIS, Informatica, Ensemble, etc.). Or - you can try to roll your own...but that can also be a lot of work not only to build, but to manage and maintain.

If you are already using SSIS - stick with that and fix whatever problem(s) you have encountered.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Ken McKelvey SSCoach Points: 18768 More actions · Answer 6

You can also use Bulkcopy from Powershell but I would expect this to run in a similar manner to SSIS.

eg

https://devblogs.microsoft.com/scripting/use-powershell-to-copy-a-table-between-two-sql-server-instances/

https://www.mssqltips.com/sqlservertip/6009/how-to-copy-a-table-in-sql-server-to-another-database/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

I believe you'll find that and INSERT/SELECT from an OPENQUERY across a linked server will work with some pretty good performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 8

Jeff Moden wrote:

I believe you'll find that and INSERT/SELECT from an OPENQUERY across a linked server will work with some pretty good performance.

As long as you are careful in how much data is being transferred. There is no way to 'batch' the results from OPENQUERY - but if you have plenty of space available in the transaction log (and tempdb) then it can work quite well.

I still would like to understand why SSIS is no longer working...always have hated the expression 'for some reason it no longer works'. If we understood what changed - and why it no longer works - we could offer alternatives that do work. Especially if they have moved to Azure/AWS or other cloud setting where setting up linked servers may not be possible.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

If you avoid "batching", it can be done with "minimal logging", which also nearly doubles the performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 10

HI Jeff,

I apricate your wanting to know I did not want to bother you with it, but I would like to get the SSIS to work. As I said above, I have a simple SSIS package that Inserts into a table based on a select query. When I run it I get the following error "Unable to complete login process due to delay in opening server connection."

Now, I created an simple insert into an local table through an SSIS package and that worked great. So, it seems to be something with an OLE DB Connection, but I do not know.

I also put in the latest OLE DB driver.

Any ideas I would appreciate it.

Thanks

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

itmasterw 60042 wrote:

HI Jeff,
I apricate your wanting to know I did not want to bother you with it, but I would like to get the SSIS to work. As I said above, I have a simple SSIS package that Inserts into a table based on a select query. When I run it I get the following error "Unable to complete login process due to delay in opening server connection."
Now, I created an simple insert into an local table through an SSIS package and that worked great. So, it seems to be something with an OLE DB Connection, but I do not know.
I also put in the latest OLE DB driver.
Any ideas I would appreciate it.
Thanks

I know I'm the wrong "Jeff" but, rumor has it that SSIS packages sometimes lose their minds and simply need to be rebuilt. I cannot confirm nor deny that rumor because I don't use SSIS and one of my fortes is the elimination of SSIS packages. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 12

itmasterw 60042 wrote:

HI Jeff,
I apricate your wanting to know I did not want to bother you with it, but I would like to get the SSIS to work. As I said above, I have a simple SSIS package that Inserts into a table based on a select query. When I run it I get the following error "Unable to complete login process due to delay in opening server connection."
Now, I created an simple insert into an local table through an SSIS package and that worked great. So, it seems to be something with an OLE DB Connection, but I do not know.
I also put in the latest OLE DB driver.
Any ideas I would appreciate it.
Thanks

This sounds like a network issue - if you can connect to a local instance (assuming by local you mean on your workstation) but cannot connect to a remote instance and a timeout occurs, then something is causing an issue with that connection.

If this is something different - for example, local means on-prem vs cloud - that would be good to know also.

You stated this was working...and now it isn't, so what changed?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 13

Hi,

I apricate everyone's help here. I think I got this to work, at least for this anyway.

Someone asked if I rebuild it, and yes I also had packages that do not work unless you rebuild them. So I did do that here, but it still did not work.

Finally, I changed the destination and the connection type to an ADO.Net type, redeployed it and it worked.

Thank you