Click here to Skip to main content
14,666,389 members
Rate this:
Please Sign up or sign in to vote.
Hi All

I am finding the duplicates rows from two tables and
inserting into temptable

When i used this query to search duplicate rows its giving me result successfully.
as i want ,
======
SELECT * FROM
TrnBal , ullr
WHERE TrnBal.LR_no = ullr.LR_No

=======

But when try to insert the same result into temptable its giving me error.

here is my second query ,

===
Insert into duprow (LR_No,Lr_Date ,Bkg_Stn ,FrmShort ,Dst,ToShort,Consignor_Name,
Consignee_Name ,Pvt_Mark ,Discription ,Article,Weight,Value ,Pmt_Mode ,DoorDel, LR_Remark, Freight ,BC ,Handling ,DDel , LC ,Others ,S_Tax ,Amount ,Initial ,Truck_No , Report_No, Rep_Date ,Trk_From , Trk_To  , Arv_No ,Arv_Date ,L_Stat)
Select LR_No,Lr_Date ,Bkg_Stn ,FrmShort ,Dst ,ToShort ,Consignor_Name,
Consignee_Name ,Pvt_Mark, Discription ,Article,Weight,Value ,Pmt_Mode ,DoorDel ,LR_Remark, Freight ,BC ,Handling ,DDel , LC ,Others ,S_Tax ,Amount ,Initial ,Truck_No ,Report_No ,
Rep_Date ,Trk_From , Trk_To  , Arv_No ,Arv_Date ,L_Stat
FROM TrnBal,ullr
Where TrnBal.LR_No = ullr.LR_No

==============

ERROR :
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'LR_No'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'Lr_Date'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'Bkg_Stn'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'FrmShort'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'Dst'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'ToShort'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'Consignor_Name'.......more.... with all field.

Guys i am stuck in Please help me ,
Any Help will be highly appreciated..

Waiting for your reply....????
Thanks in Advance.
Posted
Updated 10-Mar-12 0:17am
v3

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 2

The columns LR_No, Lr_Date etc. may be present in both the tables and you are writing

FROM TrnBal,ullr

in the Select statement, which gives rise to the above error.

So specify the Table for the columns which are present in both tables, say

Select TrnBal.LR_No, TrnBal.Lr_Date
   
v2
Comments
gufran90 10-Mar-12 13:45pm
   
Thanks for your support @ProEnggSoft

Yes , Excellent

Your are right sir , its working..

Below i am giving the query as per your suggestion.
====
Insert into duprow (LR_No,Lr_Date ,Bkg_Stn ,FrmShort ,Dst ,ToShort,Consignor_Name,
Consignee_Name ,Pvt_Mark ,Discription,Article,Weight,Value ,Pmt_Mode ,DoorDel ,
LR_Remark ,Freight ,BC ,Handling ,DDel , LC ,Others ,S_Tax ,Amount ,Initial ,
Truck_No , Report_No ,Rep_Date ,Trk_From , Trk_To , Arv_No ,Arv_Date ,L_Stat)
Select TrnBal.LR_No,TrnBal.Lr_Date ,TrnBal.Bkg_Stn ,TrnBal.FrmShort ,TrnBal.Dst ,TrnBal.ToShort ,TrnBal.Consignor_Name,TrnBal.Consignee_Name ,TrnBal.Pvt_Mark ,TrnBal.Discription,TrnBal.Article,TrnBal.Weight,TrnBal.Value,TrnBal.Pmt_Mode,
TrnBal.DoorDel ,TrnBal.LR_Remark ,TrnBal.Freight ,TrnBal.BC ,TrnBal.Handling ,TrnBal.DDel ,TrnBal.LC ,TrnBal.Others ,TrnBal.S_Tax ,TrnBal.Amount ,TrnBal.Initial ,
TrnBal.Truck_No ,TrnBal.Report_No ,TrnBal.Rep_Date ,TrnBal.Trk_From , TrnBal.Trk_To , TrnBal.Arv_No ,TrnBal.Arv_Date ,TrnBal.L_Stat
FROM TrnBal,ullr
Where TrnBal.LR_No = ullr.LR_No

=========

Thanks a lot.... And Gold Bless You.....
ProEnggSoft 10-Mar-12 22:04pm
   
Happy to know that the above solution worked.
As seen from your query above you want to select columns from TrnBal table only, in that case, you can use Column names with out Table name and giving only TrnBal table name in From clause as below:
Select LR_No, Date, Bkg_Stn, ..... From TrnBal Where TrnBal.Lr_No = ullr.LR_No

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100