SQL useful tricks

Below are some of the less-known though useful tricks in T-SQL along with examples of how to apply them. If you know of any other interesting examples of such tricks, please let me know.

Please note that the code listed here will work in MS SQL Management Studio and may not work in Oracle databases, MySQL databases, and in others.

1. Create a table with the schema from another table

The SELECT INTO statement may be used to create a new, empty table with the schema of an existing table. A false WHERE clause is required, such as the one presented below (thus, the query will return no data):

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

Obviously enough, you can also select only some columns or make joins with 

other tables.

2. Check if the data to insert is good 

How to check if the data you want to insert into a table is OK:

BEGIN TRAN

 INSERT INTO dbo.TableName ([ColumnNames])
 VALUES (...)
 
 SELECT TOP (100) ...
 FROM dbo.TableName
 ORDER BY DateColumn DESC
 
ROLLBACK TRAN

This way, you will see how the table will look after the rows with your new data are inserted into it. The transaction will be rolled back, so no data will actually be inserted into the table.

A question for you: if there is a PRIMARY KEY column, will the values be incremented by the number of rows that were actually not inserted into the table?

(3)
SELECT *
FROM
    (--"values constructor"
    VALUES (1, 'a'), (2, 'b')
) t(x, y) -- "derived column list"