on 04-20-2016 2:57 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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.
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
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.
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.
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.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.