SQL Statment to compare two tables and get the data or insert data into a table..??

Question

I have a cvs file which  loads data into a tableA daily but in this table i dont have a column(data) to filter the data

table format

Client          Totaljobs   Success    Failed

ServerA          10           9              1

ServerB           9             9              0

where in my qurery i need only prod jobs
select * from tableA where envi='prod'
where envi coulmn is not there in the table

i have other table which has Server and envi 

table format

ServerName    Instancename      envi

ServerA          ServerA/BKUP     Prod

ServerB            ServerB/QA       DEV

can i compare this two table and get output for only dev or prod server saperately or can i insert new column
in tableA envi(column) and insert data daily once the daily cvs file is loaded into tableA by comparing with servername (copy data from one table to other by comparing a column data)
o/p
Client         Totaljobs   Success    Failed  

ServerA          10            9               1

am i clear.....pls let me know any idea?

Answer 1

select a.Client, a.Totaljobs, a.Success, a.Failed
from tableA a
        inner join othertable b on a.Client = b.ServerName
where b.envi = 'Prod'

---

KH Tan

Answer 2

select [required column set]
from table1 inner join table2 on table1.serverName = table2.client



output would be

row 1 -- prod
row 2 -- dev

---

Shamas Saeed MCITP-DBD 2005 http://sqlservercoollinks.blogspot.com

Answer 3

thanks guys for the reply..

Yeah u guys are right i was thinking too much

whats the differnence between below two codes??

select a.Client, a.Totaljobs
from tableA a
        inner join othertable b on a.Client = b.ServerName
where b.envi = 'Prod'

or

select a.Client, a.Totaljobs
from tableA a,table b  
where a.Client = b.ServerName 
and b.envi = 'Prod'

i guess we should get same result for the above two queries right..??
i used select a.Client, a.Totaljobs from tableA a,table b  where a.Client = b.ServerName and b.envi = 'Prod'
am i right....??

Answer 4

Both will give you same result. The first query is using ANSI join method and it is preferable over the 2nd query.

---

KH Tan

Answer 5

Hi,
You can try this:

SELECT * FROM TABLEA WHERE SERVERNAME IN (SELECT SERVERNAME FROM TABLEB WHERE ENVI = 'PROD')

I am trying to suggest a subquery against a join since you appear to use only one column from TABLEB, since I believe a join would be more preferable if there are many columns being related.

HTH - Happy programming