SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 MAX(ID) + 1 when inserting old data to new table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

terbs
Starting Member

29 Posts

Posted - 07/26/2007 :  20:41:14  Show Profile  Reply with Quote
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

India
319 Posts

Posted - 07/27/2007 :  00:36:25  Show Profile  Reply with Quote
Explain it clearly with sample data

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

pbguy
Constraint Violating Yak Guru

India
319 Posts

Posted - 07/27/2007 :  00:52:56  Show Profile  Reply with Quote
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 - 07/27/2007 :  02:22:20  Show Profile  Reply with Quote
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 - 07/30/2007 :  20:16:36  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000