Use a SELECT INTO query:
SELECT * INTO OldTableNameYYYYMMDD FROM OldTableName
For example, if you are working on the Customers table and want to make a quick backup before continuing, run the following query:
SELECT * INTO Customers20120605 FROM Customers
Note - Using the format of YYYYMMDD at the end of the table allows the BirdDog Job Master Purge Table Job to automatically remove the table based upon your business rules
Use an INSERT INTO query:
Note: Truncate original table prior to restoring with the backed up data.
TRUNCATE TABLE OriginalTableName
INSERT INTO OriginalTableName
Select * from BackedUpTableName
RESTORE WITH IDENTITY INSERT:
Some tables will throw an error like this if you run the above restore query: An explicit value for the identity column in table 'OEORDHDR_SQL' can only be specified when a column list is used and IDENTITY_INSERT is ON. In this case, you will need to take some additional steps.
First, in SQL navigate to the OriginalTableName, then right-click on it and select Script Table as | INSERT To New Query. THis will generate a new query with INSERT INTO [dbo].[OriginalTableName] followed by lots of [column_name]. You'll want to copy this info, then use the following query (in this example, I'm using OEORDHDR_SQL):
SET identity_insert OriginalTableName ON
insert into OriginalTableName
,[and so on])
select * from BackedUpTableName
SET identity_insert OriginalTableName OFF
Note: Sometimes SQL does not include the final [identity] column so it will need to be manually added at the end as the ,[identity]) for this query to work correctly.