In t-sql 2012, I want like to update 3 tables at the same time. I would like the lockid value be set in a table called lock by the unique proimary key identifier. I would then like to pass the lockid value to the tables called locker and lockercombination. The rows for the locker and lockercombination would be obtain from other tables by doing joins. Listed below are the ddls for the 3 tables I am referring to. Would you show me the sql on how to accomplish this goal?
CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[schoolID] [int] NOT NULL,
[number] varchar NOT NULL,
[serialNumber] varchar NULL,
[type] varchar NULL,
[locationID] [int] NULL,
[grade] varchar NULL,
[reserved] [bit] NULL,
[lockID] [int] NULL,
[share] [bit] NULL,
[lockType] varchar NULL,
CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
(
[lockerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Locker] WITH NOCHECK ADD CONSTRAINT [FK_Locker_Lock] FOREIGN KEY([lockID])
REFERENCES [dbo].[Lock] ([lockID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_Lock]
GO
ALTER TABLE [dbo].[Locker] WITH CHECK ADD CONSTRAINT [FK_Locker_LockerLocation] FOREIGN KEY([locationID])
REFERENCES [dbo].[LockerLocation] ([locationID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_LockerLocation]
GO
ALTER TABLE [dbo].[Locker] WITH NOCHECK ADD CONSTRAINT [FK_Locker_School] FOREIGN KEY([schoolID])
REFERENCES [dbo].[School] ([schoolID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_School]
GO
CREATE TABLE [dbo].[Lock](
[lockID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[schoolID] [int] NOT NULL,
[serialNumber] varchar NULL,
[type] varchar NULL,
[comboSeq] [tinyint] NOT NULL,
CONSTRAINT [PK_Lock] PRIMARY KEY NONCLUSTERED
(
[lockID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Lock] WITH NOCHECK ADD CONSTRAINT [FK_Lock_School] FOREIGN KEY([schoolID])
REFERENCES [dbo].[School] ([schoolID])
GO
ALTER TABLE [dbo].[Lock] CHECK CONSTRAINT [FK_Lock_School]
GO
CREATE TABLE [dbo].[LockCombination](
[comboID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[lockID] [int] NOT NULL,
[seq] [tinyint] NOT NULL,
[combo] varchar NOT NULL,
CONSTRAINT [PK_LockCombination] PRIMARY KEY NONCLUSTERED
(
[comboID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]