Add column and insert result from CTE if result is not empty

Question

Hello, I have what I think is a chicken and egg question. I am trying to use a CTE, but once I explain the scenario, this may not be the best approach.

I am reading data from one table, created with a CSV, that contains computer system info. I'm comparing each computer's info to tables that contain expected results, and creating a 'results' table of exceptions. Here's the basic CTE code:

WITH cte(ComputerName, PercentFreeSpace) ( SELECT ComputerName, PercentFreeSpace FROM ReportTable WHERE PercentFreeSpace < 20 )

-- and what I'm trying to do with it (in english/ sql): If (SELECT ComputerName, PercentFreeSpace FROM cte WHERE (select count(*) from cte) > 0 ) Then: If the column PercentFreeSpace does not exist in the ResultsTable, Add it, and then insert all the results.

Is this a valid approach, or is there a better way? Thanks!

Answer 1

0

why not just insert the exception rows?

insert into ResultsTable (ComputerName, PercentFreeSpace)
select ComputerName, PercentFreeSpace from ReportTable 
  where PercentFreeSpace < 20

or if you need to do 'something' to the ResultsTable first

if exists ( SELECT ComputerName FROM ReportTable WHERE PercentFreeSpace < 20 )
begin
   *do something to the ResultsTable*
   insert into ResultsTable (ComputerName, PercentFreeSpace)
   select ComputerName, PercentFreeSpace from ReportTable 
     where PercentFreeSpace < 20

end

Don't think of a cte as a temporary table that gets created, it's just a definition like an in-line view or derived table. In your question you seem to want to create it, query it, and dependant on the answer do something with it.

answered Mar 24 '10 at 02:16 PM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

Thanks Kev! The ResultsTable is only going to have ComputerName with exceptions, and I only want to add columns where an exception was found for any ComputerName. Columns could be: ComputerName, FreeSpace, Uptime, DriveSize etc. There is a total of 14 criterion I'm checking, but I don't want a column in this table if all computer are expectable for each given criteria. So, I may end up with a table with only 5 columns.

Mar 24 '10 at 02:41 PM gjl15

so in that case follow my 2nd example and add the column before inserting the results

Mar 24 '10 at 03:51 PM Kev Riley ♦♦

Thanks Kev, that helps a ton. I'm very much a novice at this, as I'm sure you can tell.

Mar 24 '10 at 05:41 PM gjl15

(comments are locked)

Add column and insert result from CTE if result is not empty

Follow this question

Related Questions