Changing ID's

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.StepId AND
N.ItemId = @.new_id AND
N.StepNo = B.StepNo AND
P.ItemId = I.ItemId
ORDER BY B.StepNo, I.SeqNo
OPEN materials
FETCH materials INTO @.StepId,
@.Qty, @.ToBeProduced, @.IsAdditive, @.SeqNo, @.QtyScalingFactor,
@.ItemNumber
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec spBeck_CopyItem @.ItemNumber, @.sub_id out
INSERT INTO tblBOMBRStepItems
( StepId,
ItemId, Qty, ToBeProduced, IsAdditive, SeqNo, QtyScalingFactor
)
VALUES
( @.StepId,
@.sub_id, @.Qty, @.ToBeProduced, @.IsAdditive, @.SeqNo, @.QtyScalingFactor
)
FETCH materials INTO @.StepId,
@.Qty, @.ToBeProduced, @.IsAdditive, @.SeqNo, @.QtyScalingFactor,
@.ItemNumber
END
CLOSE materials
DEALLOCATE materials
-- then, copy Labor
INSERT INTO tblBOMBRStepLabor
( StepId,
DepartmentId, LaborClassId, Hours, PctLoad, SeqNo, QtyScalingFactor
)
SELECT N.StepId,
I.DepartmentId, I.LaborClassId, I.Hours, I.PctLoad, I.SeqNo,
I.QtyScalingFactor
FROM backups..tblBOMBRStepLabor I,
backups..tblBOMBRSteps B,
tblBOMBRSteps N
WHERE B.ItemId = @.item_id AND
I.StepId = B.StepId AND
N.ItemId = @.new_id AND
N.StepNo = B.StepNo
ORDER BY B.StepNo, I.SeqNo
-- then, copy Machinery
INSERT INTO tblBOMBRStepMachinery
( StepId,
MachineClassId, SetupHours, RunHours, TearHours, SeqNo,
QtyScalingFactor,
PreferredMachineId
)
SELECT N.StepId,
I.MachineClassId, I.SetupHours, I.RunHours, I.TearHours, I.SeqNo,
I.QtyScalingFactor,
I.PreferredMachineId
FROM backups..tblBOMBRStepMachinery I,
backups..tblBOMBRSteps B,
tblBOMBRSteps N
WHERE B.ItemId = @.item_id AND
I.StepId = B.StepId AND
N.ItemId = @.new_id AND
N.StepNo = B.StepNo
ORDER BY B.StepNo, I.SeqNo
RETURNYou can use SET IDENTITY_INSERT to enable you to insert the old
IDENTITY values.
I don't see why you are using a cursor for the INSERTs. You can just
do:
INSERT INTO tblBOMBRStepItems (...)
SELECT ...
which is a whole lot more efficient.
David Portas
SQL Server MVP
--|||>> 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 itemi
d's are what identifies the
m to the BOM tables. <<
What does that mean? Don't you have an industry (or company) standard
for identifying parts? Is this a UPC to EAN conversion or something?
You need to clean up the design and get usable names for the columns
and tables; drop the silly "tbl-" prefix (unless you are making
furniture), quit using reserved wrods and extend vague things like
"quantity (of what?)" to ISO-11179 rules.
How is an item_id different from an item_number? This looks like you
have two identifiers for an item.
Also, just can use a single INSERT INTO statement and avoid all of
these slow, proprietary cursors.|||> I don't see why you are using a cursor for the INSERTs.
Agreed. Unfortunately this is a fairly common mistake. People are used to
doing things one way and they do not know that there is a better way. We
recently hired a guy from a company who had data stored within mainframes
and within SQL Server. He was telling us that the mainframe guys would ask
"Why isn't this stuff running faster? You database guys told us that SQL
Server will be faster than our mainframes." After the cursors were removed
from the stored procedures things ran MUCH faster.
Keith
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129133792.965545.250440@.g49g2000cwa.googlegroups.com...
> You can use SET IDENTITY_INSERT to enable you to insert the old
> IDENTITY values.
> I don't see why you are using a cursor for the INSERTs. You can just
> do:
> INSERT INTO tblBOMBRStepItems (...)
> SELECT ...
> which is a whole lot more efficient.
> --
> David Portas
> SQL Server MVP
> --
>|||It's an over generalization to say that a database will run faster when
ported from a mainframe to SQL Server. In many cases it won't.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%232%23f%2340zFHA.404@.TK2MSFTNGP09.phx.gbl...
> Agreed. Unfortunately this is a fairly common mistake. People are used
> to doing things one way and they do not know that there is a better way.
> We recently hired a guy from a company who had data stored within
> mainframes and within SQL Server. He was telling us that the mainframe
> guys would ask "Why isn't this stuff running faster? You database guys
> told us that SQL Server will be faster than our mainframes." After the
> cursors were removed from the stored procedures things ran MUCH faster.
> --
> Keith
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1129133792.965545.250440@.g49g2000cwa.googlegroups.com...
>|||A little off topic, maybe, but..
What is a mainframe now?
I used to define computers by how one would transport them:
Mainframe = moving van
Minicomputer = pickup truck
Microcomputer = wheelbarrow
Nowadays, I am not sure what people mean when they say mainframe and I
haven't heard the term minicomputer in years.
Thanks for your ideas. lol, I gotta get out more.
Payson
Keith Kratochvil wrote:
> Agreed. Unfortunately this is a fairly common mistake. People are used t
o
> doing things one way and they do not know that there is a better way. We
> recently hired a guy from a company who had data stored within mainframes
> and within SQL Server. He was telling us that the mainframe guys would as
k
> "Why isn't this stuff running faster? You database guys told us that SQL
> Server will be faster than our mainframes." After the cursors were remove
d
> from the stored procedures things ran MUCH faster.
> --
> Keith
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1129133792.965545.250440@.g49g2000cwa.googlegroups.com...|||Mainframe - the larger frame around the less obvious actual frame of an
abstract painting.
Microcomputer - the really tiny chip inside your multifunctional mobile
phone that emulates a fax, a video recorder, an audio recorder, a camera, a
phazor pistol, and - would you believe it - a phone.
Minicomputer - what controls the engine in the new Mini Cooper.
ML|||I know that some IBM-ers used to say that everything that isn't water-ed
are toys.
I doubt that would hold now, as we often see PC's targeted at gaming are wat
er-ed nowadays. ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Payson" <payson_b@.hotmail.com> wrote in message
news:1129149565.653094.229260@.z14g2000cwz.googlegroups.com...
>A little off topic, maybe, but..
> What is a mainframe now?
> I used to define computers by how one would transport them:
> Mainframe = moving van
> Minicomputer = pickup truck
> Microcomputer = wheelbarrow
> Nowadays, I am not sure what people mean when they say mainframe and I
> haven't heard the term minicomputer in years.
> Thanks for your ideas. lol, I gotta get out more.
> Payson
> Keith Kratochvil wrote:
>|||Good question. I think most people identify mainframes by vendor
product line and by the departments and staff who support those
products. I don't know if there is a formal definition of mainframe
nowadays.
Mini = "midrange" = AS/400.
David Portas
SQL Server MVP
--|||Maybe I wasn't clear...
I didn't design the database, I didn't write the stored procedure...
I was just wondering (hence the posting of the code) what was wrong
with the SP that the itemids were not carrying over (I changed the
identity to "No" for this exercise in futility..it seems). Once
again-NOT an SQL programmer.
TIA-
Tena