How do insert columndata to a table frm another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How do insert columndata to a table frm another

Author

Topic

svgeorge
Starting Member

23 Posts

Posted - 2007-09-18 : 09:44:05

How do I insert column records to a table from another table

There table i want to update is Payment_LIST_AIMS and the column is Enrollment_ID
from table V_1st_Enrollment_Agency_Payment_List and column from where data to get in this table is Enrollment_ID
the data needs to be updated in Payment_LIST_AIMS WHERE(Payment_LIST_AIMS.Payment_Type = '1') OR
(Payment_LIST_AIMS.Payment_Type = '10') OR
(Payment_LIST_AIMS.Payment_Type = '19') OR
(Payment_LIST_AIMS.Payment_Type = '30') OR
(Payment_LIST_AIMS.Payment_Type = '39') OR
(Payment_LIST_AIMS.Payment_Type = '48') OR
(Payment_LIST_AIMS.Payment_Type = '57')

Pheale help
George

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-18 : 09:47:36

What's the common key between these two tables?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

svgeorge
Starting Member

23 Posts

Posted - 2007-09-18 : 09:50:19

Payment_LIST_AIMS.JC_ID and V_1st_Enrollment_Agency_Payment_List.JC_ID

Please help
George

svgeorge
Starting Member

23 Posts

Posted - 2007-09-18 : 09:53:04

Is this correct

INSERT INTO Payment_LIST_AIMS(Enrollment_ID)(select Enrollment_ID from V_1st_Enrollment_Agency_Payment_List)
WHERE(Payment_LIST_AIMS.JC_ID=V_1st_Enrollment_Agency_Payment_List.JC_ID)

i get there error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WHERE'.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-18 : 10:08:25

If there are already records and you just want to update single column, you will have to use UPDATE instead of INSERT.

UPDATE T1
SET Enrollment_ID = T2.Enrollment_ID
FROM Payment_LIST_AIMS T1 JOIN V_1st_Enrollment_Agency_Payment_List T2
ON T1.JC_ID = T2.JC_ID
WHERE
T1.Payment_Type IN ('1', '10', '19', '30', '39', '48', '57')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

svgeorge
Starting Member

23 Posts

Posted - 2007-09-18 : 10:22:42

I used qery below...
but only one row is updated where as i have (1576 row(s) affected)--from this query where data has to be updated from T2(SELECT Payment_ID, JC_ID, Payment_Type, AGENCY_ID, Agency_approval, Agency_approval_date, Payment_Updated, Provider, UserName,
Support_Retention_ID, Placement_ID, Enrollment_ID
FROM Payment_LIST_AIMS
WHERE (Payment_Type = '1') OR
(Payment_Type = '10') OR
(Payment_Type = '19') OR
(Payment_Type = '30') OR
(Payment_Type = '39') OR
(Payment_Type = '48') OR
(Payment_Type = '57')

///////////////////////

UPDATE Payment_LIST_AIMS
SET Enrollment_ID = V_1st_Enrollment_Agency_Payment_List.Enrollment_ID
FROM Payment_LIST_AIMS JOIN V_1st_Enrollment_Agency_Payment_List
ON Payment_LIST_AIMS.JC_ID = V_1st_Enrollment_Agency_Payment_List .JC_ID
WHERE
Payment_LIST_AIMS.Payment_Type IN ('1', '10', '19', '30', '39', '48', '57')

svgeorge
Starting Member

23 Posts

Posted - 2007-09-18 : 11:26:55

THANKS
I SOLVED IT MY TABLE NAME WAS WRONG YOUR SYNTAX WAS CORRECT IT WORKED

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources