CODEWITHSUNDEEP
t-sql
Elena
Elena

Reputation: 839

case - query optimization

I have this query :

INSERT INTO #t1 (VALUE1, VALUE2)
SELECT 
CASE
WHEN EXISTS(SELECT * FROM T2)
THEN SELECT VALUE1, VALUE2 FROM T2
END

This does not work , I get : "The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns."

Instead I have to use :

INSERT INTO #t1 (VALUE1, VALUE2)
SELECT 
CASE
WHEN EXISTS(SELECT * FROM T2)
THEN SELECT VALUE1 FROM T2
END

CASE
WHEN EXISTS(SELECT * FROM T2)
THEN SELECT VALUE2 FROM T2
END

But this decreases performance. Is there a solution to do it properly? Within a single CASE, to benefit from a sinqle query for the second table t2.

Upvotes: 0

Views: 69

Answers (4)

faester
faester

Reputation: 15076

Couldn't you use something like 

  INSERT INTO #t1 (VALUE1, VALUE2)
  SELECT value1, value2 from #t2 WHERE NOT (value1 IS NULL AND value2 IS NULL)

instead?

Otherwise your intention might be 

  INSERT INTO #t1 (VALUE1)
  SELECT CONCAT(value1, value2) from t2 Couldn't you use something like 

  INSERT INTO #t1 (VALUE1, VALUE2)
  SELECT value1, value2 from t2 WHERE NOT (value1 IS NULL AND value2 IS NULL)

instead?

Otherwise your intention might be 

  INSERT INTO #t1 (VALUE1)
  SELECT value1, value2 from #t2 WHERE NOT (value1 IS NULL AND value2 IS NULL)

which causes the 't2.value2' to be inserted in t1.value1 if t2.value2 is null

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062855

Just simplify:

INSERT INTO #t1 (VALUE1, VALUE2)
SELECT VALUE1, VALUE2
FROM T2

The case where there are no rows doesn't do anything here either. You don't need to do a special test for that.

Upvotes: 0

Andrea Colleoni
Andrea Colleoni

Reputation: 6021

Why not:

INSERT INTO #t1 (VALUE1, VALUE2)
SELECT VALUE1, VALUE2 FROM T2

If there are no rows in T2 nothing will be inserted into #t1.

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44326

EXISTS(SELECT * FROM T2) doesn't do anything and the syntax is wrong

Try this:

INSERT INTO #t1 (VALUE1, VALUE2)
SELECT VALUE1, VALUE2 FROM T2

This would be valid as well, although not very useful, but it seems it is what you are trying to do:

INSERT INTO #t1 (VALUE1, VALUE2)     
SELECT VALUE1, VALUE2 FROM T2
WHERE exists (SELECT 1 FROM T2)

Upvotes: 1

Related Questions