Insert value if not exists in SQL Server 2008 R2

Question

create table #tableA(id int, val varchar(50)) create table #tableB(id int, val varchar(50)) create table #tableC(id int, val varchar(50)) create table #tableD(id int, val varchar(50)) insert into #tableB values (1, '11'); insert into #tableB values (2, '22');

I want insert value for #tableD and my condition is

if #tableA has a value then

insert into #tableD select * from #tableA;

if #tableA is empty then

insert into #tableD select * from #tableB;

if #tableA and #tableB are empty then

insert into #tableD select * from #tableC;

How can I do this the simplest way?

valex · Accepted Answer · 2017-11-09 07:57:00Z

Try to use Transact-SQL statement IF..THEN..ELSE

IF EXISTS(SELECT * FROM #TableA) BEGIN insert into #tableD select * from #tableA; END ELSE IF EXISTS(SELECT * FROM #TableB) BEGIN insert into #tableD select * from #tableB; END ELSE BEGIN insert into #tableD select * from #tableC; END

Ab Bennett · Accepted Answer · 2017-11-09 08:06:54Z

two basic ways but not good if the number of temp tables is dynamic. Note I use * in the queries, but best practice to specify columns

The first works fine but may be slow on huge data, and does extra work than needed, but if a small data set it should be ok

insert into #tabled select * from #tablea union all select * from #tableb where 0 = (select count(*) from #tableA) union all select * from #tablec where 0 = (select count(*) from (select top 1 id from #tablea union all select top 1 id from #tableb ) x )

or the second way works ok, and only does the work neccessary.

insert into #tableD select * from #tableA if @@rowcount = 0 begin insert into #tableD select * from #tableB if @@rowcount = 0 begin insert into #tableD select * from #tableC if @@rowcount = 0 begin print('no rows inserted') end else begin print('rows inserted from C') end end else begin print('inserted from B') end end else begin print('insert from A') end

dbajtr · Accepted Answer · 2017-11-09 07:54:57Z

Something like this should work:

if exists (select 1 from #tableA) begin insert into #tableD select * from #tableA end else begin insert into #tableD select * from #tableB end if not exists (select 1 from #tableA union select 1 from #tableB) begin insert into #tableD select * from #tableC end

Yogesh Sharma · Accepted Answer · 2017-11-09 07:55:44Z

Use (Transact-SQL) EXISTS to insert the value based on conditions

if exists (select * from #tableA) begin insert into #tableD select * from #tableA; end else begin insert into #tableD select * from #tableB; end if not exists (select * from #tableA) begin if not exists (select * from #tableB) begin insert into #tableD select * from #tableC; end end

oɔɯǝɹ · Accepted Answer · 2017-11-09 11:49:41Z

-1

use this

INSERT INTO #tabled SELECT * FROM #tableA UNION ALL SELECT * FROM #tableB UNION ALL SELECT * FROM #tableC

edited Nov 9, 2017 at 11:49

oɔɯǝɹ

7,6357 gold badges62 silver badges72 bronze badges

answered Nov 9, 2017 at 11:23

pankaj singh

1051 silver badge11 bronze badges

Collectives™ on Stack Overflow

Insert value if not exists in SQL Server 2008 R2

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Related