Auto generation of Identity column

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

Transact-SQL (2005)

Auto generation of Identity column

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-06-28 : 00:39:48

Dear All,
I need to generate the Identity column value from max value of my own.
Please suggest how to generate the identity column

select I.*,C.* --into Map5days_interactions_callnotes
from callnotes_MSD_AU C,Interactions_MSD_AU I
where c.CONTACT_ID = I.HCP_ID
and C.NOTE_DT between dateAdd(dd, -5, I.Interaction_date) and dateAdd(dd, 5, I.Interaction_date)
and C.Team_Name = I.TeamName
and C.territory_position = I.callBy

Thanks,
Gangadhara MS
SQL Developer and DBA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-28 : 02:26:16

-- For an insert
declare @offset int
select @offset = isnull(max(id),0) from destination_table

insert destination_table(id, col2, col3, ...)
select
row_number() over (order by (select 1)) + @offset as id,
col2,
col3,
...
from source_table

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources