How to Create key (to be used in join) using concatenation

join;

I want to inner join tables in SQL Server 2008, however both tables have been set without Primary Keys. How to create a key using concatenation of existing columns?

I tried the below but it only results in one record & not the entire table.

declare @lookup as varchar(255)  
Select  @lookup = accountID + state + product  
From Table1  
Print @lookup

Best Answer

I'm not certain I understand your requirements for this, however the following will return a row for each row in Table1.

Assuming [accountID] is an integer, you'd need to:

Select [lookup] = CONVERT(VARCHAR(30), accountID) + state + product
From dbo.Table1
ORDER BY accountID + state + product;

You should always specify the schema (I used dbo since it is the default). I've also added an ORDER BY clause to make the output easier to understand.

It might make more sense to extend this to creation of the actual key field, as such:

-- add the [Lookup] column, make it nullable for now.
ALTER TABLE dbo.Table1 ADD [Lookup] VARCHAR(255) NULL;
GO

-- insert the concatenated value into the [Lookup] column
UPDATE dbo.Table1
SET [Lookup] = CONVERT(VARCHAR(30), accountID) + state + product
FROM dbo.Table1;
GO

-- make the [Lookup] column NOT nullable so we can turn it into a primary key
ALTER TABLE dbo.Table1 ALTER COLUMN [Lookup] VARCHAR(255) NOT NULL;    
GO

-- create the clustered primary key constraint
ALTER TABLE dbo.Table1 ADD CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED ([Lookup]);
GO

SELECT *
FROM dbo.Table1;

If the table dbo.Table already has a clustered primary key, you'd either not make Lookup a primary key, or perhaps you'd drop the existing primary key, and make Lookup the new primary key.

Related Question