Create a SP to Insert Data into Multiple Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Create a SP to Insert Data into Multiple Tables

Author

Topic

StephanieJones
Starting Member

12 Posts

Posted - 2003-06-18 : 11:14:53

I really need help in creating a stored procedure that will allow me to insert data from one form into more than one table (namely 2 tables). I have never worked with stored procedures and I am a little bit anxious about it. I am developing a web form using ASP/VBScript (through Dreamweaver MX) to create this form and Ineed the data from the form to go into two tables, 1)tblSamples and, 2)tblGSGSamples.

Can someone steer me on the correct path?

Thanks!
S

Stephanie Jones

Nazim
A custom title

1408 Posts

Posted - 2003-06-18 : 11:52:39

create copytable_Sp @param1 varchar(20),@param2 varchar(20)
as
set nocount on
insert into tblSamples(field1,field2) values(@param1, @param2)
insert into tblGSGSamples(field1,field2) values(@param1,@param2)
set nocount off
go

or Alternatively you can create a trigger on insert to add a new record to tblGSGSamples when a record is added to tblSamples

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-06-18 : 12:00:17

quote:

create proc sprInsertData
@FormValue1 varchar(20),
@FormValue2 varchar(20)
as
set nocount on
insert into tblSamples(FieldName1) values (@FormValue1)
insert into tblGSGSamples(FieldName2) values (@FormValue2)

Above is a sample stored procedure that will insert values into two different tables. The "set nocount on" is optional but suggested so you don't have to worry about the second result set being sent back to ASP. You can also add "begin/commit tran" to ensure that either both or neither as opposed to either or of the inserts happen, but if you do you should also check @@Error for failure and I didnt want to overly complicate the example.

hth,
Justin

-Edited to fix the bug with the [ code ] tags, to lazy find actual code, just switched it to a quote

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"

Edited by - justinbigelow on 06/18/2003 12:03:18

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-06-18 : 12:01:29

Shoot! Nazim beat me to the punch! ah well.

Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-06-18 : 14:06:31

One thing to keep in mind Ms. Jones is that a stored procedure is not limited to a specific action.

Within the same stored procedure you can insert, update, delete or select as much data as you need to.

The syntax is usually quite simple. It is really just your standard SQL statements wrapped around a CREATE PROCEDURE line.

Here is a relatively simple stored procedure:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qryPerson_EmailsIns]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[qryPerson_EmailsIns]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/**********************************************************************************************
Query Name : qryPerson_EmailsIns
Created By : AJM
Date : 02/26/2002
Description : Insert script for tblPerson_Emails
***********************************************************************************************/

CREATE PROCEDURE qryPerson_EmailsIns
@intUserId INT = -1,
@intPersonID int,
@intEmailID int,
@intSort int = 0,
@intObsolete int = 0,
@txtNote nvarchar(100) = NULL,
@intPrimary tinyint = NULL,
@dtiStart datetime = NULL,
@dtiEnd datetime = NULL

AS

INSERT INTO tblPerson_Emails
VALUES (
@intPersonID,
@intEmailID,
@intSort,
@intObsolete,
@txtNote,
@intPrimary,
@dtiStart,
@dtiEnd
)
RETURN @@IDENTITY

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[qryPerson_EmailsIns] TO [Users]
GO

All this thing does is accept a list of parameters. That is all those @ things. Some of them have "= 0" or something, that means they are optional, in other words, the stored procedure does not require a value for that particular parameter.

Then it simply inserts those values into tblPerson_Emails. And returns the primary key.

I could modify it to push that data off to another table as well. Or pull that record back out and query against another table for an insert or update statement.

Hope this helps!

Aj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources