sno column auto increment via insert command

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

General SQL Server Forums

New to SQL Server Programming

sno column auto increment via insert command

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2012-01-08 : 16:42:43

DimCompany

CREATE TABLE [PUOds].[dimCompanyCode](
[SID] [int] NOT NULL,
[CompanyCode] [nvarchar](12) NOT NULL)

INSERT INTO [dimCompany]
( SID , CompanyCode, CompanyName)

SELECT DISTINCT
(select MAX( SID) from dimcompany ) + 1 as sid
,LTRIM(RTRIM(CompanyCodeOriginal)) as CompanyCode
FROM FF_Company)

On every insert, i need the max of sid to be inserted each time

ie :
if i have
1 company1
2 company2

i shoule be able to insert
3 company3
4 company4

the sno should be instered using insert command as the sid coulmn is not identity, kindly help

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-08 : 16:57:01

Can you make that column identity?

It's possible to roll your own identity column, but it generally either causes performance problems or correctness issues.

--
Gail Shaw
SQL Server MVP

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2012-01-08 : 17:00:27

I dont have rights to change the existing table structure. so i have to work on the same. kindly help me

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-08 : 17:29:56

If you cripple insert performance into this table, is that acceptable? If not, can you create another table?

p.s. Not having rights doesn't mean it can't be done, someone has to have the rights. Whether or not it can be changed is not a factor of your individual permissions, it's a architectural decision. So, can this be changed to identity? Or will doing so break lots of other code?

--
Gail Shaw
SQL Server MVP

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2012-01-08 : 17:30:59

ok thanks, i will dicuss the same with my leads for making it identity.

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 23:17:15

see how you can modify an existing column to be of type identity

http://www.mssqltips.com/sqlservertip/1397/add-or-drop-identity-property-for-an-existing-sql-server-column/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources