October 2, 2015 at 2:29 pm
In a special request run, I need to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitiions:
CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT NULL,
[schoolID] [int] NOT NULL,
[number] [varchar](10) NOT NULL,
[lockID] [int] 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]
CREATE TABLE [dbo].[Lock](
[lockID] [int] IDENTITY(1,1) NOT NULL,
[schoolID] [int] NOT 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]
The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the correspsonding lock table that is uniquely defined in the lock table and identified by the lockid.
Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows. I would then like to take value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.
I have sql that looks like the following so far:
declare @SchoolID int = 999
insert into test.dbo.Locker ( [schoolID], [number])
select distinct LKR.schoolID, A.lockerNumber
FROM [InputTable] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID
AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID)
order by LKR.schoolID, A.lockerNumber
I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables? Thus can you either modify the sql that I just listed above and/or come up with some new sql that will show me how to accomplish my goal?
October 2, 2015 at 3:35 pm
Judging from your INSERT statement, you've got some data coming in from somewhere and getting loaded into InputTable. Can you give us the DDL for that table as well as some sample rows?
October 2, 2015 at 4:12 pm
Here is a definition of the input table, CREATE TABLE [dbo].[InputTable](
[schoolNumber] [varchar](3) NULL,
[lockerNew] [bit] NULL,
[lockerType] [varchar](3) NULL,
[lockerLocation] [varchar](30) NULL,
[lockerGrade] [varchar](4) NULL,
[lockerReserved] [bit] NULL,
[lockerShare] [bit] NULL,
[lockerNumber] [varchar](10) NULL,
[lockerSerialNumber] [varchar](20) NULL,
[lockSerialNumber] [varchar](20) NULL,
[lockType] [varchar](3) NULL,
[lockComboSeq] [int] NULL,
[lockCombo1] [varchar](8) NULL,
[lockCombo2] [varchar](8) NULL,
[lockCombo3] [varchar](8) NULL,
[lockCombo4] [varchar](8) NULL,
[lockCombo5] [varchar](8) NULL,
[lockDialCode] [varchar](4) NULL,
[lockKeyCode] [varchar](6) NULL
) ON [PRIMARY]
The only value from this table used is the LockerNumber. No other values are really needed in the question I raised. Also each school is required to make their locker numbers unique.
October 2, 2015 at 6:54 pm
How are you doing the insert into the first table? Is it a bulk insert or one at a time? You might be able to create a variable and stuff the value of SCOPE_IDENTITY() into it and then use that to insert into the second table, but that would only work if you inserted the records one at a time, I think.
October 2, 2015 at 9:11 pm
I plan on updating one record at a time. Could you show me the sql to do the following.
"stuff the value of SCOPE_IDENTITY() into it and then use that to insert into the second table,"?
Also I want to mention the following:
The InputTable.lockerNumber value will be added to the Locker.number column.
October 2, 2015 at 10:13 pm
Here's another option... use the OUTPUT clause to insert some/all of the records inserted in your stored procedure to another table:
Basically imitates a trigger without all the nasty side effects.
October 3, 2015 at 3:37 pm
Would you give me an example of the insert statement using the output statement obtain the values from the output statement to obtain the values from the inserted statement? How would you use set this up using the data from the inputTable I listed above?
October 5, 2015 at 11:18 am
Alright, here's an example of an OUTPUT clause on your previous INSERT statement. All this does is take the values that you are inserting, along with the auto-generated IDENTITY values, and stick them into a table variable. Once the table variable is populated, you can use the values for virtually anything else you want.
I say this, but I'm still not certain as to what you want to do next with the data. You mentioned the need to tie these to Locks, but I don't see where your Lock information is coming from. There may be a better way to tie these together than my example, but we need to understand the whole picture first.
declare @SchoolID int = 999
DECLARE @Lockers TABLE (LockerID int, SchoolID int, Number varchar(10)) -- Create a table variable to hold OUTPUT clause results for later use
INSERT INTO dbo.Locker ( schoolID, number)
OUTPUTinserted.lockerID, inserted.schoolID, inserted.number INTO @Lockers-- Use OUTPUT clause to get newly inserted Key value along with inserted values into @lockers table
SELECTDISTINCT LKR.schoolID
, A.lockerNumber
FROMInputTable A
INNER JOINdbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber
INNER JOINdbo.Locker LKR ON SCH.schoolID = LKR.schoolID
AND A.lockerNumber not in (select number from dbo.Locker where schoolID = @SchoolID)
ORDER BYLKR.schoolID, A.lockerNumber
--==== Show values from our new table...now that these are in a table variable, we can use them to perform other operations
SELECT*
FROM@Lockers
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply