Insert with dynamic value - 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 2000 Forums

Transact-SQL (2000)

Insert with dynamic value

Author

Topic

sahu74
Posting Yak Master

100 Posts

Posted - 2004-10-05 : 11:34:38

I have to insert a new record


INSERT INTO Header(HeaderName, UID, CID, DID,HeaderOrder)
VALUES('xyz',5,2,22,???)

The value of HeaderOrder will be =


SELECT Max(HeaderOrder)+1
FROM Header
WHERE UID=5 and CID=2 and DID =22

How can I achieve this in a single Insert statement?

Thank you.
PKS.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-10-05 : 11:38:32

[CODE]
INSERT INTO Header(HeaderName, UID, CID, DID,HeaderOrder)
SELECT 'xyz', 5, 2, 22, Max(HeaderOrder)+1
FROM Header
WHERE UID=5 and CID=2 and DID =22
[/CODE]

--******************************************************

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-05 : 11:39:57

try this:
INSERT INTO Header(HeaderName, UID, CID, DID,HeaderOrder)
select 'xyz',5,2,22, (SELECT Max(HeaderOrder)+1 FROM Header WHERE UID=5 and CID=2 and DID=22)

EDIT:

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-05 : 12:15:12

or just make header order an identity column and leave it out of the list?

Brett

8-)

sahu74
Posting Yak Master

100 Posts

Posted - 2004-10-05 : 14:48:07

Thank you all. It works great. I couldnot make the Header Order an Identity insert since the order repeats depending on the UID, CID and DID.

Thanks again.
PKS.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources