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.
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. cheersINSERT 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 |
|
|
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 + 1create function f_ts()returns intasbegin declare @t int select @t = max(id) + 1 from ttt Return @tEnd--destination tablecreate table ttt(col1 int, col2 varchar(20), id int)insert ttt Select 1, 'raju', 1 union allSelect 1, 'viju', 2 --source table tablecreate table rr(col1 int, col2 varchar(20))insert rr Select 5, 'visu' union allSelect 6, 'ram' --perform insert statementinsert tttSelect col1, col2, dbo.f_ts() from rr--------------------------------------------------S.Ahamed |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2007-07-27 : 02:22:20
|
Hi,Declare @Id IntSelect @id = max(ContactID) From tblSQLTest2Now insert all records into a temp tableDeclare @t table (I int identity(1, 1), Contact1 varchar, Position1 varchar, Email1 varchar, Tel1 varchar)Insert into @tSELECT Contact1, Position1, Email1, Tel1 FROM Company, tblSQLTest2Now insert this into u r main tableINSERT INTO [testwasp].[dbo].[tblSQLTest2]( [ContactID], [Name], [Title], [Email], [Tel]) SELECT @id + i, Contact1, Position1, Email1, Tel1 FROM @t |
|
|
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 :) |
|
|
|
|
|
|
|