simple merge program

Question

simple merge program

Anand BI Developer

SSCommitted

Points: 1772
More actions
July 4, 2016 at 3:59 am

#319720

Hi friends ,
while i am running this simple merge program , there is an error like
' The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead '
-- i have write a codings also below for your refference :
create table merge_table(id int , name varchar(25) ,age int );
create table merge_table1(id int , name varchar(25) ,age int );
insert into merge_table values(1 , 'Anand' , 25) ;
insert into merge_table values(2 , 'BALA' , 25) ;
insert into merge_table values(3 , 'JP' , 25)
merge into merge_table1
using merge_table
on merge_table.id = merge_table1.id
when MATCHED then
update setmerge_table1.name = merge_table.name ,
merge_table1.age = merge_table.age
when not matched then
insert (merge_table1.id , merge_table1.name , merge_table1.age)
values (merge_table.id , merge_table.name , merge_table.age);

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 22924 More actions · Answer 1

Anandkumar-SQL_Developer (7/4/2016)
Hi friends ,
while i am running this simple merge program , there is an error like
' The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead '
-- i have write a codings also below for your refference :
create table merge_table(id int , name varchar(25) ,age int );
create table merge_table1(id int , name varchar(25) ,age int );
insert into merge_table values(1 , 'Anand' , 25) ;
insert into merge_table values(2 , 'BALA' , 25) ;
insert into merge_table values(3 , 'JP' , 25)
merge into merge_table1
using merge_table
on merge_table.id = merge_table1.id
when MATCHED then
update setmerge_table1.name = merge_table.name ,
merge_table1.age = merge_table.age
when not matched then
insert (merge_table1.id , merge_table1.name , merge_table1.age) --This line
values (merge_table.id , merge_table.name , merge_table.age);

You only need to list the column names that you are inserting, not their sources. If you change the line I've marked to be

insert (id , name , age)

It should work.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

Cause of the issue is the following query:

insert (merge_table1.id , merge_table1.name , merge_table1.age)

values (merge_table.id , merge_table.name , merge_table.age);

you need to change this into:

insert (id , name , age)

values (merge_table.id , merge_table.name , merge_table.age);

Complete will look like this:

merge into merge_table1

using merge_table

on merge_table.id = merge_table1.id

when MATCHED then

update set merge_table1.name = merge_table.name ,

merge_table1.age = merge_table.age

when not matched then

insert (id , name , age)

values (merge_table.id , merge_table.name , merge_table.age);

Anand BI Developer SSCommitted Points: 1772 More actions · Answer 3

Dear BWFC & Dear Twin.devil ,

Thanks a lot for your valuable Time . Now i understood clearly .., where i made mistake.