Inserting new ID's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Inserting new ID's

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-13 : 08:00:15

Tena writes "I am new to SQL programming and I have a project that is for a manufacturing facility. At the start of this project parts were initially all loaded into a table (tblItems), and then parts were removed and added as time progressed.

So, now they want 7000 parts added back from the backup that originally contained all these parts (I was given a few SP's to accomplish this task), but the problem is that the original itemid's didn't carry over with the added items. It is imperative that they keep their original id's as the info from the BOM (Bill of Material) tables is also being brought over and itemid's are what identifies them to the BOM tables.

I am including the SP's that are doing the work.

TIA-

Tena

______________________________________________
CREATE PROCEDURE spBeck_CopyParts
AS

SET NOCOUNT ON

declare @ItemNumber varchar(50)
declare @id int

DECLARE items CURSOR
LOCAL FAST_FORWARD FOR
SELECT ItemNumber
FROM PARTFILE

OPEN items
FETCH items INTO @ItemNumber
WHILE @@FETCH_STATUS = 0
BEGIN
exec spBeck_CopyItem @ItemNumber, @id out

FETCH items INTO @ItemNumber
END
CLOSE items
DEALLOCATE items

RETURN
_______________________________________________
ALTER PROCEDURE spBeck_CopyItem
(
@item_number varchar(50),
@new_id int out
)
AS

SET NOCOUNT ON

declare @item_id int
declare @sub_id int
declare @StepId int
declare @Qty float
declare @ToBeProduced tinyint
declare @IsAdditive tinyint
declare @SeqNo int
declare @QtyScalingFactor float
declare @ItemNumber varchar(50)

set @new_id = NULL

SELECT @new_id = ItemId
FROM tblItems
WHERE ItemNumber = @item_number

if @new_id IS NOT NULL return

SELECT @item_id = ItemId
FROM backups..tblItems
WHERE ItemNumber = @item_number

INSERT INTO tblItems
( ItemNumber, ItemCode, ItemDescription, MaterialId,
PrimaryUOMID, PrimaryUOMtypeID, SecondaryUOMID, SecondaryUOMtypeId, UOMConversionFactor,
IsInventoried, Comments, IsDeleted, CategoryID, MinBuildQty, MinInventoryQty,
IsVendorSupplied, VendorID, IsSpecial, BuildYield, AtomicBuildQty, IncrementBuildQty,
IsScrap, IsCombinable, ToBeProduced, ReplenishmentTime, AverageBuildQty,
UnitPrice, IsCountByWeight, Origin, ToBeChecked, dtChecked,
LastMod, dtLastMod, MaxBuildQty, DemandDirectly
)
SELECT
ItemNumber, ItemCode, ItemDescription, MaterialId,
PrimaryUOMID, PrimaryUOMtypeID, SecondaryUOMID, SecondaryUOMtypeId, UOMConversionFactor,
IsInventoried, Comments, IsDeleted, CategoryID, MinBuildQty, MinInventoryQty,
IsVendorSupplied, VendorID, IsSpecial, BuildYield, AtomicBuildQty, IncrementBuildQty,
IsScrap, IsCombinable, ToBeProduced, ReplenishmentTime, AverageBuildQty,
UnitPrice, IsCountByWeight, Origin, ToBeChecked, dtChecked,
LastMod, dtLastMod, MaxBuildQty, DemandDirectly
FROM backups..tblItems
WHERE ItemId = @item_id

SELECT @new_id = ItemId
FROM tblItems
WHERE ItemNumber = @item_number

-- now let's copy the BOMBR

-- first, copy route steps
INSERT INTO tblBOMBRSteps
( ItemId, StepNo, OperationId, WIPTypeOut, UOMout,
SecondaryUOMout, Instructions, ISOdocument, Duration,
StepCode
)
SELECT @new_id, StepNo, OperationId, WIPTypeOut, UOMout,
SecondaryUOMout, Instructions, ISOdocument, Duration,
StepCode
FROM backups..tblBOMBRSteps
WHERE ItemId = @item_id
ORDER BY StepNo

-- then, copy BOMBR input items
-- note that items might not exist, so we need to copy them as well

DECLARE materials CURSOR
LOCAL FAST_FORWARD FOR
SELECT N.StepId,
I.Qty, I.ToBeProduced, I.IsAdditive, I.SeqNo, I.QtyScalingFactor,
P.ItemNumber
FROM backups..tblBOMBRStepItems I,
backups..tblBOMBRSteps B,
tblBOMBRSteps N,
backups..tblItems P
WHERE B.ItemId = @item_id AND
I.StepId = B.StepI

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-13 : 09:18:01

Check SET IDENTITY_INSERT in Books On-Line. Didn't read everything you posted, I hopw it helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources