ProAnswers.org

SQL tricks to do less actual real world programming to get things done?

What sql tricks have you personally used, that made it possible for you to do less actual real world programming to get things done?

This statement can save you hours and hours of programming…

				insert into ... select ... from 
				 


			
				for eg.


			
				INSERT INTO CurrentEmployee SELECT * FROM Employee WHERE FireDate IS NULL;


			
				 


		
	



will populate your new table with existing data. It avoids the need to do an ETL operation or use multiple insert statements to load your data.

I think the most useful one that I have used, is the WITH statement.

It allows subquery reuse, which makes it possible to write with a single query invocation, what normally would be two or more invocations, and the use of a temporary table.

The with statement will create inline views, or use a temporary table as needed in Oracle.