increment section number in a table by 1

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 2008 Forums

Transact-SQL (2008)

increment section number in a table by 1

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-22 : 11:18:10

hi i have this code


INSERT  INTO dbo.BNYWorkingSecTable
        ( SECTION)
        SELECT  SECTION
        FROM    dbo.GLAccounts1
        WHERE   NOT EXISTS ( SELECT *
                             FROM   dbo.BNYWorkingSecTable
                             WHERE  dbo.BNYWorkingSecTable.SECTION = dbo.GLAccounts1.SECTION

it writes what i want to the table but the section number is null. how can i get it to increment value by 1 every time it adds data to database.
also there is data in table so have to continue on from the number thats there

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-22 : 11:43:31

You could use an IDENTITY type that auto-increments on the table definition

e.g.


CREATE ... (..., SECTION int identity(1,1), ...)

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2014-05-22 : 11:47:21

if the table has data
eg

1
2
3
4
5
if u use the indentify will it add in the 6 or cause problems?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-22 : 12:44:48

it would add 6. Best to read up on it and try it out on a temp table to get comfortable

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-22 : 13:21:23

You can't alter an existing column to add or remove identity.
However you can create a new table with same structure but with [section] as identity. Then insert the actual values into the new table, then drop the old table, then rename the new table back to the old name.

to insert values into an identity column you need to:
set identity_insert <newTable> ON

then set it off again once the insert is complete.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources