I'm trying to create a command to copy the details of client memberships from one database to another with identical structure. I've pared it down to the bare essentials for the purposes of the question so the four items to copy are the expiry date, the subscription ID, the client ID and the item ID (which is the service which comprises the subscription).
The clients have a common GUID in both bases. The subscription ID is a unique long int which should be the same in both bases and the expiry date is just a date. So far, so easy. The tricky part is that the item_id is not necessarily the same in each database. I need to map from one to the the other with a where statement, which I know how to do.
My problem is that I need to select from the destination database's own ITEM table (item_0) in order to get and insert the correct item_id and when I do this I get thousands of duplicate rows returned. I assume I need to use a join to avoid this but as I have nothing meaningful to join item_0 to I can't get any results.
insert into DestDB..subscription (expiry_date,id,client_id,item_id)
cli_0.id as client_id,
item_0.id as item_id,
from SourceDB..subscription sub_1,
inner join SourceDB..client cli_1
on cli_1.[guid] = cli_0.[guid]
where sub_1.id not in (select id from DestDB..subscription)
and item_0.id =
(select id from DestDB..collectiondetails
where service_ID =
(select id from DestDB..service s_0 where s_0.code =
(select code from SourceDB..service s_1 where s_1.id =
(select service_ID from Source..collectiondetails item_1 where item_1.id = sub_1.item_id)))
and collection_ID =
(select id from DestDB..collection col_0
where col_0.code =
(select code from SourceDB..collection col_1 where col_1