How do i insert new records into a table that is not autoincremented

[citizenzen]

my problem is a bit quirky. i created a stored procedure that inserts and updates multiple tables. earlier, i had to modify a table named 'tbl_checkouts', that was auto-incremented to non-identity with a starting point of 17,000. the data that is inserted into tbl_checkouts comes from another source and forcing the starting point at 17000 would prevent any issues with older data. now, with my stored procedure 'usp_checkoutExistingPrgRev', i am checking for the existence of records and then if the records don't exist, inserting data into tbl_checkoutItem and then tbl_checkouts. How can I make sure that I don't create an issue when inserting these new records and primarily how can i insert the new records when the 'ID' of the records is not known? someone told me that I should create a temporary holding table that will compare ids of the last inserted record into tbl_checkouts and then based on that last record, insert it into the holding table + 1. i just don't know how to implement something like that in my stored procedure below.

CREATE PROCEDURE usp_checkoutExistingPrgRev
(
@bcode varchar,
@tapeid int,
@requestnu int,
@userid int,
@ID int OUTPUT

)

AS

BEGIN TRAN

/*update status of media in tbl_mediaCheckout*/
IF EXISTS (select * from tbl_mediaCheckoutItem WHERE mediaCheckoutItemID = @requestnu )
UPDATE tbl_mediaCheckoutItem
SET StatusID = 4
WHERE mediaCheckoutItemID = @requestnu
AND musicID = @tapeid
OR mediaCheckoutItemID = @requestnu and showID = @tapeid
OR mediaCheckoutItemID = @requestnu and elementID= @tapeid

ELSE

UPDATE tbl_mediaDubsItem
SET StatusID =4
WHERE mediaDubsItemID = @requestnu
AND fk_musicID = @tapeid
OR mediaDubsItemID = @requestnu and fk_showID = @tapeid
OR mediaDubsItemID = @requestnu and fk_elementID= @tapeid


/*check for existence, if media does not exist, insert into tbl_checkout, else, update tbl_checkoutItem*/

/*insert into tbl_checkouts*/

IF NOT EXISTS(select * FROM tbl_checkoutItem WHERE [fk tape id] = @tapeid)
INSERT INTO tbl_checkoutItem
([fk tape id], [fk checkout id], [In Library] , dateCheckedOut, dateModified,statusID)
VALUES
(@tapeid, @ID, 0, GETDATE(), GETDATE(), 2)

/*INSERT INTO tbl_checkouts ([User ID]) VALUES (@userid)

set @ID = SCOPE_IDENTITY()
SELECT @ID = SCOPE_IDENTITY()*/

ELSE

UPDATE tbl_checkoutItem
SET [In Library] = 0 ,
dateCheckedOut= GETDATE(),
dateModified= GETDATE()
WHERE [fk tape id] = @tapeid

/*if barcode is empty, insert new barcode*/
IF @bcode = NULL
UPDATE tbl_Item
SET Barcode =@bcode
WHERE [fk video ID] = @tapeid
OR [fk program ID] = @tapeid
OR [fk element ID] = @tapeid

COMMIT TRAN
GO

 

[ESquared]

Please save yourself lots of really bad pain down the road and stop naming columns with spaces in them. And drop the "fk" stuff, holy cow.

auto-incremented to non-identity

What? What does that mean?

Insert to a table. Collect the range of IDs with scope_identity() and @@rowcount. Using those, it's fairly simple math to know what IDs were in the range. Doing the insert to the next table, either simply use that ID range or join the inserted data back to your original data to get the details for that next table.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[citizenzen]

thanks. we don't always have control over how the tables or database is designed, especially if it's an inherited database. thanks for your input.

 

[ESquared]

If you'll provide more information someone could possibly help you with your task. For now I think we don't have enough information.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]