problem with sql table updation

Question

I have 2 tables

CREATE TABLE PODRAS_MS.tbl_FieldWorkers
(
    [FWInsOnServerID]   INT             NOT NULL IDENTITY(1,1),
    [BaseStationID]     INT             NOT NULL,
    [RefID]         INT                     NOT NULL,
    [DisSubInsID]       INT             NOT NULL,   
    [FieldWorkerID]     CHAR(7)                 NOT NULL,
    [LastRecDate]       DATETIME                NOT NULL,
)
ALTER TABLE PODRAS_MS.tbl_FieldWorkers ADD CONSTRAINT PK_FieldWorkers_FWInsOnServerID PRIMARY KEY([FWInsOnServerID])
ALTER TABLE PODRAS_MS.tbl_FieldWorkers ADD CONSTRAINT FK_FieldWorkers_DisSubInsID FOREIGN KEY([DisSubInsID]) REFERENCES PODRAS_MS.tbl_DisasterSubInstances([SubInsID]) ON UPDATE CASCADE ON DELETE NO ACTION
ALTER TABLE PODRAS_MS.tbl_FieldWorkers ADD CONSTRAINT FK_FieldWorkers_BaseStationID FOREIGN KEY([BaseStationID]) REFERENCES PODRAS_MS.tbl_BaseStations([BaseStationID]) ON UPDATE CASCADE ON DELETE NO ACTION
ALTER TABLE PODRAS_MS.tbl_FieldWorkers ADD CONSTRAINT DF_FieldWorkers_LastRecDate DEFAULT(GETDATE()) FOR [LastRecDate]
GO

CREATE TABLE PODRAS_MS.tbl_FieldWorkerNodeGPSLocations
(
    [FWNStatID]         INT         NOT NULL IDENTITY(1,1),
    [FWInsOnServerID]           INT         NOT NULL,
    [Latitude]          DECIMAL(20,17)          NOT NULL,
    [Longitude]         DECIMAL(20,17)          NOT NULL,
    [UpdateOn]          DATETIME        NOT NULL,
)
ALTER TABLE PODRAS_MS.tbl_FieldWorkerNodeGPSLocations ADD CONSTRAINT PK_FieldWorkerNodeGPSLocations_FWNStatID PRIMARY KEY([FWNStatID])
ALTER TABLE PODRAS_MS.tbl_FieldWorkerNodeGPSLocations ADD CONSTRAINT FK_FieldWorkerNodeGPSLocations_FWInsOnServerID FOREIGN KEY([FWInsOnServerID]) REFERENCES PODRAS_MS.tbl_FieldWorkers([FWInsOnServerID]) ON UPDATE CASCADE ON DELETE NO ACTION
ALTER TABLE PODRAS_MS.tbl_FieldWorkerNodeGPSLocations ADD CONSTRAINT DK_FieldWorkerNodeGPSLocations_UpdateOn DEFAULT(GETDATE()) FOR [UpdateOn]
GO

Both tables are updated through a webservice in the 1st table all the fields can be inserted through the web service but in the second table only data for [Latitude],[Longitude],[UpdateOn] fields comes through the webservice.so my problem is how can i insert the values to [FWInsOnServerID] field since its not comes through the webservice and its a reference for the 1st table???

Answer 1

If I understand you correctly, the insert in the second table is dependant on the result of the insert in your first table?

In this case, you could simply return the generted ID of the first table and use that result to insert into the second table.

Something like (if you're using Stored Procedures).

SELECT SCOPE_IDENTITY() 

at the end of your stored procedure. And then in your .NET code which handles the database code, use that number to do the second insert.

Answer 2

You could do it all in a single stored procedure like this:

CREATE PROCEDURE PODRAS_MS.insert_FieldWorker()
   @BaseStationID INT,
   @RefID INT,
   @DisSubInsID INT,   
   @FieldWorkerID CHAR(7),
   @Latitude DECIMAL(20,17),
   @Longitude DECIMAL(20,17)
AS
BEGIN 

     INSERT INTO 
     PODRAS_MS.tbl_FieldWorkers 
     ([BaseStationID], [RefID], [DisSubInsID], [FieldWorkerID])
     VALUES (@BaseStationID, @RefID, @DisSubInsID, @FieldWorkerID)

     DECLARE @FWInsOnServerID INT
     SELECT @FWInsOnServerID = SCOPE_IDENTITY()

     INSERT INTO PODRAS_MS.tbl_FieldWorkerNodeGPSLocations 
     ([FWInsOnServerID], [Latitude], [Longitude])
     VALUES (@FWInsOnServerID, @Latitude, @Longitude)

END

You could then select the records from the same stored procedure, but it is more common to separate this out into another stored proc.

EDIT: use an output parameter

CREATE PROCEDURE PODRAS_MS.insert_FieldWorker()
   @BaseStationID INT,
   @RefID INT,
   @DisSubInsID INT,   
   @FieldWorkerID CHAR(7),
   @FWInsOnServerID INT output
AS
BEGIN 

     INSERT INTO 
     PODRAS_MS.tbl_FieldWorkers 
     ([BaseStationID], [RefID], [DisSubInsID], [FieldWorkerID])
     VALUES (@BaseStationID, @RefID, @DisSubInsID, @FieldWorkerID)

     SELECT @FWInsOnServerID = SCOPE_IDENTITY()

END