Add Lookup Column to existing DB on SQL2005

Posted on 2011-03-11
Last Modified: 2012-05-11
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 :)
Question by:sktmx13
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 40

Accepted Solution

lcohan earned 500 total points
ID: 35111955
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.

Author Comment

ID: 35113598
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... ?

Author Closing Comment

ID: 35359667
I figured it out but would have appreciated a more in depth approach

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question