Newbie Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Newbie Question

Author

Topic

anddmx
Starting Member

6 Posts

Posted - 2005-10-11 : 21:24:53

HI,

I havent been working with SQL that long so this might sound stupid.

I have one table named employees with 4 columns named empID, Fname, Lname, adpID,

Second table named Empinfo with 7 columns named empID, Address, city, state, zipcode, phone, email,

I want to take the data from employees table and have it drop down into Empinfo with the colums name empID and fname, lname.

Is this possible?

I cant even get PK to PK to work :( need some help

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-12 : 01:39:56

I don't know what you mean by drop down into Empinfo, but perhaps this is what you want:

SELECT ei.empID, ei.Address, ei.city, ei.state, ei.zipcode, ei.phone, ei.email, e.Fname, e.lname
FROM employees e
INNER JOIN Empinfo ei
ON e.empID = ei.empID

Tara

anddmx
Starting Member

6 Posts

Posted - 2005-10-12 : 02:15:40

Move data from one table to another with same column name so its in both tables.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-12 : 02:24:33

So you want to add these columns into the table, then update the data? Adding columns would require ALTER TABLE ADD ColumnName and updating the data would require an UPDATE statement with an INNER JOIN. If you show us a data example of what you want, we'd better be able to provide you a solution.

Tara

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-12 : 04:35:27

or also you can try to use into query for this matter, somthing like

SELECT ei.empID, ei.Address, ei.city, ei.state, ei.zipcode, ei.phone, ei.email, e.Fname, e.lname
Into EmpTmp
FROM employees e
INNER JOIN Empinfo ei
ON e.empID = ei.empID

Then Drop the table Employee and EmpInfo after taking the backup of it

and then

Select * Into Employee From EmpTmp

Hmm i hope this helps you.

Complicated things can be done by simple thinking

anddmx
Starting Member

6 Posts

Posted - 2005-10-12 : 10:52:10

I have web application build in .net that connects to SQL server.

Programmed in VB.net

I have two tables in the database one table one and table two.

On the first page it add's data (First name, Last name, email
ect.) to table one now the problem is when they enter in the information on the next page because it doesnt link the two tables, so I dont know what information goes with user. I can created all of this on one webpage but thats not what I want.

I need to know how to add data from one table to another like replication between tables with the same columns name on the same data. So when the user move to the next page information from the first page is added to second table. I tried PK to PK like user ID but I cant even get that to work.

I'm sorry for confusing but I dont need select statement query which I think you guys gave me.

Thanks for trying to help me.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-12 : 12:52:56

You don't need to change the layout of your tables so that your application can display this data. You just need to join the tables together which gives you a new table, sort of at least. Then you display this in your application.

I don't understand your explanation about moving between pages on the webpage and what it has to do with your query.

Tara

anddmx
Starting Member

6 Posts

Posted - 2005-10-12 : 14:01:22

Inner Join combines the data from 2 tables into one then I pull the results from there?

Thanks for all your help Tara .

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-12 : 14:25:43

quote:
Originally posted by anddmx

Inner Join combines the data from 2 tables into one then I pull the results from there?

Yes that is what joins do.

Tara

anddmx
Starting Member

6 Posts

Posted - 2005-10-12 : 16:41:43

identity insert into multiple tables SQL

This is what I was trying to find, I didnt need query statement :)

Thanks for the help Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-12 : 17:04:52

Let's say Column1 in Table1 is the IDENTITY

DECLARE @i int

INSERT INTO Table1 (Column2, Column3)
VALUES('Tara', 'Duggan')

SET @i = SCOPE_IDENTITY()

INSERT INTO Table2(Column1, Column4)
VALUES(@i, 'SQLTeam)

We left off Column1 in the INSERT INTO Table1 on purpose, so that SQL Server assigns the value for us. Then we put the SCOPE_IDENTITY() value (use this instead of @@IDENTITY) into a variable so that we can use it for Table2's INSERT.

Does that help?

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-12 : 17:17:33

I hope what you're asking for is what Tara gave you. Because from the sound of your question earlier, it was sounding like you wanted to setup duplicate data, with fname and lname appearing in both employees and empinfo. That would be a violation of the concept of normalizing your data. In short, here's the problem: If an employee changes their name for any reason, you have to GUARANTEE that it gets changed in both places or you will end up in the position of the system not knowing which is the correct name. Keeping the name in only one table eliminates that problem.

---------------------------
EmeraldCityDomains.com

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-10-12 : 17:19:00

And if it's just a matter of defining the tables so that you can insert the data like Tara suggests, then the empid on the EmpInfo table should NOT be an IDENTITY field. Only the empid in Employees is an IDENTITY. In EmpInfo, it's just a plain int.

---------------------------
EmeraldCityDomains.com

MikeB
Constraint Violating Yak Guru

387 Posts

Posted - 2005-10-13 : 16:07:18

quote:
Originally posted by anddmx

Move data from one table to another with same column name so its in both tables.

Why not just create a view with all the information from both tables. That way when one table is updated, updating the other to match is not a concern.


CREATE VIEW vAllEmployeeInfo
AS
SELECT t1.empID, t1.Fname, t1.Lname, t1.adpID,
       t2.address, t2.city, t2.state, t2.zipcode, t2.phone, t2.email
FROM employees t1 INNER JOIN
     Empinfo t2 ON t1.empID = t2.empID

All the information you have provided indicates that this should have been done as one table to begin with unless an employee can have more then one address, or, to save space, an address is not recorded for all employees.

But, I am assuming you realized this, hence the change.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources