x

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

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!

more ▼

asked Mar 24, 2010 at 01:56 PM in Default

gjl15 gravatar image

gjl15
49 3 4 5

what does the ResultsTable look like?
Mar 24, 2010 at 02:12 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Mar 24, 2010 at 02:16 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

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, 2010 at 02:41 PM gjl15
so in that case follow my 2nd example and add the column before inserting the results
Mar 24, 2010 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, 2010 at 05:41 PM gjl15
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x41

asked: Mar 24, 2010 at 01:56 PM

Seen: 2004 times

Last Updated: Mar 24, 2010 at 01:56 PM