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
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.
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.
%a, so we want to look for names ending with
Pattern column has a fixed width of 5 characters,
a% is expanded to
'%a ' in the query. The solution is simple, use
select * from T1 t inner join Z1 z on t.Name LIKE RTRIM(z.Pattern)
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
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
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) + '|%'
RTRIM above is needed when the
Artikel column in table has a fixed which is greater than the length of the query parts like
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
WHERE part of the
SELECT query and prepend the
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
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]) -- 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
SELECT FROM SELECT doesn’t work.
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