Add Lookup Column to existing DB on SQL2005

No real question per se. I am just not good at this and do not want to destroy any data.

I created a Column in Access and transfered it to the existing DB via the upload Wizard. (Customers) - I filled a few Dummy Customers in and I can access it through our adp on the new form.

here's where I need the help.

Existing Database (tblDATA) does not have a Customers Column which refers to this now. - I am so not the person to do this but I am stuck with it.

Can anyone guide me step by step on how to create a column in SQL Management Studio and link it with this Customers Table I created?

There are many more linked Tables in there (tblStatus, tblDepartments and they all have an FK, int, not null) description after the name but "alas" I'm too dumb for this :-/

Step by Step solution appreciated - and I promise if it works I'll add a few Boni points :)
Who is Participating?
lcohanConnect With a Mentor Database AnalystCommented:
Ok, so you have tblDATA that has a column lets call it "CustomerName" right?
You want to add a matching CustomerId to that CustomerName so you can use your Customers table instead for ALL Customers related data right?
Assuming all these and if your Customers table has a Id (int),Name(varchar)....columns here's what I would do:

Add CustomerId column to tbldDATA like

ALTER TABLE tblDATA add CustomerId int null;

Populate it from Customers table

UPDATE tblDATA SET CustomerId = Customers.Id
FROM Customers
WHERE tblDATA.CustomerNAme = Customers.Name

Then you could add a FK one to many from Customers(one) table you created to tblDATA(many)

 - no worries to add new column to a table won't cause any data los however...bad written queries against tblDATA may fail. I mean any INSERT INTO ....SELECT * FROM tblDATA will fail becaus the table structure was changed.
sktmx13Author Commented:
The answer is totally appreciated but I am not sure if we're on the same page.

My Customer Table has 2 columns

ID - AutoNumber
CustomerName - Text 255 Chars (mostly Business Names)

I created a few Customers such as CableVision, Charter, etc...  and upsized that table to the existing SQL Database.

tblData has many existing columns but no CustomerName yet.


If we're one the same page - then where do I enter the code you gave me? And how could I add a FK one to many... ?
sktmx13Author Commented:
I figured it out but would have appreciated a more in depth approach
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.