SQL - Join Tables on similar columns

Hi experts,

I want to create a new table which gets its data from 2 different databases.
The problem is: how can I gather informatione from 2 different dbs and save them in one table?
I.e:
Insert Into newTable ( Field1, Field2, Field3)
Values(
(Select Field1 From
oldTable1)
(Select Field2, Field3 From
oldTable2)
)

That example probably won't work. Whats the correct way to do it?

Thanks for your help
arthrexAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
Insert Into newTable ( Field1, Field2, Field3)
Select A.Field1, B.Field2, B.Field3 From oldTable1 A
Join oldTable2 B On A.Field1 = B.Field1

0
Aneesh RetnakaranDatabase AdministratorCommented:
Insert Into newTable ( Field1, Field2, Field3)
Select Field1, Field2, Field3
From  urDbName..OldTable1 o1 INNER JOIN urDbName..OldTable2 o2
ON o1.SomePrimaryKey = o2.SomePrimaryKey
0
Jinesh KamdarCommented:
Do u mean 2 different databases or 2 different tables? What DB is it?
E.g. For 2 different Oracle DBs, u will have to use a DB-link to pull their data together into a single table.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

arthrexAuthor Commented:
Thanks for your replies.
I can't just join the two tables, because the two tables do not have an exact connection.

To be precise: oldTable1 contains one field with values like "AAA, BBB, CCC".  OldTable2 contains a filed with values like "AAA-xxx", "BBB-xxx", "CCC-xxx". If its somehow possible I would like to join the two tables on these fields. But I don't think it is possible. Is there a way to cut-off the last part of the field in OldTable2? Or is there a completely different way without using the join?

@jinesh kamdar: Its two different databases on the same server. So actually it works similar to two different tables

Thanks
0
Jinesh KamdarCommented:
Then, try this.

Insert Into newTable ( Field1, Field2, Field3)
Select A.Field1, B.Field2, B.Field3 From oldTable1 A
Join oldTable2 B On A.Field1 = SUBSTR(B.Field1,1,3)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ashish PatelCommented:
Insert Into newTable ( Field1, Field2, Field3)
Select A.Field1, B.Field2, B.Field3 From oldTable1 A
Join oldTable2 B On A.Field1 = Left(B.Field1, 3)

0
arthrexAuthor Commented:
Ok, thanks
Thats the solution.
But just a query. How do I do it, if there is not a common pattern in the value names.
 I.e.
AAA-xxx, BBBBB-xxx, CC-xx and AAA, BBBBB, CC???
0
Ashish PatelCommented:
if you want before the - if its there and if the dash(-) is not there then consider the whole value then use this


Insert Into newTable ( Field1, Field2, Field3)
Select A.Field1, B.Field2, B.Field3 From oldTable1 A
Join oldTable2 B On A.Field1 = Left(B.Field1, case when charindex('-', B.Field1)>0 then charindex('-', B.Field1)-1 else len(B.Field1) end )
0
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.