May 7, 2009 at 5:00 pm
Hi, I am using SQL Server 2005. Through store procedure and ASP.Net web pages, I am doing all CRUD operations. Also I am using individual ASP.Net forms to update individual tables. It is time consuming method and referring id column values to map in another table making me mad.
What I want to achieve is to update all related tables (many-to-many relationhip) by using one ASP.Net form. I am aware that this forum is for SQL server issues and queries not for ASP.Net. But you also know that ASP.Net form is just for user interface for underlying CRUD sql queries with parameters.
Example scenario: (copied from somebody who asked the same question and but not answered with examples.)
Table 1
Main ID (autonumber)
field1
field2
field3
Table 2
MainID
HabitatID
Table 3
Habitat ID (autonumber)
field4
field5
field6
What I want to to do in my form:
User enters records in table 1. Auto number is assigned, say '5'. User enters records in table 3. How do I ensure the records the user has entered in table 1 and 2 are linked?
How can I make the MainID in table 2 the same the MainID in table 1?
What should look like after user has entered all there records
Table 1
Main ID (autonumber) =5
field1
field2
field3
Table 2
MainID =5
HabitatID =19
Table 3
Habitat ID (autonumber) = 19
field4
field5
field6
Somebody please help me by giving the SQL queries to update the mapping table (table 2) while inserting table 1 & table 3 automatically.
May 8, 2009 at 3:30 am
Hi
Use SCOPE_IDENTITY() after inserting the record into Table1 and Table3 to get the new identity values (your autonumber). After this INSERT both IDs into your Table2. Sure your Table2 needs (by guideline) foreign keys to the other tables.
CREATE TABLE T1
(
Id INT NOT NULL IDENTITY,
Txt VARCHAR(30),
PRIMARY KEY CLUSTERED
(Id)
)
GO
CREATE TABLE T3
(
Id INT NOT NULL IDENTITY,
Txt VARCHAR(30),
PRIMARY KEY CLUSTERED
(Id)
)
GO
CREATE TABLE T2
(
T1Id INT NOT NULL,
T3Id INT NOT NULL,
FOREIGN KEY
(T1Id)
REFERENCES T1
(Id),
FOREIGN KEY
(T3Id)
REFERENCES T3
(Id)
)
GO
DECLARE @T1Id INT
DECLARE @T2Id INT
INSERT INTO T1
SELECT 'foo'
SELECT @T1Id = SCOPE_IDENTITY()
INSERT INTO T3
SELECT 'bar'
SELECT @T2Id = SCOPE_IDENTITY()
INSERT INTO T2
SELECT @T1Id, @T2Id
SELECT *
FROM T1
JOIN T2 ON T1.Id = T2.T1Id
JOIN T3 ON T2.T3Id = T3.Id
GO
DROP TABLE T2
DROP TABLE T3
DROP TABLE T1
Greets
Flo
May 8, 2009 at 4:29 am
In case if you're inserting multiple rows then you may want to have a look at the OUTPUT Clause in Books Online
DECLARE @T1Id INT
CREATE TABLE T1
(
Id INT NOT NULL IDENTITY,
Txt VARCHAR(30),
PRIMARY KEY CLUSTERED
(Id)
)
INSERT INTO T1
OUTPUT INSERTED.Id INTO @T1Id
SELECT 'foo'
--Ramesh
May 10, 2009 at 6:10 pm
Thanks for Both Florian Reischl and Ramesh for your SQL codes.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply