SQL Server Data Automation operations
Speaking of the current database type, currently the main popular MySQL, SQL Server, Oracle and other three kinds, the difference I believe we are already very familiar with, so there is not much introduction, today we mainly introduce my recent work encountered problems, our access control database data storage data is too large, Resulting from the operation of the page and query very slowly affect the daily operation efficiency, because the leader requires data to be retained, convenient for future inquiries,
So we generally judge by the date of data deletion beyond the specified date this can not be achieved, of course, a different idea to insert data other than the specified date into the backup database, and then based on the data outside the specified date deleted, in fact, the idea is true, but in this case, I need to execute two statements, If the first one fails, the second one will not be able to execute, so we follow this line of thinking to integrate two commands into one, and then we simulate.
We have 300多万条 data in the table, a bit more, the query must be slow
In order to ensure the security of the data during operation, we need to backup the table;
We will copy the Cardinfo form and then operate it;
select * into cardinfo_temp from cardinfo
We view
select * into cardinfo_temp from DB.dbo.cardinfo
And then we're ready to start the operation.
We need to create a table for the data backup, of course, since it is a backup, the structure of the table must be the same, so we still follow the above method, copy a backup table, and then empty the data,
select * into cardinfo_bak from DB.dbo.cardinfo
Then empty the data from the backup table
delete cardinfo_bak
We confirm the data
select count(*) from cardinfo_bak
We're going to move all data beyond 3 months to the backup table.
delete from cardinfooutput deleted.* into cardinfo_bakwhere card_date_ts < dateadd(mm,-3,getdate())
We start execution, we have an error.
We have two solutions according to the error, but we use the second type;
2. Delete and rebuild the table DB_Bak.dbo.acc_monitor_log, when rebuilding the original identity column on the table on the identity (identity) property to remove, so we modify, right-click on the table---design---ID---represents the property---will be changed to No
Then save, we execute again, execute successfully;
Next, we're going to count data
select count(*) from cardinfo select count(*) from cardinfo_bak
If we want to manipulate the data within three months,
delete from cardinfooutput deleted.* into cardinfo_bakwhere card_date_tls between dateadd(mm,-3,getdate()) and getdate();
Again our environment is introduced to this, there are questions can give me a message, thank you
SQL Server Data Automation operations