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

Question

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

Answer 1

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.

Answer 2

You don't need to (and shouldn't) concatenate a string just to join on multiple columns.

You can use

SELECT *
FROM   Table1 t1
       INNER JOIN Table2 t2
               ON t1.accountID = t2.accountID
                  AND t1.state = t2.state
                  AND t1.product = t2.product;

Or - if the columns are nullable and you want nulls treated equal.

SELECT *
FROM   Table1 t1
       INNER JOIN Table2 t2
               ON EXISTS (SELECT t1.accountID,
                                 t1.state,
                                 t1.product
                          INTERSECT
                          SELECT t2.accountID,
                                 t2.state,
                                 t2.product); 

Similarly you can create a composite PK on all three columns without this repetitious concatenated string.

(Assumes that all are NOT NULL and no duplicates exist)

ALTER TABLE dbo.Table1
  ADD CONSTRAINT YourPK PRIMARY KEY CLUSTERED (accountID, state, product );