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.

LIKE Query Does Not Work

select * from Table where User like 'Meyer%';

does not show any results. But when I callselect * 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:

JOIN with LIKE Does Not Work

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)

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.

Find Differences Between Two Tables

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:

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:

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