Complete Table backup using sql job

Question

Complete Table backup using sql job

Minnu

SSCertifiable

Points: 6451
More actions
June 10, 2014 at 6:23 am

#289784

Hi team,
created a job to delete all the records from table 'Unify 'daily. i want the same job to take the backup of table before deleting the records.
Is it possible in sql to take the complete table backup in .sql format.
Please suggest

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Minnu (6/10/2014)
Hi team,
created a job to delete all the records from table 'Unify 'daily. i want the same job to take the backup of table before deleting the records.
Is it possible in sql to take the complete table backup in .sql format.
Please suggest

If I'm reading this right, you want to have your procedure generate insert statements that you would then store in a table. You wouldn't be creating a backup file, but rather populating a table with everything you delete. If this is the case, then yes, you can use SQL to generate SQL statements that will (if executed) repopulate the table. For example, given the following table:

CREATE TABLE Unify (

ID Integer,

SomeField Varchar(32));

insert into Unify(ID, SomeField)

values(1, 'Some Value 1'),

(2, 'Some Value 2');

You could create a history table to hold your rows you're going to delete like this:

select 'insert into Unify(ID, SomeField) values(' + CONVERT(Varchar(30), ID) + ', ''' + SomeField + ''');'

from Unify;

You could then store your statements in your history table. Depending on the structure of your table, the statements could get rather long. Now that I've explained how it could be done, I don't think you should do it.

I think a better approach to this problem would be to create a history table with the same structure as the original table and then simply insert the rows you're going to delete into the history table. This would consume a lot less space and also let you query history much more easily and efficiently. If you add a date column to your history table, which I highly recommend, you could restore the rows by querying the history table by date.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Minnu SSCertifiable Points: 6451 More actions · Answer 2

Hi,

The above code works good.

Can you please create script for below table structure, because Column "Dept_code" having all nulls, insert script is generating as NULL.

Table Name : Emp_dept

Emp id(int)

Dept_id (int)

in_time (datetime)

Dept_code (char)

Dept_name(char)

S_id(smallint)

Emp_desc(varchar)

No_of_days(int)