October 11, 2016 at 8:27 pm
wendy elizabeth (10/11/2016)
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?
The OUTPUT clause will capture the ID values generated by IDENTITY during the INSERT operation. You should be able to get what you're looking for unless I'm completely missing the point (and that wouldn't be the first time).
"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 12, 2016 at 12:19 am
wendy elizabeth (10/11/2016)
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?
We cannot see your data. To help you, we need some sample data that represents your data, and that we can use to run your queries against, and create/test our own queries against.
Please create us insert statements like the following so that we can get an idea of what your data looks like
-- Create sample data for the Lock table
-- Using IDENTITY_INSERT allows us to force the values in the IDENTITY field, so that it's easier to insert into the next table.
SET IDENTITY_INSERT dbo.Lock ON;
INSERT INTO dbo.Lock (lockID, schoolID, serialNumber, [type], comboSeq)
VALUES (1, 12, 'L123', 'L', 2)
, (2, 12, 'L456', 'L', 2)
, (3, 12, 'L789', 'L', 2)
, (4, 12, 'L951', 'L', 2);
SET IDENTITY_INSERT dbo.Lock OFF;
-- Create sample data for the Locker table
INSERT INTO dbo.Locker (schoolID, number, serialNumber, [type], locationID, grade, reserved, lockID, share, lockType)
VALUES (12, 'C123', 'abc', 'C', 1552, 'grd', 0, 1, 0, 'typ')
, (12, 'C456', 'def', 'C', 1553, 'grd', 0, 2, 0, 'typ')
, (12, 'C789', 'ghi', 'C', 1554, 'grd', 0, 3, 0, 'typ')
, (12, 'C951', 'jkl', 'C', 1555, 'grd', 0, 4, 0, 'typ');
October 12, 2016 at 12:37 am
wendy elizabeth (10/11/2016)
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?
You could try to create a joinable field. Something like this
DECLARE @LockOutput TABLE (
rn INT IDENTITY(1,1)
, LockID INT
);
INSERT INTO dbo.Lock (schoolID, serialNumber, [type], comboSeq)
OUTPUT Inserted.LockID
INTO @LockOutput(LockID)
SELECT
schoolID = 134
, c.serialNumber
, c.[type]
, comboSeq = 2
FROM dbo.Lock AS c
INNER JOIN dbo.Locker AS locker ON locker.lockID = c.lockID
AND locker.locationID BETWEEN 1552 AND 1555
ORDER BY locationID, number;
WITH cteNewLockers AS (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY L.locationID, L.number)
, schoolID = 134
, L.number
, L.serialNumber
, L.[type]
, L.locationID
, L.grade
, L.reserved
, L.share
, L.lockType
FROM dbo.Locker AS L
WHERE L.schoolID = 12
AND L.locationID BETWEEN 1552 AND 1555
)
INSERT INTO dbo.Locker (schoolID, number, serialNumber, [type], locationID, grade, reserved, lockID, share, lockType)
SELECT
NL.schoolID
, NL.number
, NL.serialNumber
, NL.[type]
, NL.locationID
, NL.grade
, NL.reserved
, lo.LockID -- new values from lock table
, NL.share
, NL.lockType
FROM cteNewLockers AS NL
INNER JOIN @LockOutput AS lo
ON NL.rn = lo.rn;
October 12, 2016 at 2:32 am
wendy elizabeth (10/11/2016)
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).....
If your intention is to copy the locker data set (locker, lock & lockcombination tables) for SchoolID=12 and create a new locker data set from it, with new LockID's but the same data, for SchoolID = 134, then you will end up with incorrect data for SchoolID = 134. All the serial numbers of the locks and lockers (and their combinations) will be for SchoolID=12. You will have rubbish data.
If on the other hand the locker data set with serial numbers and everything for SchoolID=12 is actually for SchoolID = 134, then simply change the SchoolID from 12 to 134 in the locker data set.
Please clarify what you are trying to do, and why you want to do it. Refusing to comply with this simple request has already cost you a week.
As an aside, always use a column list for inserts. They are mandatory in certain situations. They are infinitely useful to people attempting to help you on ssc.
INSERT INTO [dbo].[Lock] (column list here)
SELECT 134, k.serialNumber, k.type,2
FROM [dbo].[Lock] k
INNER JOIN [dbo].[Locker] l
ON l.lockID = k.lockID
AND l.locationID BETWEEN 1552 and 1555
ORDER BY l.locationID, l.number
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply