| Author |
 Topic  |
|
|
terbs
Starting Member
29 Posts |
 Posted - 07/26/2007 : 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
India
319 Posts |
Posted - 07/27/2007 : 00:36:25
|
Explain it clearly with sample data
--------------------------------------------------
S.Ahamed
|
 |
|
|
pbguy
Constraint Violating Yak Guru
India
319 Posts |
Posted - 07/27/2007 : 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
|
|
|
|
PeterNeo
Constraint Violating Yak Guru
355 Posts |
Posted - 07/27/2007 : 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
|
|
|
|
terbs
Starting Member
29 Posts |
Posted - 07/30/2007 : 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 :) |
|
|
| |
Topic |
|
|
|
MAX(ID) + 1 when inserting old data to new table
