SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question about a composite key and autoincrement


Question about a composite key and autoincrement

Author
Message
herkusg
herkusg
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 22
Hello all,

I was recently assigned to convert an Access database to Sql Server. I have to try and keep the structure the closest properly because some other programs use this database.

This concrete table got a composite key for Company, Warehouse, ManufacturingID but the ID part starts over with each combination of Company and warehosue.

Example:
Company | Warehouse | ManufacturingID | ...
AU | w01 | 1
AU | w01 | 2
AU | w04 | 1
AU | w04 | 2
NZ | w01 | 1
NZ | w02 | 2

The access program does this manually which I don't want to replicate because it would just cause troubles..

I am looking for options on how to do this, preferably with not having to change much of the table so the other programs would be easy to adapt.

Reading around a bit I think one possible solution could be to use a INSTEAD OF INSERT trigger, is this right?

Thanks for any help.
Joe Torre
Joe Torre
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9520 Visits: 1556
It could be done that way. One could also use a stored procedure for inserts. Are you planning to keep an Access database as a front end?
herkusg
herkusg
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 22
Joe Torre - Wednesday, February 21, 2018 5:48 PM
It could be done that way. One could also use a stored procedure for inserts. Are you planning to keep an Access database as a front end?


No, this program will be web based (asp .net core 2.0) and the other important that uses this database is, i think, a visual basic .net application.
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3848 Visits: 531
herkusg - Wednesday, February 21, 2018 4:26 PM
Hello all,

I was recently assigned to convert an Access database to Sql Server. I have to try and keep the structure the closest properly because some other programs use this database.

This concrete table got a composite key for Company, Warehouse, ManufacturingID but the ID part starts over with each combination of Company and warehosue.

Example:
Company | Warehouse | ManufacturingID | ...
AU | w01 | 1
AU | w01 | 2
AU | w04 | 1
AU | w04 | 2
NZ | w01 | 1
NZ | w02 | 2

The access program does this manually which I don't want to replicate because it would just cause troubles..

I am looking for options on how to do this, preferably with not having to change much of the table so the other programs would be easy to adapt.

Reading around a bit I think one possible solution could be to use a INSTEAD OF INSERT trigger, is this right?

Thanks for any help.

This query looks new to me, i.e from MS ACCESS to SQL Server, I'd have to explore and come back to you, Thanks !

GilaMonster
GilaMonster
SSC Guru
SSC Guru (953K reputation)SSC Guru (953K reputation)SSC Guru (953K reputation)SSC Guru (953K reputation)SSC Guru (953K reputation)SSC Guru (953K reputation)SSC Guru (953K reputation)SSC Guru (953K reputation)

Group: General Forum Members
Points: 953532 Visits: 48920
You'll have to code that manually as you do the insert, stored procedure for insert would probably be best.
Do watch out for concurrency errors, this kind of thing is not that easy.. (I have a blog post publishing in a couple weeks on this)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


DesNorton
DesNorton
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20901 Visits: 9062
herkusg - Wednesday, February 21, 2018 4:26 PM
Hello all,

I was recently assigned to convert an Access database to Sql Server. I have to try and keep the structure the closest properly because some other programs use this database.

This concrete table got a composite key for Company, Warehouse, ManufacturingID but the ID part starts over with each combination of Company and warehosue.

Example:
Company | Warehouse | ManufacturingID | ...
AU | w01 | 1
AU | w01 | 2
AU | w04 | 1
AU | w04 | 2
NZ | w01 | 1
NZ | w02 | 2

The access program does this manually which I don't want to replicate because it would just cause troubles..

I am looking for options on how to do this, preferably with not having to change much of the table so the other programs would be easy to adapt.

Reading around a bit I think one possible solution could be to use a INSTEAD OF INSERT trigger, is this right?

Thanks for any help.

One way to achieve this is to use a "Tracking Table" where you have a row for each Company-Warehouse combination, and keep a record of the last used number.
Use a proc to update the number, and return the number to the calling process.
NOTE: This can lead to hot-spotting. However, since you are using Access, I assume that the volume will be low, and you should be OK.
CREATE TABLE dbo.NextID (
Company CHAR(2) NOT NULL
, Warehouse CHAR(3) NOT NULL
, NextID INT NOT NULL
, CONSTRAINT PK_NextID PRIMARY KEY CLUSTERED(Company, Warehouse)
);
GO

CREATE PROCEDURE dbo.GetNextID
@Company CHAR(2)
, @Warehouse CHAR(3)
, @NextID INT OUTPUT
AS
BEGIN
-- Ensure that there is a record for the provided Company-Warehouse combination
INSERT INTO dbo.NextID (Company, Warehouse, NextID)
SELECT @Company, @Warehouse, 0
WHERE NOT EXISTS (SELECT 1 FROM dbo.NextID AS n WITH (XLOCK, HOLDLOCK)
WHERE n.Company = @Company
AND n.Warehouse = @Warehouse);

-- Use a quirky Update to get the next ID
UPDATE dbo.NextID
SET @NextID = NextID = NextID + 1
WHERE Company = @Company
AND Warehouse = @Warehouse;

END;
GO


DECLARE @Company CHAR(2) = 'AU';
DECLARE @Warehouse CHAR(3) = 'w01';
DECLARE @NextID INT;

EXEC dbo.GetNextID
@Company = @Company
, @Warehouse = @Warehouse
, @NextID = @NextID OUTPUT;

SELECT
Company = @Company
, Warehouse = @Warehouse
, NextID = @NextID;
GO


Since this is a SQL 2014 board, I am assuming that you are on SQL 2014.
In that case, an alternate method is to dynamically create a sequence object for each Company-Warehouse combination.
You also create a proc to get the next number, but there is no tracking table.
CREATE PROCEDURE dbo.GetNextSEQ
@Company CHAR(2)
, @Warehouse CHAR(3)
, @NextID INT OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(4000);
DECLARE @SeqName SYSNAME = N'dbo.' + @Company + '_' + @Warehouse + '_SEQ' ;

-- Ensure that there is a dedicated sequence object for provided Company-Warehouse combination
SET @SQL = N'IF OBJECT_ID(N''' + @SeqName + N''', N''SO'') IS NULL
BEGIN
CREATE SEQUENCE ' + @SeqName + N'
AS INT
MINVALUE 1
START WITH 1
INCREMENT BY 1
NO CYCLE;
END;';

EXEC sys.sp_executesql @stmt = @SQL;


SET @SQL = N'SET @NextID = NEXT VALUE FOR ' + @SeqName + N';';

EXEC sys.sp_executesql
@stmt = @SQL
, @params = N'@NextID INT OUTPUT'
, @NextID = @NextID OUTPUT;

END;
GO


DECLARE @Company CHAR(2) = 'AU';
DECLARE @Warehouse CHAR(3) = 'w01';
DECLARE @NextID INT;

EXEC dbo.GetNextSEQ
@Company = @Company
, @Warehouse = @Warehouse
, @NextID = @NextID OUTPUT;

SELECT
Company = @Company
, Warehouse = @Warehouse
, NextID = @NextID;
GO

SELECT *
FROM sys.sequences AS s;



How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags.
herkusg
herkusg
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 22
Thanks for the replies guys,

I will consider these options and see what I can do, It seems to me that any path I go the concurrency will be a problem..

@GilaMonster Looking forward for that blog.
DesNorton
DesNorton
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20901 Visits: 9062
herkusg - Thursday, February 22, 2018 9:19 PM
Thanks for the replies guys,

I will consider these options and see what I can do, It seems to me that any path I go the concurrency will be a problem..

@GilaMonster Looking forward for that blog.

Using sequences avoids the concurrency issue.



How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags.
Subramaniam Chandrasekar
Subramaniam Chandrasekar
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3848 Visits: 531
herkusg - Thursday, February 22, 2018 9:19 PM
Thanks for the replies guys,

I will consider these options and see what I can do, It seems to me that any path I go the concurrency will be a problem..

@GilaMonster Looking forward for that blog.

Yep, I have a query from my end, Why you are not certainly used SQL Server Migration Assistant (SSMA) ? A tool which helps much in these cases. Just a curious query...

sgmunson
sgmunson
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103888 Visits: 7425
subramaniam.chandrasekar - Friday, February 23, 2018 12:56 AM
herkusg - Thursday, February 22, 2018 9:19 PM
Thanks for the replies guys,

I will consider these options and see what I can do, It seems to me that any path I go the concurrency will be a problem..

@GilaMonster Looking forward for that blog.

Yep, I have a query from my end, Why you are not certainly used SQL Server Migration Assistant (SSMA) ? A tool which helps much in these cases. Just a curious query...

The manual computation of a unique number per combination of company and warehouse makes the use of SSMA considerably less valuable. Not saying it couldn't be used for any of the other tables, but given the level of effort involved in using SSMA, it may not be worth it. You have to judge those things on a case by case basis.


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search