IDENTITY_INSERT and Table Variables

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

Transact-SQL (2000)

IDENTITY_INSERT and Table Variables

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-13 : 12:12:39

IF I do this:
DECLARE @MyTable table(QuestionID UNIQUEIDENTIFIER, QuestionPos int IDENTITY (1, 1))
SET IDENTITY_INSERT @MyTable ON

What are the implications of such? Do I need to do this after I'm done with my Table Variable?
SET IDENTITY_INSERT @MyTable OFF

Do I not need to do anything since it's a table variable?
BOL says:
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON

Does session mean "connection"?

I guess my question is do I need to turn it off at the bottom of my stored proc, and what other things will this affect?
TIA!
Michel

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-13 : 12:30:16

Here's a (hopefully more relevant) question: why are you bothering with identity in a table variable anyway, if you're going to insert existing values in that column?

I'm sure you've got a really cool reason for it, I just don't see it.

"Session" doesn't just mean connection, it's also the particular batch that connection is running at the time. If you had two different batches using the same connection, they could have different tables set to IDENTITY_INSERT. When you see "session", think "SPID", that might help.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-13 : 14:09:53

Rob, I got it solved, but thx for trying to help. Thanks for the info on Sessions. It makes sense now.

Really cool reason for doing this? Well not really but here goes:

What I have is a "dynamic testing system"
I have a Tests and Questions. Tests have N Questions.
I also have UserTest and UserQuestions. With this it allows a user to have an instance of a test. This instance is a random set of questions for a particular test. My problem was I need to store the order of the questions so that I can easily navigate between questions in an ASP application. I needed to put the Identity value into the UserQuestion table, but the field in UserQuestion could not be an Identity (thinking back I guess it can be, but I have it solved the way I wanted to solve it :) ).

So this is what I was doing (I'll spare you the TSQL, it's not nice to look at).
1. Get 5 random questions from teh questions table, and insert them into my table variable
2. Create a UserTest record
3. Insert Into UserQuestion from the Table Variable

This gives me a "random" test for each user, with their own questions stored in such a way that they can navigate back and forth between questions.

Thanks for the

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-13 : 14:28:22

quote:
Thanks for the

Well, I wish I could take credit for inventing the word "the", but other people introduced it to me and I just picked it up

That IS cool, BTW! I had a feeling you were doing something like that, or maybe some search results kind of thing.

I wouldn't worry too much about ugly T-SQL, you should see some of MINE...

Shut up Femenella!

If you've got it working, can you post it?

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-13 : 14:35:38

Here Goes.... BTW I think i fogot to finish writing my last post oops :)
I've still got to determine how many random questions I need to grab, but this is 90% complete.

CREATE PROCEDURE [dbo].[p_User_CreateUserTest](
@TestID UNIQUEIDENTIFIER,
@UserID UNIQUEIDENTIFIER,
@UserTestID UNIQUEIDENTIFIER OUTPUT
)
AS

--Lets see if this user already has an instance of this test
SELECT @UserTestID = ut.UserTestID
FROM UserTest ut
WHERE ut.TestID = @TestID

--If the User doesn't have their own instance of this test, make one
IF (@UserTestID IS NULL)
BEGIN
DECLARE @MyTable table(QuestionID UNIQUEIDENTIFIER, QuestionPos int IDENTITY (1, 1))

SET ROWCOUNT 5
--Pick Random Questions
INSERT INTO @MyTable (QuestionID)
SELECT q.QuestionID
FROM Question q
WHERE q.TestID = @TestID
ORDER BY NewID()
SET ROWCOUNT 0

--Create UserTest
SELECT @UserTestID = NewID()

INSERT INTO UserTest(UserTestID, UserID, TestID)
VALUES(@UserTestID, @UserID, @TestID)

--Create the UserQuestions
--This is this users instance of a test
INSERT INTO UserQuestion
SELECT NewID(), @UserID, mt.QuestionID, @UserTestID, QuestionPos, GetDate(), GetDate()
FROM @MyTable mt
END
GO

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-06-13 : 15:04:42

Here's another cool use for IDENTITY! Removes duplicated users from the approval list and also puts the Sequences into a nice purdy order!

CREATE PROCEDURE dbo.GenerateDefaultApprovals
(
@ProjectID INT
)
AS
SET NOCOUNT ON

DECLARE @ARLIMIT MONEY
SET @ARLIMIT = 500.0000

DECLARE @RequesterID INT, @ProjectOwnerID INT, @DepartmentID INT, @ItemID INT, @CategoryID INT
DECLARE @CTD INT, @CTT INT, @CTESS INT, @CTO INT
DECLARE @Total MONEY
DECLARE @Items TABLE (ItemID INT, ComponentType INT)
DECLARE @Approvals TABLE (SequenceID INT, UserID INT, ProjectOwner BIT, ID INT IDENTITY(1,1))

IF EXISTS(SELECT * FROM ProjectApprovals(NOLOCK) WHERE ProjectID = @ProjectID AND ActionID IS NOT NULL)
BEGIN
--PRINT 'Approvals already in motion.'
RETURN 0
END

EXEC LoadComponentTypeVariables @CTD OUTPUT, @CTT OUTPUT, @CTESS OUTPUT, @CTO OUTPUT

SELECT @DepartmentID = DepartmentID, @RequesterID = RequesterID, @ProjectOwnerID = ProjectOwnerID FROM Projects(NOLOCK) WHERE ProjectID = @ProjectID
SELECT @Total = SUM(Quantity * UnitPrice) FROM ProjectItems(NOLOCK) WHERE ProjectID = @ProjectID

SET @Total = ISNULL(@Total, 0)

INSERT INTO @Items (ItemID, ComponentType)
SELECT ItemID, NULL FROM ProjectItems(NOLOCK) WHERE ProjectID = @ProjectID

-- Find all the top level categories and associate it with a ComponentType
WHILE EXISTS(SELECT * FROM @Items WHERE ComponentType IS NULL)
BEGIN
SELECT @ItemID = ItemID FROM @Items WHERE ComponentType IS NULL
SELECT @CategoryID = CategoryID FROM CatalogItems WHERE CatalogItemID = @ItemID

WHILE (SELECT Parent_CatalogCategoryID FROM CatalogCategories WHERE CatalogCategoryID = @CategoryID) IS NOT NULL
SELECT @CategoryID = Parent_CatalogCategoryID FROM CatalogCategories WHERE CatalogCategoryID = @CategoryID

SELECT @CategoryID = CASE Name WHEN 'Desktop' THEN @CTD WHEN 'ESS' THEN @CTESS WHEN 'Other' THEN @CTO WHEN 'Telecom' THEN @CTT ELSE 0 END FROM CatalogCategories WHERE CatalogCategoryID = @CategoryID
UPDATE @Items SET ComponentType = @CategoryID WHERE ItemID = @ItemID
END

-- Insert component types from the project components
INSERT INTO @Items (ItemID, ComponentType)
SELECT c.ComponentID, c.TypeID FROM ProjectComponents AS pc INNER JOIN Components AS c ON pc.ComponentID = c.ComponentID WHERE pc.ProjectID = @ProjectID

-- Select all approvals required for the project
INSERT INTO @Approvals (SequenceID, UserID, ProjectOwner)
SELECT SequenceID, UserID, ProjectOwner
FROM ProjectApprovalTemplates
WHERE AROnly = CASE WHEN @Total >= @ARLIMIT THEN 1 ELSE 0 END
AND DepartmentID = @DepartmentID
AND ComponentTypeID IN (SELECT ComponentType FROM @Items)

-- Select a project owner if none is selected and remove project owner and request from list of approvers
IF @ProjectOwnerID IS NULL SELECT TOP 1 @ProjectOwnerID = UserID FROM @Approvals WHERE ProjectOwner = 1
DELETE FROM @Approvals WHERE UserID = @RequesterID OR UserID = @ProjectOwnerID

-- Delete all duplicates so that a user will only appear in the list once
WHILE EXISTS(SELECT * FROM @Approvals GROUP BY UserID HAVING COUNT(*) > 1)
DELETE FROM @Approvals WHERE ID IN (SELECT MAX(ID) FROM @Approvals GROUP BY UserID HAVING COUNT(*) > 1)

-- Update the sequences and insert the requester as the first step
UPDATE a SET a.SequenceID = (SELECT COUNT(*) + 2 FROM @Approvals WHERE SequenceID < a.SequenceID) FROM @Approvals AS a
INSERT INTO @Approvals (SequenceID, UserID, ProjectOwner) VALUES (1, @RequesterID, 0)

-- If there is a project owner then insert them as the last step
IF @ProjectOwnerID IS NOT NULL
INSERT INTO @Approvals (SequenceID, UserID, ProjectOwner) SELECT MAX(SequenceID) + 1, @ProjectOwnerID, 1 FROM @Approvals

-- Use transactions since a few things will be updated
BEGIN TRAN
-- Delete the current approval set since this replaces it
DELETE FROM ProjectApprovals WHERE ProjectID = @ProjectID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
--PRINT 'Could not delete current project approvals'
RETURN -1
END

-- Update the project owner for the project
IF @ProjectOwnerID IS NOT NULL
BEGIN
UPDATE Projects SET ProjectOwnerID = @ProjectOwnerID WHERE ProjectID = @ProjectID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
--PRINT 'Could not update project for required project owner.'
RETURN -1
END
END

-- Insert the approvals into the table
INSERT INTO ProjectApprovals (ProjectId, UserID, ApproverID, SequenceID, ActionID, Comment, DateCreated, DateNotified, DateProcessed, IsOverridden)
SELECT @ProjectID, UserID, NULL, SequenceID, NULL, NULL, GETDATE(), NULL, NULL, 0 FROM @Approvals

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
--PRINT 'Could not insert approval list into table'
RETURN -1
END

-- Set the requester as having seen the project so we don't have to send an initial email to anyone
UPDATE ProjectApprovals SET DateNotified = GETDATE() WHERE UserID = @RequesterID

IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN

RETURN @@ERROR
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources