DML: SELECT INTO Statements

Syntactic Suguar

SELECT INTO is an example of syntactic sugar. It is both DDL and DML because it invokes both

  • CREATE TABLE
  • INSERT INTO.

It allows us to easily copy data from one table into a new table. We write it like this:

SELECT
	*
INTO dbo.newSongTable
FROM dbo.Song;

Under the hood

This statement automatically invokes this CREATE TABLE statement:

CREATE TABLE dbo.newSongTable 
	ID		int IDENTITY(1,1) PRIMARY KEY
,	Name		nvarchar(100)
,	Duration	int);

Note: This schema for dbo.newSongTable is identical to the source table dbo.Songs.

This is followed by an INSERT INTO:

INSERT INTO dbo.newSongTable
SELECT
	*
FROM dbo.Songs;