Updating field from one table to another sql
USE Test DB GO SET NOCOUNT ON SELECT 'DELETE FROM Purchase Order Detail WHERE Purchase Order ID = ' CAST(Purchase Order ID AS VARCHAR(50)) ' AND Purchase Order Detail ID = ' CAST(Purchase Order Detail ID AS VARCHAR(50)) ';' CHAR(13) CHAR(10) 'GO' FROM Purchase Order Detail WHERE Due Date The previous query contains a few things that will allow us to execute all the generated delete statements directly using the SQLCMD utility.
The semicolon (;) is used as the statement terminator, it marks the end of the statement.
When I was asked to remove historical data from a 120 GB unpartitioned table for an online payment service I tried different approaches, all with the same basic idea of limiting blocking issues.
In this tip, we will look at different options for doing this and my final solution that I used.
At first you may be tempted to execute the following statement.
Since there is so much data to delete, this will cause a lot of blocking and also take a long time to complete.
We can modify the TOP value from 1 to N for our needs.
At this point we have seen different options to delete small amounts of data at a time, but we need to figure out a way to accomplish this task without the need to be in front of the computer the entire time. If you perform the delete inside a cursor your transaction log could grow disproportionally, even if your database is using the simple recovery model.
The primary key is based on Purchase Order ID and Purchase Order Detail ID and this query is deleting based on the primary key.
In the previous two scripts we are deleting 5000 rows at a time, but in some cases that wont avoid blocking issues.
When performing a DELETE, the Database Engine needs to hold a lock on the rows being deleted in order to maintain data consistency.
But when you delete a considerable amount of data the row locking escalates into a page or even a table lock which causes blocking issues.I have a large table with millions of historical records.I was asked to remove millions of old records from this table, but this table is accessed by our system 24x7, so there isn't a good time to do massive deletes without impacting the system.For the purpose of this tip I used the Purchase Order Detail table of the Adventure Works database and inserted a lot of data. USE [master] GO CREATE DATABASE [Test DB] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Test DB_file1', FILENAME = N'E:\MSSQL\Test DB_1.mdf', SIZE = 128MB , MAXSIZE = UNLIMITED, FILEGROWTH = 64MB) LOG ON ( NAME = N'Test DB_log_file1', FILENAME = N'E:\MSSQL\Test DB_1.ldf', SIZE = 64MB, MAXSIZE = 2048GB, FILEGROWTH = 32MB) GO USE Test DB GO CREATE TABLE[Purchase Order Detail] ( [Purchase Order ID] [int] NOT NULL, [Purchase Order Detail ID] [int] NOT NULL IDENTITY(1, 1), [Due Date] [datetime] NOT NULL, [Order Qty] [smallint] NOT NULL, [Product ID] [int] NOT NULL, [Unit Price] [money] NOT NULL, [Line Total] AS (isnull([Order Qty]*[Unit Price],(0.00))), [Received Qty] [decimal] (8, 2) NOT NULL, [Rejected Qty] [decimal] (8, 2) NOT NULL, [Stocked Qty] AS (isnull([Received Qty]-[Rejected Qty],(0.00))), [Modified Date] [datetime] NOT NULL CONSTRAINT [DF_Purchase Order Detail_Modified Date] DEFAULT (getdate()) ) ON [PRIMARY] GO ALTER TABLE [Purchase Order Detail] ADD CONSTRAINT [CK_Purchase Order Detail_Order Qty] CHECK (([Order Qty]=(0.00))) GO ALTER TABLE [Purchase Order Detail] ADD CONSTRAINT [PK_Purchase Order Detail_Purchase Order ID_Purchase Order Detail ID] PRIMARY KEY CLUSTERED ([Purchase Order ID], [Purchase Order Detail ID]) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_Purchase Order Detail_Product ID] ON [Purchase Order Detail] ([Product ID]) ON [PRIMARY] GO USE Test DB; GO INSERT INTO dbo.