appending columns from table 1 to table 2

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

General SQL Server Forums

New to SQL Server Programming

appending columns from table 1 to table 2

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-05-17 : 15:12:23

Hi,

I tried using UNION and UNION ALL but realized that the table structure needs to be the same.

How else can I append columns from table 2 to table 1? Both tables have one common field but different structure as far as columns and data types are concerned. Example

Table 1

NUM
CITY

Table 2
NUM
ZIPCODE

I tried

CREATE TABLE Final
AS

SELECT
t1.*
FROM Table1 t1
UNION
SELECT t2.*
FROM Table2 t2

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-17 : 15:17:53

Without changing any underlying tables structures, just write out all the columns in your top part ,(no * !), and if there is no value for it, just put a null, and then do the same with the bottom part
Select col1,Col2,null as Col3,Col4
from table1
UNION
Select col2,null,col3,col4
from table2

Everyday I learn something that somebody else already knew

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2011-05-17 : 15:30:26

But the table strustures are different. What am I doing wrong? I get an error

"Ambiguous reference, column null is in more than one table."

SELECT
edw.ACCT_DIM_NB,
edw.TIME_DIM_NB,
edw.NOTE_DT,
edw.TOT_MO_BK_CN,
edw.BND_CYC_DLQ_CD,
edw.NetofContra,
edw.TOT_PRIN_BAL_AM,
null,
null,
null,
null
FROM mydata.edw as edw
UNION
SELECT
null,
null,
null,
null,
null,
null,
null,
edwperf.ACCT_DIM_NB,
edwperf.evr_2cyc_dq_me_6mob_in,
edwperf.evr_3cyc_dq_me_12mob_in,
edwperf.evr_4cyc_dq_me_12mob_in

FROM mydata.edw_perf as edw_perf

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-17 : 15:37:33

My guess is that you're dong a select into statement. As in my example, you need to name those null columns in the top to match with those below

Posted - 05/17/2011 : 15:30:26
--------------------------------------------------------------------------------

But the table strustures are different. What am I doing wrong? I get an error

"Ambiguous reference, column null is in more than one table."

SELECT
edw.ACCT_DIM_NB,
edw.TIME_DIM_NB,
edw.NOTE_DT,
edw.TOT_MO_BK_CN,
edw.BND_CYC_DLQ_CD,
edw.NetofContra,
edw.TOT_PRIN_BAL_AM,
null as ACCT_DIM_NB,
null as evr_2cyc_dq_me_6mob_in,
null etc.,
null
FROM mydata.edw as edw
UNION
SELECT
null,
null,
null,
null,
null,
null,
null,
edwperf.ACCT_DIM_NB,
edwperf.evr_2cyc_dq_me_6mob_in,
edwperf.evr_3cyc_dq_me_12mob_in,
edwperf.evr_4cyc_dq_me_12mob_in

FROM mydata.edw_perf as edw_perf

It would be better to create the table first and then insert into it, but if you don't that, then I would also suggest you
convert those null columns in the top to match their correspnding field below's data type

for example, CONVERT(int,null) as ACCT_DIM_NB etc.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources