SQL Server - Insert / Select

Nesting DML

You can nest select queries, but how many of you know that you can also nest any SQL DML statement?

Lightweight ETL

There are times, particularly in development when you need to load data from one table(s) into another table. Without having to create an SSIS package this can be readily accomplished by writing a nested INSERT/SELECT statement

Simple Example

USE Northwind
GO
INSERT PhoneList (CustomerID,ContactName,ContactTitle,Phone,Fax)
   SELECT CustomerID,ContactName,ContactTitle,Phone,Fax
   FROM Northwind
GO

 

Using this technique is not just confined table to table. I could write a complex join and use it to load a single table. Additionally we can add any row restriction we want. There really are no limitations.

This technique can also come in handy when you need to load masterdata from one system to another. You can also use this technique with a DELETE or UPDATE statement.

 

 

 

 

 

 

 

 

 

 

 
 Collapse All    Expand All
Custom Search

SQL Server Information and Resources