How to insert more rows into sql server table using stored procedure?

Question

How to insert more rows into sql server table using stored procedure?

Ashok-633347

SSC Enthusiast

Points: 112
More actions
April 14, 2008 at 4:23 am

#189098

Hi,
Good morning to all.
My table: User_Group_Map(UserID UNIQUEIDENTIFIER,GroupID UNIQUEIDENTIFIER)
Now, I want to write one stored procedure that can insert rows into the above table, but more number of rows at-once.
Means, the program should allow multiple insertions without the need to call the stored procedure from front-end more number of times.
Can anyone please help me on this...
Thanks in advance...
Ashok kumar.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Ashok

Where will the data you are hoping to insert come from? If you show us what you've tried so far, that should help us to see exactly what you're trying to do.

John

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 2

[font="Verdana"]Ashok, this can be done through using XML. What we do is, we build single XML string and we pass it to the SProc. Inside the SProc such string treated as table. Means you can build a string with the records you wants to insert into the table and can be pass to a SProc in a single call. And inside the SProc you can insert the records into the table one by one. Refer the below url, so that you can better idea.

http://weblogs.sqlteam.com/travisl/archive/2005/01/04/3931.aspx

Even don't forget to remove the XML document. Just add one more line ,which is not in this article, at the end: exec sp_xml_removedocument @xml_hndl OUTPUT

Let us know.

Mahesh [/font]

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 3

You don't need XML for this... you can just do an INSERT with the understanding that there is no real good way to return the UniqueIdentifiers to the GUI without using a Trigger.

INSERT INTO User_Group_Map

(UserID UNIQUEIDENTIFIER,GroupID UNIQUEIDENTIFIER)

SELECT NEWID() AS UserID,

NEWID() AS GroupID

If you do want to return the list to the GUI, then you will need to write a trigger... but that will still be faster than anything you can do with XML.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 4

[font="Verdana"]

Jeff Moden (4/14/2008)
You don't need XML for this... you can just do an INSERT with the understanding that there is no real good way to return the UniqueIdentifiers to the GUI without using a Trigger.
INSERT INTO User_Group_Map
(UserID UNIQUEIDENTIFIER,GroupID UNIQUEIDENTIFIER)
SELECT NEWID() AS UserID,
NEWID() AS GroupID
If you do want to return the list to the GUI, then you will need to write a trigger... but that will still be faster than anything you can do with XML.

Jeff, but Ashok needs to insert multiple records into a table with a single call to Store Procedure.

Mahesh[/font]

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 5

Heh... understood.... so, somebody give me an example of the input data...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dmbaker SSCertifiable Points: 5139 More actions · Answer 6

You don't need a trigger...

[font="Courier New"]DECLARE

@userid UNIQUEIDENTIFIER,

@GroupID UNIQUEIDENTIFIER

SET @user-id = NEWID()

SET @GroupID = NEWID()

INSERT INTO User_Group_Map (UserID, GroupID) VALUES (@UserID, @GroupID)[/font]

Wrap it in a stored proc and you can use output parameters to return new ID's to the GUI.

dmbaker SSCertifiable Points: 5139 More actions · Answer 7

...and, if you pass the data in as XML (or whatever other method you choose to pass in a chunk of data that you want to work on) then you could parse it and build a temp table/table var containing your new ID's then return that table to your GUI.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jeff Moden (4/14/2008)
You don't need XML for this... you can just do an INSERT with the understanding that there is no real good way to return the UniqueIdentifiers to the GUI without using a Trigger.
INSERT INTO User_Group_Map
(UserID UNIQUEIDENTIFIER,GroupID UNIQUEIDENTIFIER)
SELECT NEWID() AS UserID,
NEWID() AS GroupID
If you do want to return the list to the GUI, then you will need to write a trigger... but that will still be faster than anything you can do with XML.

Actually, in 2005, there is. Use the OUTPUT clause... It will allow you to return the inserted rows WITH the identifiers attached....OUTPUT uses the same logic and virtual tables (inserted and deleted) as the trigger does, so it keeps the results in scope with your operation (meaning you get back only the rows you affected).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 9

Dang... I keep forgetting about that... thanks for the reminder, Matt. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 10

-- For 2005 and later

Create Procedure InsertRows

@NumberOfRows tinyint

as begin

INSERT INTO User_Group_Map

(UserID UNIQUEIDENTIFIER,GroupID UNIQUEIDENTIFIER)

SELECT top (@NumberOfRows) NEWID() AS UserID,

NEWID() AS GroupID

from master.INFORMATION_SCHEMA.COLUMNS i1

CROSS JOIN master.INFORMATION_SCHEMA.COLUMNS i2;

end;

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Ripal Patel SSChasing Mays Points: 629 More actions · Answer 11

insert into sample_tmp

select 'Ripal',45,getdate() union all

select 'Patel',145,getdate() union all

select 'Rakesh',79,getdate() union all

select 'Mukesh',12,getdate() union all

select 'Bantu',55,getdate()

in above statement 'Ripal' is first row data for column name,45 is for age and getdate() for Column date,similarly for all row and combine them using union all.

U can add multiple row using XML also,