Ask the Expert

Copying updated table from test to production system

We make updates and additions to an item table in our test system, and we want to copy that table to the production system without taking production down. I tried to truncate the production item table and insert all of the rows from a temporary item table with the following commands:
     truncate table item

     insert item
     select *
     from item_tmp
This works, but I have to take the production system down to use this. I was hoping to use a cursor and delete a row, then insert the corresponding row from the temp table so that only that item would be locked and I wouldn't have to take the production system down. In Oracle, I can declare a cursor and define a cursor record using the cursor_name%ROWTYPE, then I can delete the item in the production table and insert the current value of the cusor record. Sybase will only let you do updates and deletes with a cursor, no inserts. I also don't want to define each column from the table in the cursor, there are close to 100 columns in the table.

    Requires Free Membership to View

Actually I do not think this is the best way of dealing with this issue.

If you want effectively to use the test table "item" data as the production table all you need is to create a proxy table in production which will be the test table itself. In that case whatever happens to test table data (insert/update/delete) all will be reflected in the production real time. To clarify this point, the user will use the proxy "item" table in production which in reality will be your test table. This will avoid the issue of using cursors, etc., which are inefficient in both Sybase and Oracle. If you need more info please come back.

This was first published in November 2003

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: