June 29, 2011 at 1:41 pm
Two points, first the critique.. Using select into in this context could cause problems if the table was changed and your sproc should include a CREATE TABLE with the full definition and since this table is persisted you should probably just create it and leave it there.
I'm guessing some wierd query plan problem but I have a way around it, if you execute the build of the table as dynamic SQL it should resolve the issue.
EXEC ( 'IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = ''ArchiveTable'') SELECT * INTO dbo.ArchiveTable FROM dbo.Over WHERE 1 = 2' )
CEWII
June 29, 2011 at 1:56 pm
I think I figured it out. The main table has an identity column and that is created when I do the SELECT INTO. Then no records are getting added because of the IDENTITY FAILURE.
So I have to find a way to dynamically create the table without the identity.
June 29, 2011 at 2:25 pm
One solution is to put the whole DDL statement to create the table in the code instead of the SELECT into. For the field with a identity, change it to just a number. Your data move from Over to Archive should work then.
Another solution is to specify exact columns in the INSERT INTO Archive (COL2, COL3, .. COLX) SELECT COL2, COL3, .. COLX.
Please note, I am skipping over the indentity field since it is incremented and updated by SQL Server.
John Miner
Crafty DBA
www.craftydba.com
June 30, 2011 at 2:52 am
There's no need for the transaction or separate insert/delete operation, and the identity issue can be resolved by using a SET option:
IFOBJECT_ID(N'dbo.pArchiveLogs', N'P')
IS NULL
EXECUTE ('CREATE PROCEDURE dbo.pArchiveLogs AS');
ALTER PROCEDURE dbo.pArchiveLogs
(
@Days INTEGER = 120
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
-- Create the archive table if necessary
IFOBJECT_ID(N'dbo.ArchiveTable'
IS NULL
BEGIN
SELECT TOP (0)
*
INTO dbo.ArchiveTable
FROM dbo.SourceTable;
END
-- Allow identity column inserts
SET IDENTITY_INSERT dbo.ArchiveTable ON;
-- Atomic delete and insert
INSERT dbo.ArchiveTable WITH (TABLOCKX)
SELECT
d.*
FROM
(
DELETE dbo.SourceTable
OUTPUT DELETED.*
WHERE insert_date_time >= DATEADD(DAY, -@Days, SYSDATETIME())
) AS d;
SET IDENTITY_INSERT dbo.ArchiveTable OFF;
END;
GO
Use real column names rather than the star syntax I used (I can't see the table definition from here).
June 30, 2011 at 6:09 am
Hi Crazy Eights,
I have used the identity insert before on bulk loads when I wanted to keep the identity column the same and not change the value to a plain old number.
I do not understand the advantage of a insert/delete with a exclusive lock over two statements in a transaction.
If both statements do not complete successfully in the same transaction, I think the rollback will return the data to the previous state?
I might see the advantage of your solution if you think someone is going to execute this code more than once at the same time ...
Cheers
John
John Miner
Crafty DBA
www.craftydba.com
June 30, 2011 at 6:18 am
j.miner (6/30/2011)
I do not understand the advantage of a insert/delete with a exclusive lock over two statements in a transaction.
Hi John,
The TABLOCKX isn't required - I just added it out of habit because it may enable minimal logging with INSERT on SQL Server 2008 and above. The INSERT...DELETE combination is naturally atomic and does not require an explicit transaction. Another advantage is that it results in a single query plan whereas the two-statement approach executes twice, inevitably performing more work.
June 30, 2011 at 12:41 pm
CELKO (6/30/2011)
We would have a column for the status each row instead of physically moving them. “Archived” would be one of the status values. No need for an extra table. No need for extra disk access. No need to worry about duplicated effort. And it less code.
That is certainly an option, one could also partition the table on Enterprise Edition (or use a local partitioned view on other editions), so that old data could be stored read-only on separate a file group. There might be disadvantages to keeping all the data in the same, unpartitioned, table.
Viewing 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply