Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Add Lookup Column to existing DB on SQL2005

Posted on 2011-03-11
Medium Priority
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 1500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…

705 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