January 4, 2006 at 10:22 am
I have 2 tables A & B where they have a common column Qtr. I want to move records from B(Qtr) to A(Qtr) where
1.table A has a composite key consisting of 3 PK's.
2. No. of records in each table are different.
3. Table A(Qtr) has a default value of 0.
January 4, 2006 at 10:44 am
I'm not clear on what you need to do here. Can you provide table DDL and sample data for tables A and B. Sample data for how you want your data to appear after the move would be useful also.
January 4, 2006 at 11:13 am
Table A X(PK) Y(PK) Z(PK) Qtr(default=0)
Table B M(PK) Qtr - -
No of rows in both tables are diferent. I want to move data in Qtr column of B to Qtr column of A.
January 4, 2006 at 12:45 pm
That's not what John asked for....it will help if you provide us the schema (column names) for each table and some sample data for each table and a result that you want.
For example:
Table A
Col1 Col2 Col3 Qtr
x y z 0
Table B
Col1 Qtr
m 5
Result:
Table A
Col1 Col2 Col3 Qtr
x y z 0
m 5
For that, I would suggest:
INSERT INTO TableA (ColA, Qtr)
SELECT ColA, Qtr
FROM TableB
But with the three columns making a PK, that makes it tougher. Which is why an example helps.
-SQLBill
January 4, 2006 at 1:26 pm
Table A
Col1 Col2 Col3 Qtr
1 2004-05 BOG 0
2 2004-05 BANT 0
3 2003-04 BOG 0
Table B
Col1 Col2 Col3 Qtr
1 2004-05 BOG 367.5
2 2004-05 BANT 12.3
3 2003-04 BOG 55
4 2004-05 BOG 7120
Result: Table A
Col1 Col2 Col3 Qtr
4 2004-05 BOG 7120
2 2004-05 BANT 12.3
3 2003-04 BOG 55
I hope its clear to understand now. What all I need is update Qtr column in table A with Qtr column in table B also shud match all other columns(col1,col2,col3) where these 3 cols togetger is a PK.
January 4, 2006 at 1:37 pm
It's not really an INSERT then, like the thread title suggests ? It's a correlated UPDATE ?
Update A
Set Qtr = B.Qtr
From TableA As A
Inner Join TableB As B
On (A.Col1 = B.Col1 And
A.Col2 = B.Col2 And
A.Col3 = B.Col3)
January 5, 2006 at 5:58 am
when I used the above given query I am getting the folling error, Any help?
Server: Msg 107, Level 16, State 3, Line 1
The column prefix TableB does not match with a table name or alias name used in the query.
January 5, 2006 at 7:15 am
Of course you got that error. Your example shows the table names are TABLE A and TABLE B. PW provided you an EXAMPLE using those table names...YOU need to replace the table and column names with the REAL table and column names.
-SQLBill
January 5, 2006 at 8:33 am
Hey...its Ok, i understand that.
As I dont want to reveal my original names so used in tht way. Any help for that error?
January 5, 2006 at 9:56 am
Any help for that error?
YES. As I said:
YOU need to replace the table and column names with the REAL table and column names.
-SQLBill
January 5, 2006 at 9:59 am
Ah I think I mispoke and apologize for that. I think you are saying you DID replace the names and got that error and for our viewing you replaced the real name with the 'fake' name.
Run the command in Query Analyzer, when you get the error double click on the error and see if it shows which line the error is in.
-SQLBill
January 5, 2006 at 10:06 am
Here is something else that might help.....
Copy the query from QA into a .txt file (Notepad). Then use find and replace. First replace all occurances of your first tablename with TABLEA. Then replace all occurances of your second tablename with TABLEB. Do not do this manually (that way we can spot any typos).
If your aliases are not A and B, do the replace for them also. Example Replace alias TBA with A.
Do the same for the column names. Remember, don't manually do this. Find and replace will help you and us spot typos.
Then copy and paste that result into the reply window and post it here.
-SQLBill
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply