SQL Server Data Automation operations

Source: Internet
Author: User
Tags getdate

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.