How to insert next max value using insert select statement
sql, sql-server, sql-server-2008, sql-server-2012, t-sql
Solution
You can use `Row_Number` starting with the maximum value in `Customer1` table to generate the ID's in customer 2 and the except operator to insert the data which is not present:
INSERT INTO #Customer2(ID,Name,Email,Value)
select ID,name,email, (SELECT MAX(value) from #customer2) + row_number() over (order by id) value
from (select ID,Name,email from #Customer
except SELECT ID,name,email from #Customer2)ValueForInserting
Problem
I have a two tables. I want to insert table1 data into table2 if records from table1 are not present in the table2. I can do this. main problem with my query. ``` 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 ``` The result is id value 3 10001 6 10002 8 10003 1 10004 2 10004 4 10004 5 10004 7 10004 I want it as id value 3 10001 6 10002 8 10003 1 10004 2 10005 4 10006 5 10007 7 10008 Please advice! Thanking you in anticipation.