Select and Insert in 1 SQL Query

Posted: April 12th, 2010 | Filed under: MsSQL

I really dislike messing with cursors and loops in SQL and so I jump on any chance I get to avoid them. This little SQL-Server trick allow me to perform a select and insert statements in just 1 short query. In the simplest form the query looks like the following.

INSERT INTO Table2 (col1, col2)
SELECT col3, col4
FROM Table1

---

How about a bit detailed example using the following sample database structure.

Table1
===========
Tbl1_ID (going to assume an auto increment here)
FK_Tbl2_ID
Data_Copy

Table2
===========
Tbl2_ID
Data

Query1: Select Tbl2_ID From Table2 Where Data = 1

For every returned record from query1 it should insert a record into Table1

INSERT INTO Table1 (FK_Tbl2_ID, Data_Copy)
SELECT Tbl2_ID, Data
FROM Table2
WHERE Data = 1

If anyone has seen/done any kind of performance testing on this sort of query would you kindly post them below.

---

MSSQL Escape Character

Posted: February 25th, 2009 | Filed under: MsSQL

Here is something that got me for a while today, escaping characters in MsSQL. I went through and tried the normal \ then thought I was losing it and tried the other slash /. However for some odd reason Microsoft went with the single tick ‘.

So for example a mssql query using the escape character might look like

SELECT a FROM b WHERE a = 'cory''s'

Notice the double tick to escape the apostrophe. Really quite simple but still really weird.

---