Adding two unique valued columns to table

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

SQL Server Administration (2000)

Adding two unique valued columns to table

Author

Topic

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-08-16 : 15:22:15

Hi All,

I need to add two columns in a table where it should store the values as below (it can be identity type columns)
There can ve many revisions for one ID. The RevisionID should start with 1 once the new ID is inserted.

ID RevisionID
1 1
1 2
1 3
1 4
2 1
2 2
3 1
4 1
4 2

Can any one tell me how to add these columns in a sql (2000) table.

Thanks,
-P

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-16 : 15:32:05

you mean a SUM of the columns?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-16 : 15:40:36

do u mean select (id + revisionid)
or sum(revisionid) group by ID
there are duplicate values in both of your columns so none of them can be identity columns

Ashley Rhodes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-16 : 15:54:40

You can use a subquery to to this. However, you must have a business rule to decide the order of RevisionID.

E 12°55'05.25"
N 56°04'39.16"

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2007-08-16 : 16:15:27

I am Sorry for providing insufficient information.

There are 2 tables table1 and table2
The design and the data storage in table1 would look like this
ID is Identity column
ID FileName ......
1 doc1
2 doc2
3 doc3
4 doc4

The design and the data storage of table2 would look like this
ID column is referenced as FK from table1

The data in RevisionID column should always start with 1 if the new ID is inserted.
ID RevisionID RevisionNotes ......
1 1
1 2
1 3
1 4
2 1
2 2
3 1
4 1
4 2

I hope it is clear now. Let me know if have any questions.

Thanks,
-P

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-16 : 16:32:05

this will have to be done with a trigger i guess. You need to post the table structre where nulls are allowed and where nulls are no allowed and how the data is inserted. is it from another table a file, or something else. Still there is not suffecient information to make sure there will be no bugs.

Ashley Rhodes

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-16 : 22:01:38

@ID is the ID you inserted into table1

INSERT INTO table2(ID, RevisionID)
SELECT t1.ID, ISNULL(MAX(t2.RevisionID), 0) + 1
FROM   table1 t1 left JOIN table2 t2
       ON t1.ID = t2.ID
WHERE  t1.ID = @ID
GROUP BY t1.ID

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources