Solved

Add Lookup Column to existing DB on SQL2005

Posted on 2011-03-11
3
248 Views
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 :)
0
Comment
Question by:sktmx13
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
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.
0
 

Author Comment

by:sktmx13
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... ?
0
 

Author Closing Comment

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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now