This is my personal SQL cheat sheet. I’m using MS SQL Server 2005.
A fine complete SQL tutorial: W3Schools/SQL
Delete Some or All Rows From a Table
-- delete all, keep indexes and columns
DELETE FROM table_name;
-- delete some rows, keep indexes and columns
DELETE FROM table_name WHERE ...
Change Values or Update
UPDATE table_name SET column1=value1,column2=value2,... WHERE ...
Update Table Abc from Table Xyz
UPDATE Abc SET Abc.AccountNumber = x.AccNum
FROM Abc a
INNER JOIN Xyz x ON a.Artikel = x.Artikel
WHERE ...
Create and Copy a Table or Duplicate Table
-- copies data and schema, doesn't copy indexes
SELECT * INTO new_table_name FROM old_table_name;
-- creates an empty copy; copies schema, doesn't copy data and indexes
SELECT * INTO new_table_name FROM old_table_name WHERE 1 = 0;
Create a Table From the Result of a Complicated Query
This task can be done with the SELECT INTO statement. Consider the INTO-part in the following example. It creates a table called temp in the DB XYZ.
use ABC;
SELECT-part-of-the-query
INTO XYZ.dbo.temp
FROM-part-of-the-query
WHERE-part-of-the-query;
Copy a Table or Copy a Part of a Table
-- this copies the complete content, but does not create the table
INSERT INTO target_table SELECT * FROM source_table
-- this copies a part of the rows without creating the table
INSERT INTO target_table SELECT * FROM source_table WHERE ...
-- this copies only a part of the columns and rows
INSERT INTO target_table (col1, col2)
SELECT col1, col2 FROM source_table WHERE ...
Both methods here do not work if target_table contains an identity column. In this case, I have to leave the identity column out of the SELECT list. But there are a lot of columns and I don’ want to write down all the names.
There is a solution: you can get the list of the column’s names via SQL.
Select Into / Insert Into Table Variable Result of Query
SELECT ... INTO a table variable is not possible. You first must declare the table variable and then use INSERT INTO. Example:
DECLARE @artikels TABLE ( art nvarchar(10) );
INSERT INTO @artikels SELECT artikel FROM table WHERE ...
-- No parentheses allowed around the select statement.
Get a Comma Separated List of a Table’s Column Names
SELECT ',' + COLUMN_NAME AS [text()]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name'
ORDER BY ORDINAL_POSITION
FOR XML PATH('')
Above code creates a list of the table’s column names with a leading comma and including the identity column. Now let’s assume the identity column is called ID and it is the first column.
Then we have to trim the first four characters to get the select list we want. We can use the STUFF() function for this.
So the following command creates a comma separated list of the column names of table_name, without the first column called ID.
SELECT STUFF(
(SELECT ',' + COLUMN_NAME AS [text()]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name'
ORDER BY ORDINAL_POSITION
FOR XML PATH('')
), 1, 4, '')
I’ve not yet found out how to use this result in the INSERT INTO statement, aside of coyping the result to the clipboard… Still much better than typing 25 vcolumn names manually.
Get an Ordered List of a Table’s Column Names
SELECT COLUMN_NAME c, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name' ORDER BY c
Copy One Row of a Table Into The Same Table, Changing One Value and Dropping One Column
The easiest way to do this without typing out all the column names is with a temporary table.
SELECT * INTO #TempTable -- # is for temp tables
FROM table_name WHERE ...;
ALTER TABLE #TempTable DROP COLUMN Id; -- drop a column
UPDATE #TempTable SET SettingsName = 'default'; -- change a value
INSERT INTO table_name SELECT * FROM #TempTable; -- copy back
DROP TABLE #TempTable;
LIKE Query Does Not Work
select * from Table where User like 'Meyer%';
does not show any results. But when I call
select * from Table; I can clearly see the users called Meyer or Meyerbeer.
Solution: The column name User and all other reserved keywords which are used as column or table names must be escaped with square brackets []. This one works:
select * from Table where [User] like 'Meyer%';
The characters with which you can escape column names and the like are different from DB to DB.
JOIN with LIKE Does Not Work
select * from T1 t inner join Z1 z on t.Name LIKE z.Pattern
does not show any results, even though it should.
Reason: Say Pattern is %a, so we want to look for names ending with a.
If Pattern column has a fixed width of 5 characters, a% is expanded to '%a ' in the query. The solution is simple, use RTRIM:
select * from T1 t inner join Z1 z on t.Name LIKE RTRIM(z.Pattern)
COUNT for a complicated Query
Say you have a quite complicated query SELECT .... and you just want to know the count of lines it returns.
Then you usually can do it like this: select COUNT(1) from (SELECT ....) t
Respect the added t at the end! (Could be any other name instead of t.)
But this does not work if your original query contains nameless columns. E.g.
select COUNT(1) from (SELECT id, RTRIM(name), LTRIM(pass) from x) t will fail. Here you have to add a name to every anonymous column of your original query.
select COUNT(1) from (SELECT id, RTRIM(name) a, LTRIM(pass) b from x) t
Combine COUNT and DISTINCT
SELECT COUNT(DISTINCT Column) FROM Table;
-- If you need to use multiple columns with COUNT and DISITNCT,
-- you need to use such a query:
SELECT COUNT(1) FROM (SELECT DISTINCT Col1, Col2, ... FROM Table) AS iq
-- Warning: Both above do not work with null values. If you may have
-- null values in Column and you want to treat all null values as one
-- distinct thing, you should do this:
SELECT (SELECT COUNT(DISTINCT Col) FROM Table WHERE Col is not null)
+ (SELECT SIGN(COUNT(1)) FROM Table WHERE Col is null)
Combine UNION, TOP and DISTINCT
I want to have the TOP 5 values of a UNION of two DISTINCT queries over two tables. This is the solution:
SELECT TOP 5 * FROM (
SELECT dimid from TDrawing
UNION
SELECT dimid from TDimensions ) AS u
ORDER BY dimid DESC
Explanation: DISTINCT is not needed at all, as UNION already filters out duplicates. AS u is probably needed only for MSSQL.
Combine UNION and MAX
I want to have the biggest value over two tables. This is the solution:
SELECT MAX(d) FROM (SELECT d from t1 UNION SELECT d from t2) AS u
-- AS u at the end is probably needed only for MSSQL. But there it is.
Select Entries in One Table Which Do Not Occur in Another
Let’s assume there is one unique key column which exists in both tables.
SELECT * FROM Table1
WHERE key NOT IN
(SELECT key FROM Table2);
Drop a Table Only if it Exists
USE database_name;
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'table_name'))
BEGIN
DROP TABLE table_name;
END
Attention, the solution above will not work for temp tables. For those you need to do something like this:
IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
Put the Output of EXEC into a Variable
Here I show how to put the output of an EXEC call to a command line or shell command into a SQL variable.
DECLARE @res varchar(1000)
CREATE TABLE #temp (text varchar(1000))
INSERT INTO #temp EXEC master..xp_cmdshell 'cd'
SELECT @res = text FROM #temp WHERE text IS NOT NULL
-- here @res is something like 'C:\WINDOWS\system32'
Find Differences Between Two Tables
SELECT * FROM table1 EXCEPT (SELECT * FROM table2)
UNION ALL
SELECT * FROM table2 EXCEPT (SELECT * FROM table1)
Dates And a Difference Between Dates
MSSQL Server 2005 can not parse most of the date-time formats that newer systems understand. What it can parse, is a format like YYYYMMDD HH:MM:SS. You can print a date in this format in C# with DateTime.Now.ToString("yyyyMMdd HH:mm:ss")
A difference in minutes between a date-time given manually and a date-time column called StartDate can be calculated like this:
DATEDIFF(minute, '20150826 14:01:35', StartDate)
Getting only entries where column ChangeDate is younger than now minus 60 days (which means, the entry has been changed in the last 60 days) can be done so:
... WHERE DATEDIFF(day, ChangeDate, GETDATE()) < 60
Set Variable as List Which can be used in WHERE IN Statement
Clean version - use a table variable:
DECLARE @kkk TABLE (a varchar(10))
INSERT INTO @kkk VALUES ('100405');
INSERT INTO @kkk VALUES ('104580');
SELECT * FROM table WHERE ARTIKEL IN (SELECT a FROM @kkk)
Dirty version - use a string and a reverse LIKE query:
DECLARE @qqq VARCHAR(100)
SET @qqq = '|100405|104580|260140|'
SELECT * FROM table WHERE @qqq LIKE '%|' + RTRIM(ARTIKEL) + '|%'
The RTRIM above is needed when the Artikel column in table has a fixed which is greater than the length of the query parts like '100450'.
Update with Join
We have a query with a JOIN in it and want to update the result of the query. Let's say the query which shows the entries we want to update is this:
SELECT a.Artikel, a.Name, s.Zeichen, s.Position
FROM Artikel AS a
INNER JOIN Sachwert AS s on a.Artikel = s.Artikel
WHERE (a.Name like 'Z%XX%') and (s.Position = '1' )
ORDER by a.Artikel
And let's say we want to update every s.Zeichen of the result set to Abc. How do we do this? It is easy. We take exactly the FROM, JOIN and WHERE part of the SELECT query and prepend the UPDATE statement.
UPDATE Sachwert SET Zeichen = 'Abc'
FROM Artikel AS a
INNER JOIN Sachwert AS s on a.Artikel = s.Artikel
WHERE (a.Name like 'Z%XX%') and (s.Position = '1' )
SELECT INTO with UNION
Q: I want to fill the result of a query with UNION into a new table, like I can do it with SELECT ... INTO newTable. How do I do this?
A: Just put the INTO newTable before the first FROM statement, exactly where you'd put it if the query had no UNION. Example:
SELECT a, b, c
INTO newTable
FROM X WHERE ... UNION
SELECT a, b, c FROM Y WHERE ... UNION
SELECT a, b, c FROM Z WHERE ...
Cast one Type into Another
-- Syntax for CAST:
CAST (expression AS data_type [(length)])
-- Syntax for CONVERT:
CONVERT (data_type [(length)], expression [, style])
-- Convert style constants for CONVERT(DATE, '......', style)
-- yyyy-mm-dd 23 - yymmdd 12 - yyyymmdd 112
-- yyyy-mm-ddThh:mi:ss.mmm 126 - dd.mm.yy 4 - dd.mm.yyyy 104
-- Even better often is TRY_PARSE:
TRY_PARSE(expression AS decimal(22,10) USING 'en-GB')
More about CONVERT's style parameter.
Grant Execute-Rights for all Stored Procedures to a User
CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
ALTER ROLE db_executor ADD MEMBER theUserName
Use result of a SELECT query in another SELECT
SELECT a.Artikel FROM ( SELECT Artikel FROM table WHERE ... ) AS a
Important is the AS a at the end of the subquery in parentheses. Without the AS, the SELECT FROM SELECT doesn't work.
Delete Duplicates
I want to delete all entries from table Octave that are duplicates in relation to the columns Artikel, Pol, Side2 and where Artikel is not null but leave one of the entries. This trick with grouping and MIN(ID) does it well, given that ID is unique.
DELETE FROM Octave WHERE ID NOT IN
( SELECT MIN(ID) FROM Octave GROUP BY Artikel, Pol, Side2 )
AND Artikel is not null
Get the Number of Changes by the Last Statement
SELECT @@ROWCOUNT
Get the Number of Would-Be Changes of an Update Statement
See on stackoverflow.
Order a Table Permanently in the Database
It is not possible to do this reliably for big tables, they say. But it is easy and mostly works:
Just create a clustered index on the order you want. And Voilá - after that, every time you do a select, it will be in that order. CREATE CLUSTERED INDEX new_index on table (col1, col2);
