SQL Server Performance Forum – Threads Archive

inserting multiple values using proc.

I have procedures, which calls many sub procs. One of my sub procs, does a simple insert into one of the tables say students for eg.
CREATE TABLE (studentid varchar(30),studentname varchar(30),studentsub varchar(30),studentsubid varchar(30)) From my main proc I am inserting multiple values into this table through the sub proc called spstudentinsert, EXEC spstudentinsert @studentid,@studentname,’ENG’,@eng
EXEC spstudentinsert @studentid,@studentname,’MAT’,@mat
EXEC spstudentinsert @studentid,@studentname,’HIS’,@his
EXEC spstudentinsert @studentid,@studentname,’CIV’,@civ
EXEC spstudentinsert @studentid,@studentname,’GEO’,@geo
EXEC spstudentinsert @studentid,@studentname,’PHY’,@phy
EXEC spstudentinsert @studentid,@studentname,’CHE’,@che
I am harcoding values for studentsub as this is constant and will not be passed as a variable. Now my question is Is there any way I can do a bulk insert of these values without having to call the sub procs so many times. In the actual proc, there are several values to be inserted. The sub proc is very simple and is just a ‘insert into’…’values’,,,,,type of proc.
Any help is appreciated. Thanks, StarWarsBigBang

---

if you have the values you want to insert in a flat file such as a csv, you can use bcp.exe or BULK INSERT to insert them. www.elsasoft.org

---

Hi,
As you have mentioned,
the values for studentsub is changing in every insert which you have hardcoded so you need to fire multiple inserts which can be combined using multiple selects and taking UNION ALL. consider this:
INSERT INTO Your_table
SELECT @studentid,@studentname,’ENG’,@eng
UNION ALL
SELECT @studentid,@studentname,’MAT’,@mat
UNION ALL
SELECT @studentid,@studentname,’HIS’,@his
UNION ALL
SELECT @studentid,@studentname,’CIV’,@civ
and so on…

---

You can pass as an xml string if you want to pass multiple records to insert. Or use one of the split function to parse the string input into array table and manipulate from there. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

]]>