Scenario:You are working as SQL Server developer, you need to provide some update or delete scripts to update or delete data from a table.You want to take the backup of those records or if the table is small you might want to backup entire table before you run update or delete statements.
How would you backup entire table or only the records on which you need to run update or delete statements?
USE yourDatabaseName Go Create Table dbo.Customer( Id int identity(1,1), FName VARCHAR(50), LName VARCHAR(50), Age int, DOB Date, Salary Numeric(6,2)) --Use the Insert Into with Values single Statement Insert into dbo.Customer Values('Aamir','Shahzad',36,'1980-01-01',5000.50), ('Raza','M',33,'1983-03-03',4900.34), ('John','Smith',26,'1990-04-05',5000.50)
Select * into dbo.Customber_Bkp_20160507 from dbo.Customer
Select * into dbo.Customber_Bkp_20160507_OnlyAamir from dbo.Customer where FName='Aamir'
|How to backup records into new table from existing SQL Server Table in SQL Server|
Scripts used in the video demo:
--Take the backup or create table for all records Select * into [YourDBName].dbo.Customer_20160510 from [dbo].[Customer] --Create table for selected records Select * into [YourDBName].dbo.Customer_20160510_TwoRecords from [dbo].[Customer] where id<=2 --Check if table is created successfully Select * From [YourDBName].dbo.Customer_20160510_TwoRecords Select * from [dbo].[Customer] --Update Records in current table update [dbo].[Customer] set LName=LName+' Test' where id<=2 --Update records in current table from backup table update d set d.LName=s.LName from [YourDBName].dbo.Customer_20160510_TwoRecords s inner join [dbo].[Customer] d on s.id=d.id