February 21, 2018 at 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.
February 21, 2018 at 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?
February 21, 2018 at 7:04 pm
Joe Torre - Wednesday, February 21, 2018 5:48 PMIt 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.
February 21, 2018 at 9:34 pm
herkusg - Wednesday, February 21, 2018 4:26 PMHello 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 | 2The 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 !
February 22, 2018 at 2:46 am
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
February 22, 2018 at 4:58 am
herkusg - Wednesday, February 21, 2018 4:26 PMHello 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 | 2The 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;
February 22, 2018 at 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.
February 22, 2018 at 9:55 pm
herkusg - Thursday, February 22, 2018 9:19 PMThanks 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.
February 23, 2018 at 12:56 am
herkusg - Thursday, February 22, 2018 9:19 PMThanks 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...
February 23, 2018 at 6:18 am
subramaniam.chandrasekar - Friday, February 23, 2018 12:56 AMherkusg - Thursday, February 22, 2018 9:19 PMThanks 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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 25, 2018 at 8:00 pm
DesNorton - Thursday, February 22, 2018 9:55 PMherkusg - Thursday, February 22, 2018 9:19 PMThanks 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.
I see, I will try this. Also is it easy to setup the actual value of a sequence? For example the Au_wh0_seq to 33245 ?
February 25, 2018 at 9:01 pm
herkusg - Sunday, February 25, 2018 8:00 PMDesNorton - Thursday, February 22, 2018 9:55 PMherkusg - Thursday, February 22, 2018 9:19 PMThanks 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.
I see, I will try this. Also is it easy to setup the actual value of a sequence? For example the Au_wh0_seq to 33245 ?
Very easy ALTER SEQUENCEALTER SEQUENCE dbo.Au_wh0_seq
RESTART WITH 33245;
February 26, 2018 at 6:25 am
Keep in mind sequences can and will have gaps in them. This is not a bug.
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
February 26, 2018 at 12:06 pm
herkusg - Wednesday, February 21, 2018 4:26 PMHello 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 | 2The 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.
Read about the CREATE SEQUENCE statement in SQL.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 26, 2018 at 5:00 pm
Does is really matter if the numbers are sequential or is everyone just used to that?
If it doesn't really matter, you might just use an identity column and let each row get the next number.
If you really needed a sequential number for display reasons in certain output, you could use ROW_NUMBER() to derive that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply