批量通过参数插入多个集合 [英] Bulk Insert multiple collections via parameters

问题描述

您好，



我正在开发一个应用程序，我想写一个已请求的日志数据。



目前我根据我已经传入我的程序的学生ID加载学生数据。



然后我尝试为学生加载子数据根据请求，如技能，爱好，语言，成绩等等。这些作为个人收藏在我的存储过程中传递。



为每个学生加载所有内容后我必须记录他们的学生ID和关于子数据的相应静态子数据标识符，例如技能的id为45，爱好​​的id为46等等。静态子标识符对所有学生都是相同的。



该表可以为该学生提供多个记录（根据所请求的内容并提供该记录），审计表如下所示：



表Audit_table

- Id

- Student_Id

- StudentBioI d $ / $




我想对我想写的代码提出一些建议。我传递了两个varrays，首先是学生标识符，其次是与该学生请求的子数据相关的标识符。



循环的约定是什么多个集合并适当地插入那个学生？



目前我只能让其中一个工作，即StudentId varray：

  CREATE  或 REPLACE  TYPE  studentID  IS  VARRAY（ 256 ） OF  NUMBER; 
  CREATE  或 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 ; 

我想填充StudentBioId对于那个学生也是如此，但我不确定如何为每个学生适当地做这个。



建议/例子将不胜感激。



亲切的问候，

解决方案

   -    VARIABLE DECLARATION  
  DECLARE   @ STUDENTIDS   VARCHAR （ 500 ）
  DECLARE   @ STUDENT_IDCOUNT   INT  
  DECLARE   @ STUDLOOPCOUNT   INT  
  DECLARE   @ S_I D   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 （< span class =code-digit> 20 ）
）
   -    INSERT #TEMP语句 
  INSERT   INTO   @ST UDTEMP   SELECT  *  FROM  DBO.SPLIT（ @STUDENTIDS ，' ，'）
 
   -    SELECT COUNT  
  SELECT   @ STUDENT_IDCOUNT  = COUNT（STUDENT_ID） FROM   @ STUDTEMP  
 
 
   -    INSERT MULTIPLE RECORDS  
 < span class =code-keyword> IF （ @ STUDENT_IDCOUNT >  0 ）
  BEGIN  
  SET   @ STUDLOOPCOUNT  =  1 ; 
  WHILE （ @ STUDLOOPCOUNT < =  @ STUDENT_IDCOUNT ）
  BEGIN  
 
   -   < span class =code-comment>选择你相应的学生价值 
  SELECT   @ S_ID  = STUDENT_ID  FROM   @ STUDTEMP   WHERE  ID =  @ STUDLOOPCOUNT  
  SELECT   @ S_BIO_ID  = STUDENTBIO_ID  FROM   @ STUDTEMP   WHERE  ID =  @ STUDLOOPCOUNT  
 
   -    INSER将值调整为AUDIT_TABLE表 
  INSERT   INTO  AUDIT_TABLE 
（
 STUDENT_ID，
 STUDENTBIOID 
）
 
  VALUES  
（
  @ S_ID ，
  @ S_BIO_ID  
 
）
  SET   @ STUDLOOPCOUNT  =  @ STUDLOOPCOUNT  +  1  
  END  
  END  

请按照给定的链接尝试以上溢出功能代码。 

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

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:

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,

解决方案

--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[^]

这篇关于批量通过参数插入多个集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！