Joining a table from another DB after amending key

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

SQL Server 2005 Forums

Transact-SQL (2005)

Joining a table from another DB after amending key

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-11-16 : 09:40:51

Hi

I'm trying to join a table from another database. In the first database the primary key is in the following format: {12345678}
In the table that i'm trying to join the primary key is in the format of 12345678

What I want to do is join the two table together but I know I will need to remove the { } within the field to be able to join them.

I thought it would be quite straight forward using the following code :

SELECT
c.cust_name,
REPLACE(REPLACE(c.customer_id, '{', ''), '}', '') as CRMS_ID,
b.emailaddress1
FROM Customer C
LEFT Join CRMS.dbo.Contacts b
ON REPLACE(REPLACE(c.customer_id, '{', ''), '}', '')=b.accountid

When I run this code I get the following error:

Conversion failed when converting from a character string to uniqueidentifier.

Does anyone know if this can actually be done this way?

The result of the replace function is what I want but it doesn't want to join together.

Any help will be appreciated.

Thanks

Dave

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-16 : 09:50:41

Please give the DDL of the involved tables so that we have not guess about.

No, you're never too old to Yak'n'Roll if you're too young to die.

bendertez
Yak Posting Veteran

94 Posts

Posted - 2010-11-16 : 10:24:12

Hi WebFred

Sorry what do you mean by DDL?

The only link I can see is the DBS.dbo.customer_ID to CRMS.dbo.Contacts.accountid (but withou the brackets {} I mentioned above).

Thanks

Dave

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-16 : 10:43:31

DDL = DataDefinitionLanguage
That means if we can see the CREATE TABLE statement(s) then we are able to find a solution.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources