You are here:

MS SQL/Stored Procedure to enter multiple rows into a table

Advertisement


Question
Hello,

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:

USE [dermatology]
GO
/****** Object:  StoredProcedure [dbo].[patientSampleList]    Script Date: 02/10/2014 16:41:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[patientSampleList]
  -- Add the parameters for the stored procedure here
@paId INTEGER
  
AS
BEGIN
  -- 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
FROM patient_sample
INNER JOIN sample
ON patient_sample.sampleId = sample.sampleId
WHERE patientId = @paId
END

The user input @ variables are coming from a .net webpage.

Any help would be much appreciated

THank you

Answer
Hi TJ

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
GO

IF OBJECT_ID('uspInsertRows', 'P') IS NOT NULL
DROP PROCEDURE uspInsertRows
GO


CREATE TABLE TestTable
(
myId INT,
myName VARCHAR (10),
myDate DATETIME DEFAULT GETDATE()
)
GO


/*
EXEC uspInsertRows 7
SELECT * FROM TestTable
*/
CREATE PROCEDURE uspInsertRows (@RowCount INT)
AS
BEGIN
SET NOCOUNT ON

;WITH CTE
AS
(
SELECT 1 AS myId
UNION ALL
SELECT 2
)

, AllRows
AS
(
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)
FROM AllRows
WHERE RowNum <= @RowCount

END
GO  

MS SQL

All Answers


Answers by Expert:


Ask Experts

Volunteer


Mohit Nayyar

Expertise

Microsoft SQL Server: T-SQL development (stored procedures, triggers, functions), Database optimization, performance tuning, high availability (Clustering, Log Shipping, Mirroring, Replication), scalability, SQL Server migration (Sybase/Oracle to SQL Server), SSIS/DTS, Data Warehousing (Kimball Methodology), ETL (SSIS), Cube (SSAS), Reporting Services (SSRS), database modeling, database administration, Security implementation and typically anything related to Microsoft SQL Server.

Experience

With over 10 years of experience in Database Administration, Development, Business Intelligence solutions and managing enterprise level database solutions based on Microsoft SQL Server.
Publications
IT Magazine: ASP, SQL

Publications
IT Magazine: ASP, SQL
SQLServerCentral.com

Education/Credentials
MCA (Masters)

Awards and Honors
Brainbench Certified: RDBMS
MCTS: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCITP: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCDBA: SQL Server 2000
MCP: SQL Server 6.5

©2016 About.com. All rights reserved.