Quetions about inserting data from two tables into one

Question

Quetions about inserting data from two tables into one

itmasterw 60042

SSCrazy

Points: 2858
More actions
January 14, 2019 at 12:44 pm

#381183

HI,
have two tables with similar column name but not totally the same. For example, on table have ABA the other has ABA_no.
Then, one table has fields that are not in the other and vice versa. So, we want to make a new table that will have all the fields from each of these two tables.
Each table has records that the other does not and each has the same unique ID.
The question that I have is how do I have is how do I then insert that data from these two tables into this new one?
Any ideas would be appreciated
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

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

goher2000 SSCertifiable Points: 5479 More actions · Answer 1

careful with the col order

create table t1 (c1 int, c2 int, c5 int);
create table t2 (c3 int, c4 int , c6 int);
create table t3 (c1 int, c2 int, c5 int,c3 int, c4 int , c6 int)

insert into t1 values (1,2,5);

insert into t2 values (3,4,6);

select * from t1;
select * from t2;

insert into t3 (c1 , c2 ,c3 , c4 ,c5, c6)
select c1, c2, null,null,c5,null from t1 union
select null,null,c3,c4,null,c6 from t2

select * from t3

drop table t1
drop table t2
drop table t3

drew.allen SSC Guru Points: 76944 More actions · Answer 2

You haven't given very much detail, and what you have given has a very basic answer, so I feel like you've left out some important details. Perhaps if you tell us what you've already tried and the problems you ran into, we might be able to help more.

Otherwise, the simple answer is to treat them as two completely separate statements.
1) INSERT/MERGE from the first table.
2) INSERT/MERGE from the second table.

There are also methods to combine the two tables into a single result set which is then inserted, but we don't have the data to choose between those methods.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 3

To Dew (SSC Guru)
Sorry if I left out anything. If you can tell me what you might need I would be happy to share it.
I have not tried anything yet I am in the planning stages; and not sure how to do this.
Both tables that exist are fairly wide. But my real question is if you have a column (and I have many like this) that is one name and the other has a different name will I be able to insert the data into the third table even
if I pick one of the table names for that new table.

Thank you

drew.allen SSC Guru Points: 76944 More actions · Answer 4

itmasterw 60042 - Tuesday, January 15, 2019 5:20 AM
I have not tried anything yet I am in the planning stages; and not sure how to do this.

That is the crux of your problem. You haven't tried anything, yet. You learn best by trying things. Once you've tried things you can also be a LOT more specific about where you ran into problems. I don't know how you expect to plan things if you don't even have basic knowledge about how things work. Stop planning and try things. Once you do, you'll have a much better idea about what is possible, and be much more prepared for your planning stage.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 5

HI, I would fully agree with you, if I had a place to start, but having no idea how to begin will wast tons of company money and can even be dangerous if I were totally new at it. However, goher2000 above gave me the one little thing I needed t get started, and I am all set now.

Thanks