How do I import data from one table to another conditionally? 1

[baddos]

I have two tables in my DB.

#Table 1 - asset
Fields (asset{PK}, login{FK}, department)

#Table 2 - users
Fields (login{PK}, department)

Basically what I want to do is import the users.department data into the asset.department based upon the asset.login.

I.E.

If table1 has these rows

1,baddos,NULL
2,baddos,NULL
3,joeuser,NULL

I want SQL to grab the department ID from the users table based on their login Primary key and through it into the asset table for evey row matching the login id.

Can this be done w/ a SQL script, or do I need to program something outside of SQL to do the job?

-Bad Dos

 

[misgirlny]

If I undestand correctly what you need to do - the solution is very simple:

update asset
set department = u.department
from asset a,users u
where a.login = u.login

 

[tlbroadbent]

I recommend that you don't have a Department column on the Asset table unless there is some important reason to carry the same data in two tables. The data is redundant and thus denormalized.

I also recommend using ANSI SQL JOIN syntax. Now is the time to start converting because Microsoft has indicated the old style syntax may not be supported in future versions of SQL Server.

update asset
set department = u.department
from asset a
Inner Join users u
On a.login = u.login If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[swampBoogie]

What Microsoft has indicated is that they will not support the old outer join syntax i.e.

select * from a,b
where a.c *= b.c

If anyone has that type of queries it is definitly time to rewrite them.

They have said nothing about specifying an inner join in the where clause, i.e.

select * from a,b
where a.c = b.c

which is as much ANSI SQL as using a join in the from clause is.

 

[baddos]

I used the update method that misgirlny suggested and it worked great.

I need to put the department field in there because it might be assigned to a person in one department, but be owned by a different department.

Right now I have minimal records in there, so it's no big deal verifying everything.

Thanks for the help guys!

-Bad Dos