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)
 MAX(ID) + 1 when inserting old data to new table

Author  Topic 

terbs
Starting Member

29 Posts

Posted - 2007-07-26 : 20:41:14
I need my procedure to simply find the max of tblSQLTest2.ContactID and + 1 when fetching old data from table COMPANY.

I am unable to simply set the field to indentity as the table in under merge replication.

I know this code doesnts work, but it should give a clear impression of what I need.. Ive tried searching books online and msdn with no luck.

cheers


INSERT INTO [testwasp].[dbo].[tblSQLTest2]
(
[ContactID],
[Name],
[Title],
[Email],
[Tel]
)

SELECT
ContactID(MAX) + 1,
Contact1,
Position1,
Email1,
Tel1

FROM Company, tblSQLTest2

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-27 : 00:36:25
Explain it clearly with sample data

--------------------------------------------------
S.Ahamed
Go to Top of Page

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-27 : 00:52:56
try like this using a function which returns the maxid+1

--function returns maxid + 1
create function f_ts()
returns int
as
begin
declare @t int
select @t = max(id) + 1 from ttt
Return @t
End
--destination table
create table ttt(col1 int, col2 varchar(20), id int)
insert ttt
Select 1, 'raju', 1 union all
Select 1, 'viju', 2

--source table table
create table rr(col1 int, col2 varchar(20))
insert rr
Select 5, 'visu' union all
Select 6, 'ram'

--perform insert statement
insert ttt
Select col1, col2, dbo.f_ts()
from rr

--------------------------------------------------
S.Ahamed
Go to Top of Page

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-07-27 : 02:22:20
Hi,

Declare @Id Int

Select @id = max(ContactID) From tblSQLTest2

Now insert all records into a temp table

Declare @t table (I int identity(1, 1), Contact1 varchar, Position1 varchar, Email1 varchar,
Tel1 varchar)

Insert into @t
SELECT
Contact1,
Position1,
Email1,
Tel1

FROM Company, tblSQLTest2

Now insert this into u r main table


INSERT INTO [testwasp].[dbo].[tblSQLTest2]
(
[ContactID],
[Name],
[Title],
[Email],
[Tel]
)

SELECT
@id + i,
Contact1,
Position1,
Email1,
Tel1

FROM @t
Go to Top of Page

terbs
Starting Member

29 Posts

Posted - 2007-07-30 : 20:16:36
thank you both PB and Peter.

Peter your method worked perfectly, although I had to declare the size of the varchar in the declaration of the temporary table.

thanks again :)
Go to Top of Page
   

- Advertisement -