January 31, 2012 at 2:00 am
I have the tables with the below structure
Table 1 Table 2 Table 3
ColumnsID ,SINID,AgentCode ID, Amount
Table1 data
ID -- SIN
1 123456789
2 123400999
3 123123123
Table2 data
ID -- AgentCode
1 AG123
2 AG121
3 AG124
Table3 data
ID -- Amount
1 100.90
2 200.80
3 134.90
Now i want to combine all columns to a single table like below
Table Table4
ID -- SIN AgentCode Amount
1 123456789 AG123 100.90
2 123400999 AG121 200.80
3 123123123 AG124 134.90
Can any one suggest how to acheive the above result set
January 31, 2012 at 2:14 am
You need to read about JOIN - http://msdn.microsoft.com/en-us/library/ms191517.aspx and look at the column that each table has in common.
January 31, 2012 at 2:20 am
Can any one can give a live example of the scenario
January 31, 2012 at 2:32 am
I believe something like this will do the trick.
CREATE TABLE #table1 (ID INT,SIN_ID INT,AgentCode_ID VARCHAR(30), Amount DECIMAL(5,2))
CREATE TABLE #table2 (ID INT,SIN_ID INT,AgentCode_ID VARCHAR(30), Amount DECIMAL(5,2))
CREATE TABLE #table3 (ID INT,SIN_ID INT,AgentCode_ID VARCHAR(30), Amount DECIMAL(5,2))
CREATE TABLE #table4 (ID INT,SIN_ID INT,AgentCode_ID VARCHAR(30), Amount DECIMAL(5,2))
INSERT INTO #table1 (id, SIN_ID)
VALUES (1, 123456789),
(2, 123400999),
(3, 123123123)
INSERT INTO #table2 (id, AgentCode_ID)
VALUES (1, 'AG123'),
(2, 'AG121'),
(3, 'AG124')
INSERT INTO #table3 (id, Amount)
VALUES
(1,100.90),
(2,200.80),
(3,134.90)
INSERT INTO #table4 (id, sin_id, agentcode_id, amount)
SELECT t1.id, t1.sin_id, t2.agentcode_id, t3.amount
FROM #table1 t1
INNER JOIN #table2 t2
ON t1.id = t2.id
INNER JOIN #table3 t3
ON t3.id = t1.id
--if the id already exists in your target table you could use a merge like so:
MERGE #table4 as trg
USING (SELECT t1.id, t1.sin_id, t2.agentcode_id, t3.amount
FROM #table1 t1
INNER JOIN #table2 t2
ON t1.id = t2.id
INNER JOIN #table3 t3
ON t3.id = t1.id) as src
ON trg.id = src.id
WHEN MATCHED AND (trg.sin_id != src.sin_ID OR trg.AgentCode_ID != src.AgentCode_ID OR trg.amount != src.amount) THEN
UPDATE SET trg.sin_Id = src.sin_id,
trg.agentcode_id = src.agentcode_id,
trg.amount = src.amount
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, sin_id, agentcode_id, amount)
VALUES (id, sin_id, agentcode_id, amount);
Alternatively you could use the sql MERGE statement to MERGE table1 into table2, then MERGE table2 into table3 then MERGE table3 into table4 - not the best way though.
January 31, 2012 at 2:34 am
Specifically you need to look at INNER JOIN, and the ID column is what you would use in the 'ON' part of that command.
Seriously, read up on this area (you really need to understand this -it is fundamental), give it a go and come back with any questions if you have problems.
January 31, 2012 at 2:37 am
Loundy (1/31/2012)
I believe something like this will do the trick.Alternatively you could use the sql MERGE statement to MERGE table1 into table2, then MERGE table2 into table3 then MERGE table3 into table4 - not the best way though.
Genius - why didn't I see that solution:-)
January 31, 2012 at 9:00 am
:Whistling:
SELECT id, CONVERT(INT,SIN_ID) AS SIN_ID, CONVERT(VARCHAR(30),AgentCode_ID) AS AgentCode_ID,
CONVERT(DECIMAL(5,2),Amount) AS Amount
FROM (SELECT id, CONVERT(VARCHAR(200),SIN_ID), 'SIN_ID'
FROM #table1
UNION ALL
SELECT id, CONVERT(VARCHAR(200),AgentCode_ID), 'AgentCode_ID'
FROM #table2
UNION ALL
SELECT id, CONVERT(VARCHAR(200),Amount), 'Amount'
FROM #table3) a(id,col,type)
PIVOT (MAX(col) FOR type IN ([SIN_ID],[AgentCode_ID],[Amount])) AS pvt
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply