I need to populate TABLE_D in database DB1 from TABLE_S in database DB2. TABLE_S contains an ID VARCHAR and I want to populate TABLE_D with an ID INTEGER. Of course, I need to keep the relation between TABLE_S.ID and TABLE_D.ID.
I don't know how to create proc in sql server but I guess that's the only way to do it.

Thanks in advance for any input.

Do you want to convert the varchar to an integer, or do you want to generate a new identity? Do you want the resulting column of be of datatype Identity?

regards
David Cameron

Thanks for your answers. I think I figured out a solution:
-----------
CREATE TABLE LU_CLIENT_BU(
CLIENT_BU_ID INT IDENTITY(0,1) PRIMARY KEY,
CLIENT_BU_DESC_1 VARCHAR(80),
CLIENT_BU_DESC_2 VARCHAR(80),
CLIENT_BU_DESC_3 VARCHAR(80)
);

INSERT INTO LU_CLIENT_BU
SELECT DISTINCT TARGETBU, CBU, CBU1, CBU2
FROM CLIENT;
-----------
The IDENTITY(0,1) allows to define an automatic increment from 0 to x (+1 each time a row is inserted in the table). So I basically define the new PK CLIEN_BU_ID as INT and then I can populate my new table fromt the old one. A new unique ID is generated for each row inserted.

I don't use the CLIENT table afterward.
Cheers