In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?

UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
WHERE LKC.seq = 2
**Note the tables of [LockerPopulation] and TST.dbo.School are only used for the update statement to obtain the data.

A normal select statement looks like the following:
select * from  TST.dbo.Locker LKR 
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
where  LKR.number in (000,001,1237)

In case you need the ddl statements for the tables affected here are the ddl statements:

CREATE TABLE [dbo].[Locker](
 [lockerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [schoolID] [int] NOT NULL,
 [number] [varchar](10) NOT NULL,
 [serialNumber] [varchar](20) NULL,
 [type] [varchar](3) NULL,
 [locationID] [int] NULL,
 [grade] [varchar](4) NULL,
 [reserved] [bit] NULL,
 [lockID] [int] NULL,
 [share] [bit] NULL,
 [lockType] [varchar](3) 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
-------------------
CREATE TABLE [dbo].[Lock](
 [lockID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [schoolID] [int] NOT NULL,
 [serialNumber] [varchar](20) NULL,
 [type] [varchar](3) 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
--------------------
CREATE TABLE [dbo].[LockCombination](
 [comboID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [lockID] [int] NOT NULL,
 [seq] [tinyint] NOT NULL,
 [combo] [varchar](8) 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]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[LockCombination]  WITH NOCHECK ADD  CONSTRAINT [FK_LockCombination_Lock] FOREIGN KEY([lockID])
REFERENCES [dbo].[Lock] ([lockID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[LockCombination] CHECK CONSTRAINT [FK_LockCombination_Lock]
GO

• Edited by wendy elizabeth Wednesday, July 29, 2015 4:22 AM

You mentioned the following: "That being the case you should be using a number table and do a left join with it based on seq value to get missing records. '. Can you show me the sql on how to accomplish this goal you are referring to?

There are a few records that that have sequence numbers 2 to through 5. Thus can you show me the sql that can be used to keep the existing rows that have sequence numbers 2 through 5, while adding the rows that are missing for the other records?

Try

select *
from  TST.dbo.Locker LKR  
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
CROSS APPLY (values(1),(2),(3),(4),(5)) as Numbers(number)
where  LKR.number in (000,001,1237)

This will produce 5 rows for every row in the original select statement.

The way I read it, they don't want five rows for every row, they want 5 rows per... lockid...

select *
from TST.dbo.Lock LK 
  CROSS APPLY (values (1),(2),(3),(4),(5)) x(seq)
  LEFT OUTER JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID and x.seq = LKC.seq