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);

• Neil Burton

  SSC-Insane

  Points: 23087

  More actions

  July 4, 2016 at 4:18 am

  #1888257

  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

  July 4, 2016 at 4:58 am

  #1888263

  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

  July 4, 2016 at 5:36 am

  #1888277

  Dear BWFC & Dear Twin.devil ,

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