title: How to insert next max value using insert select statement
body: <p>I have a two tables. I want to insert table1 data into table2 if records from table1 are not present in the table2.</p>
  
  <p>I can do this. main problem with my query.</p>
  
  <pre><code> create table #Customer
  (
      ID int,
      Name nvarchar(20),
      Email nvarchar(20)
  )
  
  create table #Customer2
  (
      ID int,
      Name nvarchar(20),
      Email nvarchar(20),
      Value varchar(20)
  )
  
  insert into #Customer values (1,'AAA','A@mail.com')
  insert into #Customer values (2,'BBB','B@mail.com')
  insert into #Customer values (3,'CCC','C@mail.com')
  insert into #Customer values (4,'DDD','D@mail.com')
  insert into #Customer values (5,'EEE','E@mail.com')
  insert into #Customer values (6,'FFF','F@mail.com')
  insert into #Customer values (7,'GGG','G@mail.com')
  
  
  insert into #Customer2 values (3,'x','asa@mail.com','10001')
  insert into #Customer2 values (6,'y','B@mail.com','10002')
  insert into #Customer2 values (8,'z','z@mail.com','10003')
  
  
  
  update C2
  set C2.Email = C1.Email, C2.Name = C1.Name 
  from  #Customer C1
  inner join #Customer2 C2  on C2.ID = C1.ID
  
  insert into #Customer2 
  select C1.ID, C1.Name, C1.Email, (SELECT MAX(CONVERT(int, Value))+1 from #Customer2
  ) from  #Customer C1
  left join #Customer2 C2 on C2.ID = C1.ID
  where C2.ID is null
  
  
  select ID,value  from #Customer2
  
  drop table #Customer
  drop table #Customer2
  </code></pre>
  
  <p>The result is </p>
  
  <p>id  value</p>
  
  <p>3   10001</p>
  
  <p>6   10002</p>
  
  <p>8   10003</p>
  
  <p>1   10004</p>
  
  <p>2   10004</p>
  
  <p>4   10004</p>
  
  <p>5   10004</p>
  
  <p>7   10004</p>
  
  <p>I want it as </p>
  
  <p>id  value</p>
  
  <p>3   10001</p>
  
  <p>6   10002</p>
  
  <p>8   10003</p>
  
  <p>1   10004</p>
  
  <p>2   10005</p>
  
  <p>4   10006</p>
  
  <p>5   10007</p>
  
  <p>7   10008</p>
  
  <p>Please advice!</p>
  
  <p>Thanking you in anticipation.</p>