Insert from another table, and default if NULL

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, and default if NULL

Author

Topic

SarahLOR
Starting Member

18 Posts

Posted - 2010-04-14 : 11:39:15

I need to insert some data from various tables into 1 table.
When a certain column is NULL in the original table, I need to replace this with a value in the new table.
Within Oracle, I would have used the decode function. Is there something similar that I can use in SQL server?

Thanks,
Sarah

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-14 : 11:45:00

The ISNULL function will do what you need. ISNULL(a, b) returns a, unless a is NULL, in which case it returns b.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

SarahLOR
Starting Member

18 Posts

Posted - 2010-04-14 : 12:04:41

That works, thanks.

Now I need to select from another table based on this column.

So, I have inserted a default value into the standing data table, but I get the data from the other table which is null. If it is null, it needs to lookup the ID linked to this default value.

So, in the where clause, it needs to say where tab1.column = tab2.column, but it tab2.column is null, lookup a certain value in tab1

Hope this makes sense!

SarahLOR
Starting Member

18 Posts

Posted - 2010-04-14 : 12:17:39

Try to make more sense of it with an example:

insert into table
(select distinct a.Id,
b.code
from table a CROSS JOIN table b
where a.Name = b.Name)

if a.name is null, look up the c.code based on b.Name being the default value e.g. TEST

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-14 : 12:21:26

I think you're going to have to supply some sample data. I'm not really sure what you're asking for.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

SarahLOR
Starting Member

18 Posts

Posted - 2010-04-14 : 12:31:21

OK, hopefully this will help!

insert into test(Id, Name)
(select distinct a.ID,
ISNULL (b.name, 'IMPORT')
from dbo.contacts a CROSS JOIN Upload b
WHERE a.Name = 'TEST')

I then need to insert into another table, getting ID for the table above

insert into test1
(select distinct a.Id,
b.code
from test a INNER JOIN Upload b
where a.Name = b.Name)

Within the Upload table, the name is null in a number of rows, so does not match any row in the test table, as this has now been set to 'IMPORT'

So, now when I use the where clause, for those rows with a value of null, I need to get the ID where the name is 'IMPORT'

if b.name is null
select id from test
where name = 'IMPORT'

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-14 : 13:10:47

I think I know what you mean. Try this:

insert into test1
(select distinct a.Id,
b.code
from test a INNER JOIN Upload b
where a.Name = ISNULL(b.Name, 'IMPORT'))

This will cause records in the Upload table with a NULL Name to be matched with records in the test table where the name is IMPORT. Is that what you want?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

SarahLOR
Starting Member

18 Posts

Posted - 2010-04-14 : 13:54:58

Exactly, thanks very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources