How to Create key (to be used in join) using concatenation? 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 &amp; not the entire table.

declare @lookup as varchar(255)  
Select  @lookup = accountID + state + product  
From Table1  
Print @lookup
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 );