Increment a field starting with the same number everytime.

Question

Post reply

Increment a field starting with the same number everytime.

Ken at work

Hall of Fame

Points: 3426
More actions
July 23, 2010 at 1:21 pm

#97242

I have data in a table that I "reformat" and place into
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Temp](
[Status] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Co_No] [int] NOT NULL,
[Div_No] [int] NOT NULL,
[Seq] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_tbl_PRTIND_Temp] PRIMARY KEY CLUSTERED
(
[Co_No] ASC,
[Div_No] ASC,
[Seq] ASC,
) ON [PRIMARY]
) ON [PRIMARY]
I use this table to insert "from" into a table on a linked server.
I delete the data in the table.
Insert data into this table and start over.
You ask, why does he insert data into a table before inserting it into the final table. Good question.
I need to increment the Seq Field, and short of doing a loop (RBAR) this seemed way faster.
But each time I do this I need the Seq Field to start at 1 (one) again and it doesn't (always).
Can I accmoplish this?
I appreciate your help,
Thank you,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Transfer all the data and then TRUNCATE the table

From TRUNCATE TABLE in Books On Line

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

Or if you can not TRUNCATE use this:

Again from BOL

DBCC CHECKIDENT

(

'table_name'

[ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]

)

[ WITH NO_INFOMSGS ]

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

Or just drop this approach, and use the row_number() windowed function to insert into the other DB, everytime using a new sequential number, based on your requirement.

Cheers,

J-F

Ken at work Hall of Fame Points: 3426 More actions · Answer 3

The RowNumber() approach looks nice.

Is that a Server 7 command? I'm having trouble with it.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 4

Ken@Work (7/26/2010)
The RowNumber() approach looks nice.
Is that a Server 7 command? I'm having trouble with it.

Sadly, no, it's a sql server 2005 function. Sorry to have mislead you, I did not see it was for a sql 7 server.

I would say that your best approach would be to truncate the table, and insert again.

Sorry again,

Cheers,

J-F

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

J-F Bergeron (7/26/2010)
Ken@Work (7/26/2010)
The RowNumber() approach looks nice.
Is that a Server 7 command? I'm having trouble with it.
Sadly, no, it's a sql server 2005 function. Sorry to have mislead you, I did not see it was for a sql 7 server.
I would say that your best approach would be to truncate the table, and insert again.
Sorry again,

For what it's worth, I agree... truncating the table would be the easiest for SQL Server 7 and 2000.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ken at work Hall of Fame Points: 3426 More actions · Answer 6

Yeah! Works great, thanks.

Okay, another question.

What if the table is like this?

CREATE TABLE [dbo].[tbl_Temp](

[strStatus] [nvarchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[intCo] [int] NOT NULL,

[intDiv] [int] NOT NULL,

[intGroup] [int] NOT NULL,

[intSeq] [int] NOT NULL,

[intID_No] [int] NOT NULL,

) ON [PRIMARY]

What if, please I hope so...

I need Group indexed by each change in ID_NO, and Seq to start at 1 and increment for each record that matches the ID_No.

I can Order By to the ID_No are together, that's easy enough or PK the ID_No.

Is this doable?

Thank again,

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

Ken@Work

You will be more likely to receive a tested answer by:

1. Posting you last question to a new forum

Including the table structure as you have in your last posting

2. Provide some sample data.

3. Sample of required output.

May I recommended that you follow the suggestions for posting by clicking on the first link in my signature block for the proper format

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]