Create a stored procedure to populate a table from other (multiple) table columns

Question

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 (dbo.patient_address).

I want the following, but I am having a hard time understanding how to script this as a stored procedure.

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

Below are create statements of all of these tables.

ADDRESS TABLE - 30000 rows in place (complete data set)

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]

PATIENT TABLE - 30000 rows in place (complete data set)

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

ADDRESS TYPE - 3 choices (home, billing, work) – integer between 1 - 3

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')

This is the final table that I want to populate from all of the above tables using a stored procedure transaction.

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]

Please take the time to understand the difference between a stored procedure and a transaction. They are two different things. The stored procedure can be executed within a transaction, or there can be a transaction within the stored procedure. Please do not use all caps in your question text (non-code/sql), it comes across as yelling to the reader. — Ryan Gates, May 31 '13 at 15:13
***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... — marc_s, May 31 '13 at 15:15
At first glance this looks like a simple `INSERT...SELECT...` query; what have you tried so far? And you might want to give more information about the "random" address type that you want to have. Are you generating test data? Do you just want a random value between 1 and 3 for the AddressTypeID? Have you seen [this question](http://stackoverflow.com/questions/7878287/generate-random-int-value-from-3-to-6)? — Pondlife, May 31 '13 at 21:38

score 0 · Answer 1 · answered Jun 03 '13 at 11:35

Try this:

INSERT INTO PATIENT_ADDRESS
SELECT PatientID, AddressID, 
    CONVERT(INT,(ABS(CHECKSUM(NEWID())/2148000000.))*3)+1 AS AddressTypeID
FROM (
    SELECT PatientID, ROW_NUMBER() OVER (ORDER BY PatientID) AS RowNum 
    FROM dbo.PATIENT
) x
INNER JOIN (
    SELECT AddressID, ROW_NUMBER() OVER (ORDER BY AddressID) AS RowNum 
    FROM dbo.ADDRESS
) y ON x.RowNum = y.RowNum

Create a stored procedure to populate a table from other (multiple) table columns

1 Answers1