February 15, 2005 at 8:59 am
I have a table with several addresses for each company. I want to number each entry. In another table, I have a entries for contact people at each location. How can I code to get the location_number populated?
--The user isn't going to know which location_number to insert
--I'm wanting SQL to populate that based on how many locations are already in the
--table for that supplier
CREATE TABLE [SUPPLIERS] (
[SUPPLIER_NUMBER] [int] NULL ,
[Location_Number] [smallint] NULL ,
[SUPPLIER_NAME] [varchar] (50) NULL ,
[ADDRESS1] [varchar] (50) NULL ,
[ADDRESS2] [varchar] (50) NULL ,
[City] [varchar] (50) NULL ,
[State] [varchar] (2) NULL ,
[ZIP] [varchar] (50) NULL ,
[Comment] [varchar] (1000) NULL ,
[old_unqkey] [int] NULL ,
[Supplier_UniqueID] [int] IDENTITY (1, 1) NOT NULL
) ON [DATA]
GO
insert into suppliers values(1234, 1, 'ABC Co')
go
insert into suppliers values(1234, 2, 'ABC Co')
go
insert into suppliers values(5678, 1, 'DEF Co')
--etc
Access is my frontend.
TIA,
Michelle
February 15, 2005 at 9:16 am
I think the simplest way would be to use a trigger to make that update. You'd have to do it for inserts and deletes. Msg me if you need an exemple of the code to make this work.
February 15, 2005 at 11:52 am
Run this code in query analyser and post any questions you might have in here.
if exists (Select * from dbo.SysObjects where name = 'SUPPLIERS' and XType = 'U')
begin
DROP TABLE SUPPLIERS
end
GO
CREATE TABLE [SUPPLIERS] (
[SUPPLIER_NUMBER] [int] NULL ,
[Location_Number] [smallint] NULL ,
[SUPPLIER_NAME] [varchar] (50) NULL ,
[ADDRESS1] [varchar] (50) NULL ,
[ADDRESS2] [varchar] (50) NULL ,
[City] [varchar] (50) NULL ,
[State] [varchar] (2) NULL ,
[ZIP] [varchar] (50) NULL ,
[Comment] [varchar] (1000) NULL ,
[old_unqkey] [int] NULL ,
[Supplier_UniqueID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TRIGGER [trSUPPLIERS_Insert] ON [dbo].[SUPPLIERS]
FOR INSERT
AS
SET NOCOUNT ON
UPDATE S SET Location_Number = dtNewLocation.Location_Number
FROM dbo.SUPPLIERS S INNER JOIN
(SELECT I1.Supplier_UniqueID, COUNT(*) + dtLastLocation.MaxLocation_Number as Location_Number FROM (SELECT S2.SUPPLIER_NUMBER, ISNULL(MAX(S2.Location_Number), 0) AS MaxLocation_Number FROM dbo.SUPPLIERS S2 INNER JOIN Inserted I3 ON S2.SUPPLIER_NUMBER = I3.SUPPLIER_NUMBER GROUP BY S2.SUPPLIER_NUMBER) dtLastLocation INNER JOIN Inserted I1 ON I1.SUPPLIER_NUMBER = dtLastLocation.SUPPLIER_NUMBER INNER JOIN Inserted I2 ON I2.SUPPLIER_NUMBER = I1.SUPPLIER_NUMBER AND I2.Supplier_UniqueID <= I1.Supplier_UniqueID GROUP BY I1.Supplier_UniqueID, dtLastLocation.MaxLocation_Number)
dtNewLocation ON S.Supplier_UniqueID = dtNewLocation.Supplier_UniqueID
GO
PRINT 'TESTING 1 insert at the time'
insert into dbo.suppliers (SUPPLIER_NUMBER, SUPPLIER_NAME, ADDRESS1) values(1234, 'ABC Co', 'a')
Select top 1 * from dbo.suppliers where SUPPLIER_NUMBER = 1234 order by Supplier_UniqueID desc
insert into dbo.suppliers (SUPPLIER_NUMBER, SUPPLIER_NAME, ADDRESS1) values(1234, 'ABC Co', 'b')
Select top 1 * from dbo.suppliers where SUPPLIER_NUMBER = 1234 order by Supplier_UniqueID desc
insert into dbo.suppliers (SUPPLIER_NUMBER, SUPPLIER_NAME, ADDRESS1) values(1234, 'ABC Co', 'c')
Select top 1 * from dbo.suppliers where SUPPLIER_NUMBER = 1234 order by Supplier_UniqueID desc
insert into dbo.suppliers (SUPPLIER_NUMBER, SUPPLIER_NAME, ADDRESS1) values(1234, 'ABC Co', 'd')
Select top 1 * from dbo.suppliers where SUPPLIER_NUMBER = 1234 order by Supplier_UniqueID desc
PRINT 'Testing a "bulk" insert'
insert into dbo.suppliers (SUPPLIER_NUMBER, SUPPLIER_NAME, ADDRESS1)
Select 1234, 'ABC Co', 'e'
UNION ALL
Select 1234, 'ABC Co', 'f'
UNION ALL
Select 1234, 'ABC Co', 'g'
UNION ALL
Select 1234, 'ABC Co', 'h'
Select top 4 * from dbo.suppliers where SUPPLIER_NUMBER = 1234 order by Supplier_UniqueID desc
PRINT 'this shows what happens if the suppliers all get whipped out.. or if the last ones are deleted'
TRUNCATE TABLE dbo.SUPPLIERS
insert into dbo.suppliers (SUPPLIER_NUMBER, SUPPLIER_NAME, ADDRESS1) values(1234, 'ABC Co', 'd')
Select top 1 * from dbo.suppliers where SUPPLIER_NUMBER = 1234 order by Supplier_UniqueID desc
PRINT 'as you can see the "identity" gets reused, to avoid this you''d have to keep the last identity of each supplier in a separate table and use that to query the last used id'
PRINT ''
PRINT 'Testing the code to see if it works with multiple suppliers beeing inserted at the same time : '
insert into dbo.suppliers (SUPPLIER_NUMBER, SUPPLIER_NAME, ADDRESS1)
SELECT 1234, 'ABC Co', 'e'
UNION ALL
SELECT 5678, 'ABC Co', 'f'
UNION ALL
SELECT 5678, 'ABC Co', 'g'
UNION ALL
SELECT 9012, 'ABC Co', 'h'
Select top 4 * from dbo.suppliers order by Supplier_UniqueID desc
PRINT 'TESTS SUCCESSFULLS'
GO
--DROP TABLE SUPPLIERS
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply