Hi,

I have a SQL database that I need to populate from an access database. The access database has two tables - order and orderDetails. For historical reasons, these are defined below:

Order:
OrderRef varchar(5)
Description varchar(20)

OrderDetails:
OrderDetailsRef varchar(5)
OrderRed varchar(5)
ItemDescription varchar(20)

I need to transfer the data in these tables to a SQL database. The two tables are defined in SQL as follows:

Order:
OrderId int
OrderRef varchar(5)
Description varchar(20)

OrderDetails:
OrderDetailsId int
OrderId int
ItemDescription varchar(20)

Note that the Id columns have changed from being character based to using int (for a SQL identity column)

So in essence if I have the following data in access:

Order:
OrderRef Description
ABC1 A first Order

OrderDetails:
OrderDetailsRef OrderRef ItemDescription
XYZ2 ABC1 Item1

I want this to appear in the SQL database as:
Order:
OrderId OrderRef Description
1 ABC1 A first Order

OrderDetails:
OrderDetailsId OrderId ItemDescription
1 1 Item1

I can easily transform the order table to the new structure, allowing SQL to insert the new Id's in the identity column, and keeping the old char based OrderRef field. However, the problem comes when I am trying to transfer the orderdetails table. How can I keep the link between order and orderdetails. If I use a data transform task, how do I know what value to put into OrderId in the orderdetails table? Is there some mechanism that will enable me to find the new Id of the order given the old character based Ref?

Many thanks in advance

Tony Joselin

The easiest is to import the order details in a staging table with the same
structure as the Access table and then populate the OrderDetails table with
the following statement:

INSERT INTO OrderDetails (OrderId, ItemDescription )
SELECT o.OrderID, st.ItemDescription
FROM Order o
INNER JOIN StagingTable st
ON o.OrderRef = st.OrderRef

--
Jacco Schalkwijk
SQL Server MVP


"Tony Joselin" <Tony Joselin (AT) discussions (DOT) microsoft.com> wrote

reasons, these are defined below:
based OrderRef field. However, the problem comes when I am trying to
transfer the orderdetails table. How can I keep the link between order and
orderdetails. If I use a data transform task, how do I know what value to
put into OrderId in the orderdetails table? Is there some mechanism that
will enable me to find the new Id of the order given the old character based
Ref?