October 11, 2016 at 11:18 am
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 [OPS].[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](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
-------------
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](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
SET ANSI_PADDING OFF
GO
October 11, 2016 at 11:31 am
You have been here long enough to know that
a) You should use IFCodes around your SQL code
b) You need to include INSERT statements to populate your source tables with some useful data, to allow people to help you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 11, 2016 at 12:08 pm
Instead of trying to query to retrieve the inserted values, how about using the OUTPUT clause to save them to a temporary table or a table variable and then use that in the second query? I think that would work better. It'll be a lot safer over all. You won't have to worry about secondary transactions possibly stepping on what you're doing, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 11, 2016 at 1:18 pm
I like your idea of using the output clause from the insert statement. However, I will still have the same problem of trying to obtain the unique lockid value and placing it into the lockid value of the locker value.
I do not have anything to join on that I am aware of from the new rows in the locker table. Thus could you show me the t-sql on how to accomplish this goal?
October 11, 2016 at 1:28 pm
Tell you what, show us what you have tried to do to solve your problem. It would help if you provided DDL, sample data, and expected results based on the sample data. Use these items to demonstrate the issue(s) are encountering.
October 11, 2016 at 1:49 pm
is this the third time of asking the same q?........apologies if I am wrong
http://www.sqlservercentral.com/Forums/FindPost1824675.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 11, 2016 at 1:53 pm
This not the third time I asked a question. In this question, I found out what the issue is and I want to find a resolution.
October 11, 2016 at 1:59 pm
I provided the ddl on the original post. here is sample data:
SELECT [lockID] = 4336
,[schoolID] = 134
,[serialNumber] = 2001
,[type] = BL
,[comboSeq] =2
for [dbo].[Lock]
SELECT [lockerID] = 4336
,[schoolID] = 134
,[number] = 2268
,[serialNumber] = null
,[type] = ed
,[locationID] = 1555
,[grade] - null
,[reserved] = null
,[lockID] = 4336
,[share] = 1
,[lockType] = BL
FROM [dbo].[Locker]
October 11, 2016 at 2:02 pm
wendy elizabeth (10/11/2016)
This not the third time I asked a question. In this question, I found out what the issue is and I want to find a resolution.
J Livingston SQL (10/11/2016)
is this the third time of asking the same q?........apologies if I am wrong
therefore please accept my humble apologies again
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 11, 2016 at 2:07 pm
wendy elizabeth (10/11/2016)
I provided the ddl on the original post. here is sample data:SELECT [lockID] = 4336
,[schoolID] = 134
,[serialNumber] = 2001
,[type] = BL
,[comboSeq] =2
for [dbo].[Lock]
SELECT [lockerID] = 4336
,[schoolID] = 134
,[number] = 2268
,[serialNumber] = null
,[type] = ed
,[locationID] = 1555
,[grade] - null
,[reserved] = null
,[lockID] = 4336
,[share] = 1
,[lockType] = BL
FROM [dbo].[Locker]
Once again, no IFCode formatting. These are not INSERT statements. And you have not provided desired results.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 11, 2016 at 2:10 pm
Funny, but when I run that query on MY computer, it says it can't find the table.
October 11, 2016 at 2:25 pm
wendy elizabeth (10/11/2016)
This not the third time I asked a question. In this question, I found out what the issue is and I want to find a resolution.
Please explain the issue. Provide as much detail as you can.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 11, 2016 at 2:41 pm
The insert statements are on my original post. The example I showed you is an example of what I want the output to look like. Would you give me an example of the if statement you are looking for? Is this for begin tran and end tran?
October 11, 2016 at 2:45 pm
Please do everyone here a favor and post them all together.
1. create table statements.
2. insert statements.
3. T-SQL for the queries you tried and how they're not quite what you're trying to do.
... and please format them using the tags!
Please do everyone here a favor and read this article. You've been here long enough to know better.
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 11, 2016 at 3:23 pm
Also, please remember that we are volunteers. We are not paid to provide answers. We do this to help others but to do this we need you provide as much information as possible in a format that does not require us to make any changes to what you provide so that we can just cut, paste, and run in an empty sandbox database to help you solve your issue(s).
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply