Announcing our $3.4M seed round 🚀 Read more →

How to Duplicate a Table in SQL Server in SQL Server

Sometimes it's useful to duplicate a table. There are various methods, depending on your intent:

Copy entire table structure along with particular data set:

select *
into users_1
from users
where domain = 1;

Copy only particular columns into new table along with particular data set:

select 
  id, 
  username, 
  password
into users_1
from users
where domain = 1;

Copy only particular columns from more tables into new table along with particular data set:

select 
  u.username, 
  s.start_date as session_start_date, 
  s.end_date as session_end_date
into users_sessions_1_rpt
from sessions s
left join user_sessions us on s.id=us.session_id
left join users u on us.user_id=u.id
where u.domain = 1;

Copy only table structure, no data copying:

select * 
into users_dm
from users
where 1 = 0;

Ready for a modern SQL editor?