Bulk Insert multiple collections via parameters

Question

0.00/5 (No votes)

See more:

Hi there,

I am developing an application where i would like to write a log data that has been requested.

Currently i load student data based the student ids i have passed into my procedure.

I then attempt to load sub data for the student based on the request e.g Skills, Hobbies, Languages, Grades, etc etc. These are passed in my stored proc as individual collections.

After everything has been loaded for each student I have to log their student id and the corresponding static sub data identifier regarding the sub data e.g Skills has an id of 45, Hobbies has an id of 46 etc etc. The static sub identifier is the same for all students.

The table can have multiple records for that student(based on whats requested and providing it exists), the audit table looks like this:

Table Audit_table
- Id
- Student_Id
- StudentBioId

I would like some advice on the code i am trying to write. I am passing in two varrays, firstly the student identifiers and secondly identifiers relating to the sub data that has been requested for that student.

What is the convention for looping through multiple collections and inserting them appropriately against that student?

Currently i can only get one of them to work which is the StudentId varray:

SQL

CREATE OR REPLACE TYPE studentIDs IS VARRAY (256) OF NUMBER;
CREATE OR REPLACE TYPE studentBio IS VARRAY (256) OF NUMBER;

BEGIN
   FORALL i IN 1..studentIDs.COUNT
      INSERT INTO audit_table(Id, Student_id, StudentBioId) 
      VALUES  (-1, studentIDS(i), null);
END;

I would like to populate the StudentBioId for that student as well but i am unsure of how to do this appropriately for each student.

Advice/Examples would be much appreciated.

Kind Regards,

Posted 20-Jun-15 1:46am

Eagle32

Add a Solution

Comments

RossMW 21-Jun-15 23:16pm

If I understand correctly you are wanting to loop thru studentbiod array for each studentid. If this is the case then you can have a studentBiod loop inside the studentid loop.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

anand vemuri · Answer 1 · 2015-06-21T23:14:00

SQL

--VARIABLE DECLARATION
DECLARE @STUDENTIDS VARCHAR(500)
DECLARE @STUDENT_IDCOUNT INT
DECLARE @STUDLOOPCOUNT INT
DECLARE @S_ID INT
DECLARE @S_BIO_ID VARCHAR(500) 

--EG
--SELECT @STUDENTIDS = '1,2,3,4,5,6'

IF (ISNULL(@STUDENTIDS, '') <> '' )
BEGIN
--TEMP TABLE
DECLARE @STUDTEMP TABLE
(
	ID INT IDENTITY(1,1) NOT NULL,
	STUDENT_ID VARCHAR(20),
	STUDENTBIO_ID VARCHAR(20)
) 
--INSERT #TEMP STATEMENTS	
INSERT INTO @STUDTEMP SELECT * FROM DBO.SPLIT(@STUDENTIDS, ',')
 
--SELECT COUNT
SELECT @STUDENT_IDCOUNT = COUNT(STUDENT_ID) FROM @STUDTEMP
 
 
--INSERT MULTIPLE RECORDS
IF(@STUDENT_IDCOUNT > 0)
BEGIN
	SET @STUDLOOPCOUNT = 1;
	WHILE (@STUDLOOPCOUNT <= @STUDENT_IDCOUNT)
	BEGIN
		
		--SELECT YOUR  CORRESPONDING STUDENT VALUES
		SELECT @S_ID     = STUDENT_ID FROM @STUDTEMP WHERE ID = @STUDLOOPCOUNT
		SELECT @S_BIO_ID = STUDENTBIO_ID FROM @STUDTEMP WHERE ID = @STUDLOOPCOUNT

		-- INSERTING VALUES INTO AUDIT_TABLE TABLE
		INSERT INTO AUDIT_TABLE 
		(	
			STUDENT_ID,
			STUDENTBIOID
		)

		VALUES 
		(	
			@S_ID,
			@S_BIO_ID
				 
		)
		SET  @STUDLOOPCOUNT = @STUDLOOPCOUNT + 1
	END	
END

Please try above code for spilt fuction follow the given link.

http://www.aspdotnet-suresh.com/2013/07/sql-server-split-function-example-in.html[^]