MS SQL/Stored Procedure to enter multiple rows into a table
I need to write a stored procedure that would, based on an existing ID would then create several more IDs (the number of entried would be derived from a user input) and insert them into a database table.
So in my case, I am creating a application that stores medical samples based on their IDs that I will call sampleID. The sampleid is usually something like 'T100050'.
So what happens to these samples is that they are split into aliquots which are then used in experiments. So if the user goes to the sample page, there will be a button that says 'Create Aliquots'. Once pressed it will ask the user for a number between 1 and 10 (which represent the number of aliquots the user has created). The what I want the stored procedure to do is to take this user entered number and the sampleID and create a number of aliquotIDs dependent on what number the user entered. the aliquotIDs should be the sampleID-1, sampleID-2, sampleID-3 etc.. up until the user entered number. They are then inserted into a table called 'aliquot' along with the original sampleID.
I know that doing this requires a For Loop that starts from 1 to i where i is the user entered number. But I have no idea how to do this or the string concatenation for the aliquotIDs in SQL Server.
IF it helps this is an example of how I've been creating Stored Procedures:
/****** Object: StoredProcedure [dbo].[patientSampleList] Script Date: 02/10/2014 16:41:49 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
CREATE PROCEDURE [dbo].[patientSampleList]
-- Add the parameters for the stored procedure here
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT patient_sample.patientId, patient_sample.sampleId, patient_sample.patient_studyId, patient_sample.studycode,
patient_sample.visit_type, sample.sampleType, sample.sample_taken_date
INNER JOIN sample
ON patient_sample.sampleId = sample.sampleId
WHERE patientId = @paId
The user input @ variables are coming from a .net webpage.
Any help would be much appreciated
Please use following code, I create a sample table and store procedure for you as sample. Please feel free to modify as you like.
IF OBJECT_ID('TestTable', 'U') IS NOT NULL
DROP TABLE TestTable
IF OBJECT_ID('uspInsertRows', 'P') IS NOT NULL
DROP PROCEDURE uspInsertRows
CREATE TABLE TestTable
myName VARCHAR (10),
myDate DATETIME DEFAULT GETDATE()
EXEC uspInsertRows 7
SELECT * FROM TestTable
CREATE PROCEDURE uspInsertRows (@RowCount INT)
SET NOCOUNT ON
SELECT 1 AS myId
SELECT ROW_NUMBER() OVER (ORDER BY a.myId) AS RowNum
FROM CTE A
CROSS JOIN CTE B
CROSS JOIN CTE C
CROSS JOIN CTE D
CROSS JOIN CTE E
CROSS JOIN CTE F
CROSS JOIN CTE G
CROSS JOIN CTE H
INSERT INTO TestTable (myId, myName)
SELECT RowNum, 'Mohit ' + CAST (RowNum AS VARCHAR)
WHERE RowNum <= @RowCount