Automating Exporting tables and relationships to separate db

[netsmurph]

I have been issued with the challenge of creating a procedure to take data from one database and effectively split this into separate mdbs.

I have written a procedue to create the appropriate tables in the source db and export them each to the destination db.

The issue i have is that the indexes and keys are not created.

Can anyone suggest how this could be done? (it does not seem there is an ALTER TABLE command to create a primary key on a table).

Could anyone suggest a better solution - would it be easier to create the tables/indexes/keys in the destination db using ExecuteSQLDDL, export the tables to temp tables in the destination db, append the data for each and then delete the temp tables.

Sorry for the long post, but wanted to fully explain my logic ;-)

Thanks in advance.

Andrew

 

[PHV]

it does not seem there is an ALTER TABLE command to create a primary key on a table
CREATE [ UNIQUE ] INDEX index_name
ON table_name (field_name [ASC|DESC][, field_name [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netsmurph]

Many thanks PHV here i got it working perfectly now, although im still not sure whether there a more elegent way of achieving the same thing

 

[PHV]

You may use the CONSTRAINT clause in your CREATE TABLE instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886