INSERT and UPDATE same record to new database

[ombinar]

Hello,

I have source table called Customer and child table called Orders. I need to copy all Customer records to a destination database, which Customer table already exists. My problem is copying all the records of the Orders table, which foreign key is CustomerID from Customer table's source database.

How can I write my a SQL statement so that Orders records copied to destination contain CustomerID from Customer table in the destination database?

Thanks in advance..

RO

 

[bborissov]

INSERT INTO DestOrders (field list here)
SELECT (field list here)
FROM SourceOrders
INNER JOIN DestCustomersTable ON SourceOrders.CustomerId = DestCustomersTable.CustomerId

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[davejam]

is the customerID going to be the same internally and externally... if not, this is the problem.

When creating the customer records to the external source you need some kind of link back to the original data... such as passing the ID to a field such as intRef... this way you can have a link to the customerID in your orders table.
Then when you create your orders you can match up against this field to get the external customerID, you can then use this as your link between the external tables (customer and orders)

If you are having data in two areas with potentially different ID's, it is always key to have a link back, even if its just for error checking!!

daveJam

easy come, easy go!!!