CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
Dattatray Kale
Dattatray Kale

Reputation: 81

How to insert next max value using insert select statement

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','[email protected]')
insert into #Customer values (2,'BBB','[email protected]')
insert into #Customer values (3,'CCC','[email protected]')
insert into #Customer values (4,'DDD','[email protected]')
insert into #Customer values (5,'EEE','[email protected]')
insert into #Customer values (6,'FFF','[email protected]')
insert into #Customer values (7,'GGG','[email protected]')


insert into #Customer2 values (3,'x','[email protected]','10001')
insert into #Customer2 values (6,'y','[email protected]','10002')
insert into #Customer2 values (8,'z','[email protected]','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.

Upvotes: 4

Views: 25594

Answers (1)

A.J
A.J

Reputation: 382

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

Upvotes: 3

Related Questions