Useful /Handy SQL Queries: MS SQL server

Q. Find duplicates in a table

SELECT zip ,
COUNT (zip) AS NumOccurrences
FROM zipcode GROUP BY zip
HAVING (COUNT(zip)> 1 )

Q. Select a row or column value at random

SELECT TOP 1 city
FROM cityAddress ORDER BY NEWID()

Q. List items in one table that are not in the other

(LEFT JOIN)
SELECT customers.*
FROM customers LEFTJOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.customer_id IS NULL

Alternatively,

SELECT customers.*
FROM customers
WHERE customers.customer_id NOT IN(SELECT customer_id FROM orders)

Note: In clauses are slower in execution

Q. List items in one table that are also in another table

(INNER JOIN)

SELECT
DISTINCT customers.*
FROM customers INNER JOIN orders
ON customers.customer_id = orders.customer_id

Alternatively,

SELECT customers.*
FROM customers
WHERE customers.customer_id IN(SELECT customer_id FROM orders)

Note: In clauses are slower in execution

Q. Get Total count of distinct column value

Notice the DISTINCT keyword placement

SELECT COUNT(DISTINCT customer_state) AS total
FROM customers

Q. Copy data from one table into another

INSERT INTO customers(customer_id, customer_name)
SELECT customer_id, customer_name
FROM partnerCustList

Q. Bulk Insert data from one table into another.

The new table will have same structure as the one where the data is copied from with Bulk Insert

SELECT *
INTO customers
FROM partnerCustList