Moving & Copying Data with SQL (Insert, Select Into)
When working with SQL Server it is often useful to backup tables or copy data from one table to another. The SQL statements to achieve these things are pretty straightforward but given that you might not use these too often it is easy to forget the exact syntax. So the two options are firstly to copy all of a table to a new table, creating the new table at the same time. The second option is to copy, selectively if required, data from one table to another existing table.
Option 1 - Copy entire table
Select <Fieldlist> Into <New Table Name> From <Existing Table Name>
If you want all the columns then just use the wildcard:
Select * Into <New Table Name> From <Existing Table Name>
Its worth noting that this technique for backing up a table will not copy over any primary keys. This is fine for my use as I would normally use this to backup a table prior to testing out some code which might mess up the data in the table.
The next technique we will look at is copying rows from one table to another table which already exists. So we will need two tables, one empty and with some data. Copy and paste the following SQL to set up some test tables:
Create Table Pets
(
PetID Int IDENTITY (1,1),
PetName VarChar(20) Not Null,
Age Int Not Null,
Colour VarChar(20) Not Null,
Primary Key (PetID)
)
Insert Into Pets (PetName, Age, Colour) Values ('Jack', 1, 'Black')
Insert Into Pets (PetName, Age, Colour) Values ('Fido', 4, 'Brown')
Insert Into Pets (PetName, Age, Colour) Values ('Joey', 5, 'Tabby')
Insert Into Pets (PetName, Age, Colour) Values ('Sooty', 2, 'Black & White')
Insert Into Pets (PetName, Age, Colour) Values ('Spot', 7, 'Sandy')
Create Table PetsCopy
(
PetID Int IDENTITY (1,1),
PetName VarChar(20) Not Null,
Age Int Not Null,
Colour VarChar(20) Not Null,
Primary Key (PetID)
)
We can now copy rows selectiveley from Pets to PetsCopy as follows, or if we wanted we could copy all rows:
Insert Into PetsCopy Select PetName, Age, Colour From Pets Where Age > 3