?
Solved

Nesting a SELECT statement inside an INSERT statement

Posted on 2014-07-23
1
Medium Priority
?
229 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 2000 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

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
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

770 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