INSERT FROM ANOTHER TABLE - 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

INSERT FROM ANOTHER TABLE

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-14 : 11:37:58

Have an employee table humres:
EmpID
1
2
3
4
5

My other table favorites
EmpID Category URL
1 A http....
1 A http...
1 B https....

I want to insert records for every empolyee in the humres table and give them the same category and url info that EmpID has in the favorites table.

X002548
Not Just a Number

15586 Posts

Posted - 2008-08-14 : 12:23:35

INSERT INTO where?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-14 : 13:13:04

insert into the favorites table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 13:35:51

do you mean adding missing employee records in favourites?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-14 : 13:55:43

No. What I am trying to accomplish is setting up favorites for 1 employee, then giving all other employees the same favorites.

So employee 1 may have 4 favorites in the favorites table. I want to give all other employees the same favorites. And I'm pulling those employess from my employee file.

Once again. I have 2 tables. FAVORITES and HUMRES
HUMRES may have 100 employees in it.
FAVORITES has 4 records in it all for employee 1 with 4 different url's.
I want to populate the FAVORITES table with all employees from the HUMRES table and give them the same data as employee 1. So each employee will have 4 records in the FAVORITES table that are identical to employee 1.

Hope that describes it a little better.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 14:10:48

ok. here it is

INSERT INTO favourites
SELECT h.EmpID,f.Category,f.URL
FROM humres h
CROSS JOIN (SELECT Category,URL FROM favourites) f
LEFT JOIN favourites f1
ON f1.EmpID =h.EmpID 
WHERE f1.EmpID IS NULL

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-14 : 15:06:44

That works good. Thank you. Another twist.

Say I already had numerous records in the favorites table. But wanted to make sure everyone had the same as employee 1 and also keep there own they already have.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 15:09:03

quote:
Originally posted by Vack

That works good. Thank you. Another twist.

Say I already had numerous records in the favorites table. But wanted to make sure everyone had the same as employee 1 and also keep there own they already have.

that should be covered by above query as the same employee id wont be picked again for populating to favourites if they are already present in it. Hence they will keep what they already have.

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-14 : 15:14:01

so if i have employee 1 with 3 favories and employee 2 with 5 favorites that are all different
from employee 1 I want employee 2 to get employee 1's favorites but I do not want employee 1 to get employee 2's favorites.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 15:20:38

ok then slight modification

INSERT INTO favourites
SELECT h.EmpID,f.Category,f.URL
FROM humres h
CROSS JOIN (SELECT Category,URL FROM favourites) f
LEFT JOIN favourites f1
ON f1.EmpID =h.EmpID
AND f1.Category =h.Category
AND f1.URL =h.Category
WHERE f1.EmpID IS NULL

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-14 : 15:31:47

How does that know to use employee 1??

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-14 : 15:35:51

One other thing I just noticed.
humres only has empid. It does not have the field category

should it be AND f1.Category = f.Category

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 22:22:39

quote:
Originally posted by Vack

One other thing I just noticed.
humres only has empid. It does not have the field category

should it be AND f1.Category = f.Category

yup it should be

f1.Category =f.Category
AND f1.URL =f.URL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources