469,917 Members | 1,834 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

How to increment a field for a Line Number?

2
Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server
Jul 8 '07 #1
5 5681
srinit
43
Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server
HI ,
Can you tell me clearly.please send how you tried it
Jul 9 '07 #2
What is the relationship between the two columns ?

I don't think SQL Server 2000 will allow you to use the result of a Stored/Procedure / User Defined Function as the default value for a column.

SQL Server 2005 might.
Jul 9 '07 #3
TrentC
2
Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.


Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE @LineNum INT

BEGIN

SELECT @LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END
Jul 9 '07 #4
Infide
28
Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.


Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE @LineNum INT

BEGIN

SELECT @LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END
It looks like you are trying to replicate the functionality of an identity column.

Unless the value in your line_number column is directly related to the information in that record just make your line_number column an int column with identity turned on and seed at +1.
Jul 10 '07 #5
DonlonP
25
Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server
Hi,

I recently had to do the same thing, if your table has an identity column (i.e. primary key that is incremented automatically when you insert a new row), which all tables should, then you can use the following method:

First of all insert all your DocNum records into the table so that the identity column (ID say) is updated automatically. Then update the LineNum field as follows:

UPDATE
[TABLE_NAME]
SET
LineNum = (SELECT
COUNT(*)
FROM
[TABLE_NAME] t1
WHERE
t1.ID <= [TABLE_NAME].ID
AND
t1.DocNum = [TABLE_NAME].DocNum
)
Jul 18 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Tom | last post: by
1 post views Thread by Jim Bowe | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.