Move data from one table another based on column value

Question

Move data from one table another based on column value

milan-428297

SSC Veteran

Points: 230
More actions
November 14, 2008 at 7:47 am

#383388

Hi all,
Could you please help me to move data from one table to another? It's a bit tricky.
a. Table A has 2 colums
1.map 2. Amount
B. Table B has 3 columns
1. TAmount 2. GAmount 3. PAmount
Now depeding upon the value of "map" column, data needs to be moved to either TAmount or GAmount or PAmount.
e.g if "map" = T then TAmount columns is filled and rest are null
if "map" = G then GAmount columns is filled and rest are null/empty
Any suggestion is appriciated? I am looking a solution based on
INSERT INTO TableB(TAmount,GAmount,PAmount)
SELECT
....
FROM
TableA
Thanks.
Milan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

You CAN do this with the following code. However, please note that it's not necessarily a good idea. Can you explain the reason this is needed?

INSERT INTO TableB(TAmount,GAmount,PAmount)

SELECT

CASE WHEN A.Map = 'T' THEN A.Amount ELSE NULL END,

CASE WHEN A.Map = 'G' THEN A.Amount ELSE NULL END,

CASE WHEN A.Map = 'P' THEN A.Amount ELSE NULL END

FROM TableA A

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 2

milan (11/14/2008)
Hi all,
Could you please help me to move data from one table to another? It's a bit tricky.
a. Table A has 2 colums
1.map 2. Amount
B. Table B has 3 columns
1. TAmount 2. GAmount 3. PAmount
Now depeding upon the value of "map" column, data needs to be moved to either TAmount or GAmount or PAmount.
e.g if "map" = T then TAmount columns is filled and rest are null
if "map" = G then GAmount columns is filled and rest are null/empty
Any suggestion is appriciated? I am looking a solution based on
INSERT INTO TableB(TAmount,GAmount,PAmount)
SELECT
....
FROM
TableA
Thanks.
Milan

So I guess you need a better understanding of CASE statements, since you were so close!

Just in case PAmount ever needs to be filled, I prepared that also, but you could that last CASE statement just with NULL as desired.

INSERT INTO TableB(TAmount,GAmount,PAmount)

SELECT CASE map WHEN 'T' THEN Amount ELSE NULL END,

CASE map WHEN 'G' THEN Amount ELSE NULL END,

CASE map WHEN 'P' THEN Amount ELSE NULL END

FROM

TableA

Ronald

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

milan-428297 SSC Veteran Points: 230 More actions · Answer 3

Seth,

Many thanks.

This is needed as I have a raw table imported from excel sheet using SSIS package which needs to be moved to another table.

Thanks again for the help. Very much appreciated.

Regards,

Milan G