cancel
Showing results for 
Search instead for 
Did you mean: 

Copy table from different database

Former Member
0 Kudos

Hi

If I want to copy an existing table in the same DB I would use something like this

INSERT MyTable_Backup ON EXISTING UPDATE WITH AUTO NAME SELECT * FROM MyTable

Assuming I can lookup the name of the server and the database I want to copy from

e.g.

Server = S002

DB = MyDB21

How can I use that to copy from a different database, or even a different server?

Thanks

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member89972
Active Contributor
0 Kudos

If the purpose is to keep the tables in sync there are three options to achieve this

BCP

- bcp out from source and bcp in to the target

PROXY

- proxy tables (similar to tables accessed via linkserver in MSSQL) needs set up of remote source

server + security set up to access data (preferably same login + p/w to minimize lookups)

REPLICATION

- replication. This needs more effort but the rewards of keeping data in sync are really great.

  This will also scale up if you multiple tables to be in sync almost in real time.

Option 1 can be used if it is one time requirement really.

Option 2 can be used if the requirement is a few tables and mostly lookup + reference tables and you do not need real time data sync

Option 3 can be used if you have large set of tables to be kept in sync.

HTH

Avinash

Former Member
0 Kudos

Hi Avinash

There is no need to sync the tables - here is the scenario..

Customers that use our software have a DB for each of their customers. When they get a new customer a default DB is created with tables fields and default data. Say the new customer has a requirement for a cash flow report that is identical to an existing customer? Here we just copy that table from customer A to customer B and it gets them off and running. Perhaps some modifications but a lot faster than creating the report from scratch.

This utility is a 'one-off' just to save time getting a new customer up and running

former_member89972
Active Contributor
0 Kudos

Hi Dave

In ASE we have model database that is useful as template database to create a new database from.

We can customize it beyond what SAP/Sybase provides.

Not sure about ASA though.

In either case you could have a super-set of tables/objects in the template database and use that as a starting point. 

HTH

Avinash

Former Member
0 Kudos

Avanash

We have tables that hold the templates for all new Databases, so we can generally have those up and running in under 60 seconds. It's when a customers new customer has a requirement that is very similar to an existing table that has been built up from the template they can copy the data from one to the other and just clean up rather than reinvent the wheel.

Regards

Dave

former_member89972
Active Contributor
0 Kudos

Ah now I got it. 

Very special requirement I must say,  Model/template + plus something more governed in a dynamic way.

Cool. Good luck. And let us know what path/solution you adopted finally !

Avinash

Former Member
0 Kudos

Hi Dave,

You can:

1. Create data source to S002/MyDB21

2. Create remote server (link)

3. Create a proxy table (link)

Hope this helps,

Jinwoo

Former Member
0 Kudos

Hi Jinwoo

Thank you for the response, but I think you might have misunderstood the question. Not wanting to create new tables/tables/servers

All tables, databases and servers already exist, so the intent is to

1. Delete Table1 in Database1 on Server1

2. Copy Table 2  in Database2 on Server2 to Table1 in Database1 on Server1

Thanks

Former Member
0 Kudos

Hi Dave,

Does the copying of records have to be done over database server-to-database server communication only? I had feeling that was the case and that's why I came up with using a proxy table. To use SELECT INTO, you need an intermediate place to hold those records (i.e. SELECT INTO t1 FROM {proxy_table}).

Or you could use UNLOAD statement and use LOAD statement back again. If Server2 has allow_write_client_file option on (SET OPTION PUBLIC.allow_write_client_file = 'ON';), then you can unload the table into the machine you are connecting from (UNLOAD TABLE t1 INTO CLIENT FILE 'c:\\demo\\testdata.dat' ENCODING 'utf-8') and LOAD it back to the Table1 of Server1.

I hope I understood your requirement correct this time or let me know what I'm missing.

Regards,

Jinwoo

Former Member
0 Kudos

This is how we currently do it, but it's a bit cumbersome - was just looking for something a little cleaner. Had to add a text file as there seems no way to paste into this editor (right click  - Ctrl-V) nothing works

Former Member
0 Kudos

This application connects to a Web Service, and the web service makes the request to the DB and sends it back to the application

former_member188493
Contributor
0 Kudos

When you create a remote server, you are just creating a local name to use in references to the other server. The syntax CREATE SERVER could just as well have been CREATE LOCAL ALIAS alias_d FOR REMOTE DATABASE other_d.

When you create a proxy table, you are just creating a local name to use in references to a table on the other server. The syntax CREATE EXISTING TABLE could just well have been CREATE LOCAL ALIAS alias_t FOR REMOTE TABLE other_t ON alias_d.

Remote servers and proxy tables are the recommended way to copy SQL Anywhere tables from one database to another...

INSERT local_t SELECT * FROM alias_t;

...so, you are not really "creating" servers or tables.

Former Member
0 Kudos

Thank you for the clarification. I will have go at that syntax today

Former Member
0 Kudos

If we can be sure that all the Databases that contain tables to be copied from are on the same server as the databases/tables that are being copied to, is there a method to create a proxy table that doesn't require CREATE SERVER?

As all the interaction is done by the Web Service we can't specify connection strings directly in the client application  - all the methods I have tried so far fall over with - Remote server 'ServerName' could not be found

chris_keating
Advisor
Advisor
0 Kudos

If you are doing this via a proxy table, you need a remote server as that defines the connection that SA needs to communicate to the other database - SA cannot directly access a different SQL Anywhere database even if running on the same SQL Anywhere server.

How does the Web Service fit here. Is it simply getting data from SA or is SA acting as a WS client and/or WS server?

Making a guess, I think that you could setup a SA WS server and implement the table copy in the context of a stored procedure that is invoked as a WS.

former_member188493
Contributor
0 Kudos

Show us the code. There should be no problem executing CREATE SERVER inside a SQL Anywhere web service (CREATE SERVICE). If you are talking about a web service running outside SQL Anywhere, perhaps you can put the CREATE SERVER inside a SQL Anywhere stored procedure and call it from your external process.

Former Member
0 Kudos

The application is WPF, that connects to a MS Web Service running on IIS. All the connection strings are defined in the Web Service that queries the Sybase DB. I am quite sure we could get this working from within the Web Service. However the Web Service is running on .NET 4.0 and is getting a little creaky - later this year we intend to update a host of hardware and software when Server 2016 comes out, and that will involve updating Sybase to the current version updating the Web Services to .NET 4.6x

Making any updates to a service that is used 24/7 when VS keeps throwing the toys out is unnerving to say the least so will probably delay until we have something newer and can test it offline.

The stored procedure sounds like a good idea. Can we call from any DB? When I say that we can start, stop, delete copy and generally manipulate one DB whilst connected to another which suggests we should be able to perform other tasks.