Solved

Nesting a SELECT statement inside an INSERT statement

Posted on 2014-07-23
1
224 Views
Last Modified: 2014-07-23
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]
      ,[AgencyID],[create_date], [rUnitProviderId])
  
  SELECT [ProviderNumber],[ReportingUnit],[UnitName],[SiteIdentifier],[SiteName]
      ,'2' ,getDate(),  

(SELECT ProviderID FROM Provider WHERE AgencyID = 2 AND ProviderNumber = THIS IS WHERE I NEED HELP!!!

  FROM [ReportingUnits]
  WHERE Agencyid = 1

Open in new window

0
Comment
Question by:huerita37
1 Comment
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 500 total points
ID: 40216028
Hi,

I think this will help you to get what you wanted.

INSERT INTO ReportingUnits ([ProviderNumber],[ReportingUnit],[UnitName],[SiteIdentifier],[SiteName]
      ,[AgencyID],[create_date], [rUnitProviderId])
  
  SELECT [ProviderNumber],[ReportingUnit],[UnitName],[SiteIdentifier],[SiteName]
      ,'2' ,getDate(), (SELECT ProviderID FROM Provider b WHERE AgencyID = 2 AND b.ProviderNumber = a.[ProviderNumber]) rpid

  FROM [ReportingUnits] a
  WHERE Agencyid = 1

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question