I copied data from table 'Provider' with agencyID = 1 and INSERTED INTO the same table with an agencyID = 2. This table has an ID. I need this ID in my next INSERT INTO statement. There are many rows for agencyID = 2 in the Provider table. There will be a many to many relationship between the two tables.
The next table I will be using is the 'ReportingUnits' table. I will do the same thing. Coping the data WHERE agencyID =1 into the same table with agencyID = 2. [rUnitProviderId] field is the primary key of the provider table. How can I populate this field with a SELECT statement inside my INSERT statement?
Here is my query:
INSERT INTO ReportingUnits ([ProviderNumber],[ReportingUnit],[UnitName],[SiteIdentifier],[SiteName]
(SELECT ProviderID FROM Provider WHERE AgencyID = 2 AND ProviderNumber = THIS IS WHERE I NEED HELP!!!
WHERE Agencyid = 1