Sunday, April 26, 2015

SQL Server Brain Basher of the Week #009

When we need to create a table using a SELECT statement, the option we use is SELECT .... INTO. No arguments, for the most of situations, this is the best way for creating a table based on the data we load. However;

Can we create a table using CREATE TABLE statement and load data using SELECT statement executing all of them as a single statement in any Microsoft SQL Server database edition?

This is not possible with most of the editions we generally use but this possibility is available with one of the editions; PDW (Parallel Data Warehouse). This gives greater flexibility and more performance, specifically on ELT operations. This is really smart and can be used with many cases though there are few restrictions.

Here is a sample code;
create table SalesTemp
with (distribution = hash(DateKey)) 
as select *  from FactSales

No comments: