Create a new table with data from an existing table

The idea here is sort of like creating a table based on a SELECT statement, but I think SQL’s syntax is a little counterintuitive.

Here’s the crown jewel:

SELECT * INTO [dbo].[tmp_Users] FROM sqlstr_Users;

And here’s how we might use it in practice:

-- First we create a table variable as storage for the demo
DECLARE @sqlstr_Users TABLE
(
	Id int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	UserName VARCHAR(64) NOT NULL,
	Notified BIT DEFAULT 0
);

-- Add sample data
INSERT INTO @sqlstr_Users (UserName, Notified) 
VALUES ('Karen', 1);

-- Creates the real table
SELECT * INTO [dbo].[tmp_Users] FROM @sqlstr_Users; 

SELECT * FROM [dbo].[tmp_Users];
Default image
Eric
Code guy.

Newsletter Updates

Enter your email address below to subscribe to our newsletter

Leave a Reply