T-sql 2012 update primary key value into column of the second table

Transact-SQL
1

In a t-sql 2012, I want to place the new lockid values obtain from the first insert statement (for the lock table) indiviudally into the second insert statement (locker table in the in the column called lockid).
See sql below.
insert into [dbo].[Lock]
SELECT 134,c.serialNumber,c.type,2
FROM .[dbo].[Lock] c
join [dbo].[Locker] locker on locker.lockID = c.lockID
AND locker.locationID between 1552 and 1555
order by locationID, number

insert into [dbo].[Locker]
SELECT
134
,[number]
,[serialNumber]
,[type]
,[locationID]
,[grade]
,[reserved]
,[lockID] --want new values from lock table
,[share]
,[lockType]
FROM [dbo].[Locker]
where [schoolID] = 12 and locationID between 1552 and 1555

I would like to use something like the sql listed below to insert the lockid values from
the lock table into the locker table for the column called lockid.

with cte as
(
Select SchoolID, lockID,
Row_Number() Over(Partition by SchoolID order by LockerID) RowNum
From Locker
)
Update cte set
lockID = RowNum + 117173
where SchoolID = 134;

The problem is the lockid column of the locker table has a foreign key constraint on the lockid column of the lock tabe.
There is nothing setup for 'cascade updates'.

Thus could you show me the sql on how I can do the insert on the locker table with obtaining the lockid values from the lock table without needing to do a drop or disable index?

If this is not possible, would you show me the sql on how to accomplish my goals using the disable, drop index, and other whatever way you think is a good solution?

Here is a definition of the 2 tables:

USE [TEST]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

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

USE [TEST]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
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

2

you can't. or, you can insert the new lockid into the lock table first.

3

+1, nice...... you have the inserted, deleted and instead of... instead of and reverse it back in, very smart

4

Reply to the quote: "you can't. or, you can insert the new lockid into the lock table first.". What do you mean by this?
It makes no difference to mean what table is created first. However I want all the new lockids from the lock table to be inserted into the locker table. Would you show me the sql on how to accomplish this goal?

5

What I mean is this:

It appears that you want to insert rows into the locker table that have lock ids that are not in the lock table. If you could, that would violate the referential integrity guaranteed by the foreign key constraint. However, if you want to do it the other way, well that's fine!

I was reacting to this query in your original post:

with cte as
(
Select SchoolID, lockID, 
Row_Number() Over(Partition by SchoolID order by LockerID) RowNum
From Locker
)
Update cte set
lockID = RowNum + 117173
where SchoolID = 134;

Now, this would cause a problem for row number 42, e.g. if lockid 117215 was not already in the lock table. So tell me, what is the business purpose of "+ 117173"?

To get the lockids from the lock table and insert into the locker table:

insert into lockertable (lockid)
select lockid from locktable
where lockid not in (select lockid from lockertable)  --> optional if you want unique lockids in the locker table
6

The 117173 value was a lockid value I had created in a temporary version of the lock table I was working with at the time.

7

OK .. so the little insert I posted is one way to get lockids from the lock table to the locker table. Will that do it?

8

Here is an example of what I am trying to do in the sql:
insert into [dbo].[Lock]
SELECT 134,c.serialNumber,c.type,2
FROM [dbo].[Lock] c
join [dbo].[Locker] locker on locker.lockID = c.lockID
AND locker.locationID between 1552 and 1555
order by locationID, number

insert into[dbo].[Locker]
SELECT
134
,[number]
,[serialNumber]
,[type]
,[locationID]
,[grade]
,[reserved]
,[lockID] = (select lockID from [dbo].[Lock] where lockID not in (select lockID from[dbo].[Locker]))
,[share]
,[lockType]
FROM [dbo].[Locker]
where [schoolID] = 12 and locationID between 1552 and 1555

I am basically trying to obtain specific existing rows for shoolid=12 and insert those rows for schoolid=134 into the locker table. I then also want to add the new lockids for the lock table that were added for schooled=134.

9

then something more like:

update locker
set locker.lockid = lock.lockid
from locker
join lock 
on locker.schoolid = lock.schoolid
and locker.locationid = lock.locationid
where locker.schoolid = 12 and locationid between .....
10

What you are suggesting is a good idea but the lockid value in the locker table is a foreign key value obtained from the lock table. Thus I was wondering if I needed to drop or disable the "ALTER TABLE [dbo].[Locker] WITH NOCHECK ADD CONSTRAINT [FK_Locker_Lock] FOREIGN KEY([lockID])" values?

Basically what is a way that I can accomplish my goal?

11

To do this:

Use an Insert instead of update

insert into lockertable (lockid) -- and other columns you want
select lockid from locktable    -- and other columns you want
where lockid not in (select lockid from lockertable)
1 Like