Insert into without duplicates

INSERT INTO  TableDuplicates
(empId)
SELECT  empId
FROM TableEmployees
WHERE empId NOT IN (select empId from TableDuplicates)

Copy table into new temporary table and put data back in original table

Sometimes you need to modify a table, like add a new column when there are constrains in place and you may be asked to drop the table before you can modify it. One solution is to store data in a temporary table, make the change and place the data back.

This example works when you have an identity column (autoId)

--- (1)Create new table (TempTable1) and insert into this table using SELECT INSERT
SELECT *
INTO TempTable1
FROM Table1

--- (2)Modify the Table1 --> add columns etc

--- (3)Copy the data from TempTable1 to the original Table1
---    It is important to list all the fields but the autoId (if there is one)

INSERT INTO Table1 (col1,col2,....colN)
SELECT col1,col2,....,colN
FROM TempTable1

Sample of COALESCE in SQL SERVER

DECLARE @List nvarchar(100)
select @List= COALESCE(@List + ',', '') + colValue  from TableFields where categoryId = 15 and LOWER(name) <> 'subject'
select @List

This will return a concatenated string with all the values (in all rows) for the column colValue and categoryId = 15. (the categoryId could be a parameter  @categoryId)

A delicious copy from table to table in SQL SERVER (2008)

What are we cooking today?
Copy from a table1 into a table2
Copy from the result set from a stored procedure into an existing table2
Copy from table1 into a table2 that does not exist yet (to be created)

Recipe

---When the Table2 (target table) was previously created.
INSERT INTO Table2 (fname, lname)
SELECT fname, lname
FROM Table1

---When the Table2 (target table) was previously created. [From an stored procedure]
INSERT INTO Table2 (fname, lname)
exec spGetInfo


---When the table2 (target table) WAS NOT created.
SELECT fname, lname
INTO Table2
FROM Table1


Enjoy it!