Set default 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)

Set default value

Author

Topic

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-06-03 : 00:41:30

I have the following code that I need to modify so that if there are no records in T_Schedule_ProductionItems that @id starts at 1

[CODE]SELECT ItemID, ItemType, EquipDesc, identity(int, 1, 1) as id into #a FROM vSchedule_SchedulableItems ORDER BY ItemType, EquipDesc
declare @id int
select @id = max(ItemID) from T_Schedule_ProductionItems
INSERT INTO T_Schedule_BatchItems ( OrigID, ItemType, ItemDescription, ItemID )
select ItemID, ItemType, EquipDesc, @id + id
from #a[/CODE]

Basically what this does is append recrods from one table into another and at the same time inserts an incrementing number. It works as long as there are allready recrods in the table, but when it is blank then I don't get the incrementing number.

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-03 : 00:57:05

select @id = COALESCE(max(ItemID),1) from T_Schedule_ProductionItems

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-03 : 00:59:52

or
select @id = ISNULL(max(ItemID),1) from T_Schedule_ProductionItems

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-03 : 09:40:18

Why not make the ItemID column in T_Schedule_BatchItems an identity? If you really need to add the OldID to it to come up with a new ID, just use oldID as the primary key with the ItemID column as a candidate key.

Did that make sense? maybe a guru could put that into English for you.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-06-03 : 14:51:57

quote:
Originally posted by DonAtWork

Why not make the ItemID column in T_Schedule_BatchItems an identity? If you really need to add the OldID to it to come up with a new ID, just use oldID as the primary key with the ItemID column as a candidate key.

Did that make sense? maybe a guru could put that into English for you.

That makes sence, but does not apply in my case.

Michael
For all your DNC needs for your CNC machines. www.mis-group.com

mistux
Yak Posting Veteran

90 Posts

Posted - 2005-06-03 : 15:08:58

quote:
Originally posted by raclede

or
select @id = ISNULL(max(ItemID),1) from T_Schedule_ProductionItems

Thanks, that worked!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources