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?
 
Jinesh KamdarConnect With a Mentor Commented:
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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
 
Ashish PatelConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.