Solved

Nesting a SELECT statement inside an INSERT statement

Posted on 2014-07-23
1
221 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 14

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now