TSql Practical SQL Examples (New Tables)

 

This is the 5th post on practical T-SQL; this post focuses on joining tables this time to continue the first one on SELECT, where you would start with…

Table creation

Part of the DDL is table creation and manipulation…

— create anew table as result of a select

SELECT EnglishProductName, ListPrice DealerPrice AS Discounted

INTO DimProductDiscounted

FROM dbo.DimProduct

WHERE ListPrice IS
NOT
NULL

A new table will be created with the resulting query data

N> Use a fake condition ex where 1 = 0 to create an empty table

N> Select Into will fail if the table already exists. When the table already exists, you can use Insert Into Select From, see later

N> the new table DimProductDiscounted has the same structure of DimProduct but, it will not have any of the keys (for instance, a primary or foreign key) nor any of the indexes from DimProduct

 

DELETE DimProductDiscounted

You delete the table contents, but not the table itself

 

DROP
TABLE DimProductDiscounted

The table no longer exists in the db (with all his content)

 

— temporary table #

SELECT EnglishProductName, ListPrice DealerPrice AS Discounted

INTO #DimProductDiscounted

FROM dbo.DimProduct

WHERE ListPrice IS
NOT
NULL

Temporary tables are created in the tempdb

N> Temporary tables are often used as work tables or staging tables or test tables

More here

 

— global temporary table ##

SELECT EnglishProductName, ListPrice DealerPrice AS Discounted

INTO ##DimProductDiscountedGlobal

FROM dbo.DimProduct

WHERE ListPrice IS
NOT
NULL

A local temporary table # is only accessible from the current query editor window (a session); a global temporary table ## is visible from other query editor windows or sessions

You can open another session (windows) and query the ## table

 

— semi-permanent table in tempdb

SELECT EnglishProductName, ListPrice DealerPrice AS Discounted

INTO tempdb.dbo.NewDimProduct

FROM dbo.DimProduct

WHERE ListPrice IS
NOT
NULL

if you stop and restart SQL Server, they are erased from tempdb, because the tempdb database is always re-created and cleared as a result when SQL Server starts up

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s