title: Create a stored procedure to populate a table from other (multiple) table columns
body: <p>I need to create a stored procedure transaction that will pull data from two tables and create a random integer to populate a third table (<code>dbo.patient_address</code>). </p>
  
  <p>I want the following, but I am having a hard time understanding how to script this as a stored procedure. </p>
  
  <p>dbo.address AddressID = dbo.patient_address AddressID,
  dbo.patient PatientID = dbo.patient_address PatientID,
  RAND is an integer between 1-3 (see dbo.addresstype) = dbo.patient_address AddressTypeID</p>
  
  <p>Below are create statements of all of these tables.</p>
  
  <p><strong>ADDRESS TABLE</strong> -  30000 rows in place (complete data set)</p>
  
  <pre><code>CREATE TABLE [dbo].[ADDRESS](
      [AddressID] [int] NOT NULL,
      [StreetAddress] [varchar](50) NULL,
      [City] [varchar](50) NULL,
      [State] [varchar](50) NULL,
      [PostalCode] [varchar](50) NULL,
   CONSTRAINT [ADDRESS_PK] PRIMARY KEY CLUSTERED 
  (
      [AddressID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p><strong>PATIENT TABLE</strong> - 30000 rows in place (complete data set) </p>
  
  <pre><code>CREATE TABLE [dbo].[PATIENT](
      [PatientID] [int] NOT NULL,
      [PatientFName] [varchar](50) NOT NULL,
      [PatientLName] [varchar](50) NOT NULL,
      [GenderID] [int] NOT NULL,
      [DateOfBirth] [date] NOT NULL,
   CONSTRAINT [PATIENT_PK] PRIMARY KEY CLUSTERED 
  (
      [PatientID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  </code></pre>
  
  <p><strong>ADDRESS TYPE</strong> - 3 choices (home, billing, work) – integer between 1 - 3</p>
  
  <pre><code>CREATE TABLE [dbo].[ADDRESS_TYPE](
      [AddressTypeID] [int] NOT NULL,
      [AddressTypeName] [varchar](10) NOT NULL,
      [AddressTypeDescr] [varchar](10) NULL,
   CONSTRAINT [ADDRESS_TYPE_PK] PRIMARY KEY CLUSTERED 
  (
      [AddressTypeID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  INSERT INTO [dbo].[ADDRESS_TYPE]
  ([AddressTypeID],[AddressTypeName],[AddressTypeDescr])
  Values ('1','Home','Home'), ('2','Bill','Billing'), ('3','Work','Work')
  </code></pre>
  
  <p>This is the final table that I want to populate from all of the above tables using a stored procedure transaction. </p>
  
  <pre><code>CREATE TABLE [dbo].[PATIENT_ADDRESS](
      [PatientID] [int] NOT NULL,
      [AddressID] [int] NOT NULL,
      [AddressTypeID] [int] NULL,
   CONSTRAINT [PATIENT_ADDRESS_PK] PRIMARY KEY CLUSTERED 
  (
      [PatientID] ASC,
      [AddressID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>