Question Re: Sybase data transfer tool in Technology Q&A

Sybase data transfer tool

Former Member — Tue, 24 Mar 2015 09:12:36 GMT

Hi,

Is there a tool available which allows me to select a table in Sybase, and have it copied to another Sybase database?

That means create the table when it does not exist, and insert all records from source to target.

I'm new to Sybase and have the SQL Server Import Wizard in mind, which lets me do just that.

BODS is used in the project which we can use to get data across but we have hundreds of little lookup tables with validated data which we need in the next DTAP environment. Building all these transfers individually will probably take ages.

Re: Sybase data transfer tool

former_member187136 — Tue, 24 Mar 2015 13:05:35 GMT

Hello Opstal,

There is a tool called bcp which you can use for copying the table and data to a flat file and pasting it in the destination

1 bcp out

2 bcp in

Further details you can get in from http://infocenter.sybase.com

Regards

Kiran K Adharapuram

Re: Sybase data transfer tool

former_member187136 — Tue, 24 Mar 2015 14:09:20 GMT

Mark,

Even I didn't use the "sybmigrate" so I didnt suggest the tool to Opstal

My intention of asking for the bcp is that Opstal will take the table scheme from may be internal tool like ddlgen or any 3rd party tool

Thanks for bringing this point, as a point to add Mark is bcp in & out is just dealing with the "Table Data" this is nothing to do with the structures/schema

Am I right Mark

Regards

Kiran K Adharapuram

Re: Sybase data transfer tool

former_member229367 — Tue, 24 Mar 2015 23:10:14 GMT

I'm assuming this isn't on the same ASE.

You could consider adding a remote server then a proxy database. Then you can do cross-database operations between the two. I'd use that to create the table with a zero-row statement ("select ... into... where 0=1" for example).

If you aren't talking huge volumes of data you could even transfer the data that way.

But for speed considerations I'd probably use BCP out/in to transfer the actual data.

Re: Sybase data transfer tool

former_member187136 — Wed, 25 Mar 2015 00:46:58 GMT

Yes proxy dB/tables are good option.Also if we run the command to find table structure 1=2 will not yield correct because only ddlgen gives you table structure and the respective constraints and table level access permissions.

Also I agree bcp will be faster and. You can copy data in chunks by specifying number of rows needed through the bcp utility.

Cheers

Kiran K Adharapuram

Re: Sybase data transfer tool

former_member229367 — Wed, 25 Mar 2015 00:57:19 GMT

If I'm entirely honest I'd write a proc to generate the DDL and use that to (re)create the destination table.

But that's just me. I didn't get the sense that the OP wanted to deal with that kind of complexity.

I guess more than anything I was trying to outline an avenue that I hadn't seen on here yet - remote server with proxy DB.

Re: Sybase data transfer tool

former_member187136 — Wed, 25 Mar 2015 01:42:54 GMT

Hello Douglas,

Automating entire thing through script is very good idea in fact I used to follow the same way for the ddlgen even I didn't test or worked on proxy stuff but however I am sure there will be documented information in

http:infocenter.sybase.com

What do you say?

But I am really interested what is his strategy and approach for data transfer so that we can help with good recommendations accordingly.. Am I correct 😉 ?

Cheers

Kiran K Adharapuram

Re: Sybase data transfer tool

former_member229367 — Wed, 25 Mar 2015 02:07:51 GMT

Yes spot on. Without knowing the OPs full requirements it's very difficult to make a good recommendation.

It kind of sounds like he wants a GUI tool similar to MSSQL import/export data. For that he'd need to go third-party.

But if he just wants to do a once-off copy of a table and is he could use DBArtisan/Sybase Central/ddlgen/others for the DDL and then BCP for the data.

For a scheduled job of some kind ddlgen and BCP might work.

For a scheduled all-inside-ASE job there' proxy DBs and cross-database select.

Use case, use case, use case. That's the key!

Re: Sybase data transfer tool

Former Member — Wed, 25 Mar 2015 07:54:31 GMT

Thanks for all answers.

It sounds like there is no ready-to-use GUI tool from Sybase for this purpose and I have to look into third party if I want to do things easy...

As I'm new to Sybase, scripting is something I would rather avoid at this point but I'm sure I will need to spend time on that.

As (I think) all solutions work on a table by table approach, I may as well use BODS for this, right?

Re: Sybase data transfer tool

former_member187136 — Wed, 25 Mar 2015 08:02:20 GMT

Hello Opstal,

Yes we can run the utility on any tables provided you have login and access on those tables

There are quite third party utilities available in market for this, I am not quite aware as the internal tools serves the purpose if we are familiar with the internals

However there are GUI tools to do this activity as you said you are correct

Regards

Kiran K Adharapuram