problem with insert statement - 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 2008 Forums

Transact-SQL (2008)

problem with insert statement

Author

Topic

new_developer
Starting Member

18 Posts

Posted - 2011-01-06 : 06:26:59

hi
i have one table call it Session have one column
call it SessionID
i want insert one record indide it
like this

insert into Session(SessionID) values(SessionID + 1)

but it give me error
any one can modify to me this statement
thanks

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-01-06 : 06:41:51

quote:
Originally posted by new_developer

hi
i have one table call it Session have one column
call it SessionID
i want insert one record indide it
like this
insert into Session(SessionID) values(SessionID + 1)

but it give me error
any one can modify to me this statement
thanks

The following will work:


INSERT INTO [Session]
SELECT MAX(SessionID) + 1
FROM [Session] WITH (UPDLOCK, SERIALIZABLE)

You may want to post what you are actually doing as code like this is not usually a good idea.

[Edit]: If SessionId is an IDENTITY then use the following:


INSERT INTO [Session] DEFAULT VALUES

new_developer
Starting Member

18 Posts

Posted - 2011-01-06 : 09:08:47

friend ok now i add one record
but i want to retrive same record inside
the statement can u tell me how can i retrive
the record i inserted it
thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-06 : 10:15:24

You can only do that inside of a trigger on the table.

Jim

Everyday I learn something that somebody else already knew

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-01-06 : 11:47:54

quote:
Originally posted by new_developer

friend ok now i add one record
but i want to retrive same record inside
the statement can u tell me how can i retrive
the record i inserted it
thanks

IN SQL2008 use the OUTPUT clause:


DECLARE @output TABLE
(
	SessionID bigint NOT NULL
)

INSERT INTO [Session]
OUTPUT inserted.SessionID INTO @output
SELECT MAX(SessionID) + 1
FROM [Session] WITH (UPDLOCK, SERIALIZABLE)

SELECT *
FROM @output

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources