CODEWITHSUNDEEP
sql
masfenix
masfenix

Reputation: 8016

copy column values to another part of the table

I have a SELECT statement along with a join that uses two tables. We have tables Shell and table Tanker.

It looks something like this select S.*, T.* from shell S left outer join tanker T on S.id = T.id. Note that Tanker has about 183 fields.

So I get the following data

     ====Shell table==== =========Tanker table columsn======================   
    orgid  org eli lang orgid   org     Lang    {Other columns start}
    906875  s   1   1   NULL    NULL    NULL    NULL    NULL    NULL    NULL
    906876  s   2   2   NULL    NULL    NULL    NULL    NULL    NULL    NULL
    906877  b   2   1   NULL    NULL    NULL    NULL    NULL    NULL    NULL
    906878  s   1   1   NULL    NULL    NULL    NULL    NULL    NULL    NULL
    906879  b   2   1   NULL    NULL    NULL    NULL    NULL    NULL    NULL

Now I want to select orgid, org, lang, and the other 183 fields. So right now I do select T.* FROM .... but the problem with this is that when it selects the orgid, org, lang, and eli it gets the NULLS only. Which makes sense because I am only selecting T.* and its an left join so naturally these will be null.

However, for my purpose I need them to be the values from the shell table. Ie, I want to select the orgid, org, eli, lang from Shell and then the rest from Tanker. I know the one way I do this is the following

select s.orgid, s.org, s.eli, s.lang, t.column, t.column2, t.column3

BUT RECALL that Tanker T has almost 200 columns. I do not want to write 200 columns on my script.

So here is the question.

Is there any way I can "copy" the values of orgid, org, eli, lang (ie, the actual not null values) to the columns which are null? That way I can just do select T.* and select all 200 columns.

so really if you are confused the end result I am looking for is

    ====Shell table==== =========Tanker table columsn======================    
    orgid  org eli lang orgid   org     Eli Lang    {Other columns start}
    906875  s   1   1   906875  s        1  1       NULL    NULL
    906876  s   2   2   906876  s        2  2       NULL    NULL    NULL
    906877  b   2   1   906877  b        2  1       NULL    NULL
    906878  s   1   1   906878  s        1  1       NULL    NULL    NULL
    906879  b   2   1   906879  b        2  1       NULL    NULL    NULL

Upvotes: 1

Views: 270

Answers (2)

Limey
Limey

Reputation: 2772

you can use Isnull (SQL server). It allows you to provide a value to use incase column you are selecting is null.

select isnull(t.org, s.org) as org

if T has a value, it gives you that, else it give you the one from S

EDIT:

SPFiredrake pointed I focused on the wrong part. Another thing you could do (if all your worried about is a HUGE SQL script) is to select * from T and then just the few columns from S that may be needed and then handle the logic of comparrision in whatever lanaguage will be playing with your data.

Upvotes: 0

SPFiredrake
SPFiredrake

Reputation: 3892

In this case, I would recommend just writing a view that would perform this select and you can reuse it wherever you need this kind of functionality. Should you ever need to modify it, it's a simple matter of updating the view (although you'll have to be careful you don't have any fundamental logic working against that view).

In order to do this easily, you can actually click-drag a table from SSMS into the query window. Just click-drag the Columns folder under the table in question into the query window and it will copy ALL the columns into it. That way you can do the select manually without having to type everything in.

Upvotes: 1

Related Questions