March 31, 2009 at 4:48 pm
Hello,
My name is Greg. I am new to T-SQL as our SQL guru quit for a better job two week ago and I am trying to pick up the pieces until we can find another one. I am an ASP.NET developer and am trying to create a stored procedure that will allow me to insert information into two tables at the same time. Table one has account information (CustInfo) and table 2 has telephone numbers that I could assign to these customers (DIDs).
CustInfo (Table 1)
CustID int(identity is yes and this is the PK for this table)
C_Namevarchar(50)Customer Name
Etc
Etc
RoutingDID
Etc
Etc
DIDs (Table2)
DID_ID(identity is yes and this is the PK for this table)
CustIDint(FK)
DIDvarchar(50)
Assignedvarchar(50)Needs to be the RoutingDID from CustInfo(Table1)
Her e is my Stored Procedure, which works for inserting all the info into CustInfo(Table 1) but I am trying to add code that will allow me to insert the RoutingDID value into the DIDs table2 at the same time.
CREATE PROCEDURE [dbo].[usp_NewCustomer]
@C_Name varchar(50),
@C_Address1 varchar(50),
@C_Address2 varchar(50),
@C_City varchar(50),
@C_State varchar(50),
@C_Zip varchar(50),
@C_DID varchar(50),
@C_email varchar(50),
@T_Name varchar(50),
@T_DID varchar(50),
@T_email varchar(50),
@MACAddr varchar(50),
@RoutingDID varchar(50),
@DID1 varchar(50),
@DID2 varchar(50),
@DID3 varchar(50),
@DID4 varchar(50),
@DID5 varchar(50),
@DID6 varchar(50),
@DID7 varchar(50),
@DID8 varchar(50),
@DID9 varchar(50),
@DID10 varchar(50),
@PWD varchar(50),
@CallPaths varchar(50),
@CallerID varchar(50)
AS
INSERT INTO CustInfo
(C_Name,
C_Address1,
RoutingDID,
PWD,
CallPaths,
CallerID)
VALUES
(@C_Name,
@C_Address1,
@RoutingDID,
@PWD,
@CallPaths,
@CallerID)
GO
INSERT INTO DIDs
(Assigned)
Values
(RoutingDID)
WHERE CustID =@CustID
Thank to everyone who assists me.
Greg
March 31, 2009 at 8:13 pm
Hello Greg,
What are all of the columns that you want to populate in the DIDs table, and which parmeters do you want to assign to the columns in the DIDs table.
Thx,
Mike
March 31, 2009 at 8:14 pm
Hello Greg,
What are all of the columns that you want to populate in the DIDs table, and which parameters do you want to assign to the columns in the DIDs table.
Thx,
Mike
March 31, 2009 at 11:55 pm
Hi Greg
You have to use the SCOPE_IDENTITY() function to get the currently inserted/created CustID. Then you can insert the data into your DID table.
USE tempdb
GO
IF (OBJECT_ID('Did') IS NOT NULL)
DROP TABLE Did
GO
IF (OBJECT_ID('Cust') IS NOT NULL)
DROP TABLE Cust
GO
CREATE TABLE Cust
(
Id INT IDENTITY,
Name VARCHAR(100),
RoutingDid VARCHAR(100),
PRIMARY KEY CLUSTERED (Id)
)
GO
CREATE TABLE Did
(
Id INT IDENTITY,
DID VARCHAR(100),
CustId INT,
PRIMARY KEY CLUSTERED (Did),
FOREIGN KEY (CustId) REFERENCES Cust (Id)
)
GO
IF (OBJECT_ID('usp_Cust_Insert') IS NOT NULL)
DROP PROCEDURE usp_Cust_Insert
GO
CREATE PROCEDURE usp_Cust_Insert
@Name VARCHAR(100),
@RoutingDID VARCHAR(100),
@Did1 VARCHAR(100),
@Did2 VARCHAR(100)
AS
DECLARE @CustId INT
INSERT INTO Cust
SELECT @Name, @RoutingDID
SELECT @CustId = SCOPE_IDENTITY()
INSERT INTO Did
SELECT @RoutingDID, @CustId
UNION SELECT @Did1, @CustId
UNION SELECT @Did2, @CustId
GO
EXECUTE usp_Cust_Insert 'MyCust', 'RoutingDID', 'DID1', 'DID2'
SELECT * FROM Cust
SELECT * FROM Did
Greets
Flo
April 1, 2009 at 8:22 am
Hi
In addition to the "Florian Reischl" store procedure its good to use TRANSACTIONS also.
If one INSERT fails then dont proceed with the next INSERT.
Thanks -- Vijaya Kadiyala
April 1, 2009 at 4:13 pm
Hi,
I just want to insert a 'Yes' in the Assigned Column of the DIDs table.
April 2, 2009 at 3:36 am
gsmccoy (4/1/2009)
Hi,I just want to insert a 'Yes' in the Assigned Column of the DIDs table.
Into your CustInfo table or the DIDs table? What is the problem?
Greets
Flo
April 2, 2009 at 3:38 am
Vijaya Kadiyala (4/1/2009)
HiIn addition to the "Florian Reischl" store procedure its good to use TRANSACTIONS also.
If one INSERT fails then dont proceed with the next INSERT.
Hi Vijaya
Sure it should be included into a TRY-CATCH block with transaction handling. Was just a sample 😉
Greets
Flo
April 2, 2009 at 9:42 am
Hi Flo
Thanks for adding Try and Catch 😉 ..I hope from next time onwrods i will Catch my posting exception 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply