Insert Value into table
-
Hi
This is regarding old requirment of inserting a value into a one column table of the type of identity.
This scenario is not a real scenario but the hypothetical one. If we want to do the things simmilarly, then how to hadle it. I am expecting the answer without making chane in identity mode i.e. ON/OFF.
Suppose we write the trigger on a table and make note of how many records get inserting into the table. for this we can use this method.
I know we can use more than 2 columns to maintain it properly. But I am asking from the curiosity point.
Thanx
-
Does this answer your question?
IF NOT EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'TestIDIns' AND XType = 'U')BEGIN
CREATE TABLE dbo.TestIDIns
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
, Name VARCHAR(50) NOT NULL
 
INSERT INTO dbo.TestIDIns (Name) VALUES ('ONE')
SET IDENTITY_INSERT dbo.TestIDIns ON
INSERT INTO dbo.TestIDIns (ID, Name) SELECT 9, 'NINE' UNION ALL SELECT 10, 'TEN'
SET IDENTITY_INSERT dbo.TestIDIns OFF
INSERT INTO dbo.TestIDIns (Name) VALUES ('ELEVEN')
SELECT * FROM dbo.TestIDIns ORDER BY ID
DROP TABLE dbo.TestIDIns
END
-
This seems like a pointless exam question. DEFAULT VALUES may be what you want:
DECLARE @t TABLE
(
TID int IDENTITY NOT NULL
)
INSERT INTO @t DEFAULT VALUES
INSERT INTO @t DEFAULT VALUES
INSERT INTO @t DEFAULT VALUES
SELECT *
FROM @t
Edit: I suppose you could use this with ROLLBACK and SCOPE_IDENTITY() to produce functionality similar to an Oracle SEQUENCE.
-
Please do not cross-post. We lessen your chances of getting the right answer faster by doing so.
Finish this thread here :
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=145&messageid=324845
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply