Hi

(Both files in same database and schema.)
I have a table called "Sales" and a table called "SaleHistory".
Both files have the same exact structure in terms of fields, indexes, primary key etc.

Q. I wish to move all data records from "Sales" table to "SalesHistory" table
(so the end result would be a move all records from one table and append to another)

Is there an elegant SQL solution with edb to do this using delphi code ?
Perhaps a query ?

I looked at doing something like "INSERT INTO SalesHistory Select * from Sales"
But I got a primary key violation so perhaps that is not possible or I am doing something wrong.

I remember there used to be a "batchmove" but that was for the borland BDE engine.

Thanks

Kamran

kamran


What you've posted suggests that you have an autoinc as the primary key for the tables. As long as its not used as a foreign key anywhere that doesn't matter. If it is you may have a problem.

You can still use the INSERT INTO syntax, all you need to do is eliminate the primary key from it.

INSERT INTO SalesHistory "list of fields excluding primary key" Select "list of fields excluding primary key" from Sale

Roy Lambert

Hi Roy

Thanks for your response.

I was hoping to retain the EXACT copy of all data including the primary keys in the "SaleHistory" file.
But it looks as if that is not possible from what you are telling me?

Just some further info:

The "Sales" file also has a master relationship with a "SalesHistory" file
with the keys "SalesHistory_id" and "SalesHistory_id" which are of type GUID
GUID is generated automatically in the SalesHeader file.

Yes...   there are foreign keys!!

So I guess I will have to do this separately for each and allow the system to regenerate the primary
keys that have been excluded by the query?

Am I understanding that right ?

Kamran

Roy Lambert wrote:

kamran

What you've posted suggests that you have an autoinc as the primary key for the tables. As long as its not used as a foreign key anywhere that doesn't matter. If it is you may have a problem.

You can still use the INSERT INTO syntax, all you need to do is eliminate the primary key from it.

INSERT INTO SalesHistory "list of fields excluding primary key" Select "list of fields excluding primary key" from Sale

Roy Lambert

kamran wrote:

Sorry a typo...  this should read

Just some further info:

The "Sales" file also has a master relationship with a "SalesHeader" file
with the keys "SalesHistory_id" and "SalesHistory_id" which are of type GUID
GUID is generated automatically in the SalesHeader file.

kamran

>I was hoping to retain the EXACT copy of all data including the primary keys in the "SaleHistory" file.
>But it looks as if that is not possible from what you are telling me?

I need to see the table structures & indices before I can be sure, From what you've posted so far I think you may have a problem with the actual structure of the tables.

>The "Sales" file also has a master relationship with a "SalesHistory" file
>with the keys "SalesHistory_id" and "SalesHistory_id" which are of type GUID
>GUID is generated automatically in the SalesHeader file.
>
>Yes... there are foreign keys!!

On this forum we can only give so much advice. Partly that depends on what you tell us. This last bit of information really makes me think you have a problem in the making. Without the table structures, and an understanding of what's happening with the tables its not possible to say much. However, it reads as though you're copying data from a file including a foreign key pointing to the file you're copying into. I doubt that is good news.

>So I guess I will have to do this separately for each and allow the system to regenerate the primary
>keys that have been excluded by the query?
>
>Am I understanding that right ?

No and Yes. You'll only need one sql statement to do the copying and the system will create the primary keys for the destination table.

Can you post the table structures and explain just what it is you're doing with this move please. Also I assume that after the move from sales to sales history you delete from the sales file ?

Roy

Hi Roy

Yes here are the "actual" structures, index definitions and primary keys
(simplified names for tables were used earlier)

1. The "TranHeader" table will want to move all records to table called "TranHeaderH"  (exactly the same structure and keys etc.)

2. The "TranDetail" table will want to move all records to a table called "TranDetailH"    (exactly the same structure and keys etc.)

3. There is a master detail relationship on both these tables using the "th_id"  field that is defined in both tables.

4. The TranHeader and TranDetail will be empty after the move.
5. The TranHeaderH and TranDetailH will have the appended records.

Thanks

Kamran

Roy Lambert wrote:

kamran

>I was hoping to retain the EXACT copy of all data including the primary keys in the "SaleHistory" file.
>But it looks as if that is not possible from what you are telling me?

I need to see the table structures & indices before I can be sure, From what you've posted so far I think you may have a problem with the actual structure of the tables.

kamran


The best way to generate the table details is to copy the sql creation script without the code to add the rows.

If you open a blank sql window and just drag the table from the left hand tree into it EDBManager will create the sql code for you. You can then drag the indices into the same window BELOW the table sql and it will add those. You can also use the Reverse Engineer option for the database which gives you all the tables, triggers etc


I'm beginning to understand what you're doing - a fairly standard sales master and sales lines type of thing.

If this is just starting life and you are able to change things that's good. If not you have a bit of a problem.

What I'd do  is alter the structure on the two history tables and take out the default for the primary key. You should NOT be calculating this again for that table. GUIDs should be unique so if you're using Tim's CURRENT_GUID as the default either you've tried to copy the same data twice or less likely there's a problem with Tim's generating a GUID value.

You have  td_id and td_line_no. I'd dump td_id and make a compound primary key of th_id and td_line_no

I don't know what auditors are like in your country but in the UK they would be upset if they have trouble tracing physical documents in a database and if you start changing keys that may annoy them. So I'd also look at what's printed on any physical document (order, invoice or reports) and decide if I really needed the GUIDs to start with - they may be unique but they're not user friendly.

Final comment is that you need to make the copy / delete atomic so it might be a good idea to write a stored procedure with whatever you want to use as selection criteria as the parameters and build in a transaction so that if a problem occurs the whole thing is rolled back and you don't end up with some bits transfered and not deleted.


Roy Lambert

Hi Roy

I made an exact copy of the tables TranHeader and TranDetail by adding an H at the end of the table names
using the the "Create Copy of Table" option in edb manager and then I thought that I should duplicate the index names so I recreated them in the history files to match and yes, I guess I should remove the primary key bits in the history files.

Thanks for looking at it,  and the recommendations.

I will give it go.

Cheers

Kamran

Roy Lambert wrote:

kamran


The best way to generate the table details is to copy the sql creation script without the code to add the rows.

If you open a blank sql window and just drag the table from the left hand tree into it EDBManager will create the sql code for you. You can then drag the indices into the same window BELOW the table sql and it will add those. You can also use the Reverse Engineer option for the database which gives you all the tables, triggers etc


I'm beginning to understand what you're doing - a fairly standard sales master and sales lines type of thing.

If this is just starting life and you are able to change things that's good. If not you have a bit of a problem.

What I'd do  is alter the structure on the two history tables and take out the default for the primary key. You should NOT be calculating this again for that table. GUIDs should be unique so if you're using Tim's CURRENT_GUID as the default either you've tried to copy the same data twice or less likely there's a problem with Tim's generating a GUID value.

You have  td_id and td_line_no. I'd dump td_id and make a compound primary key of th_id and td_line_no

I don't know what auditors are like in your country but in the UK they would be upset if they have trouble tracing physical documents in a database and if you start changing keys that may annoy them. So I'd also look at what's printed on any physical document (order, invoice or reports) and decide if I really needed the GUIDs to start with - they may be unique but they're not user friendly.

Final comment is that you need to make the copy / delete atomic so it might be a good idea to write a stored procedure with whatever you want to use as selection criteria as the parameters and build in a transaction so that if a problem occurs the whole thing is rolled back and you don't end up with some bits transfered and not deleted.


Roy Lambert