January 13, 2020 at 6:32 pm
Hi,
I have a SP to be able to archive a database ( from production to other database). i have got a problem with identity_insert.
As this is very huge tables, i work with set rowcount= xx to avoid lock.
Here a very simple example of my problem :
CREATE TABLE [dbo].[Table_1](
[KEYU] [int] IDENTITY(1,1) NOT NULL,
[DATA1] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table_2](
[KEYU] [int] IDENTITY(1,1) NOT NULL,
[DATA1] [varchar](50) NULL
) ON [PRIMARY]
GO
DECLARE @CPT integer;
SET NOCOUNT ON;
WHILE @CPT < 100
BEGIN
insert into Table_1 select concat('comm_',@cpt);
END
-- begin of script
DECLARE @SQLSTR varchar(500);
DECLARE @TOTALDEL INTEGER = 0;
declare @StayingAlive INTEGER = 1;
SET @SQLSTR = 'DELETE from Table_1 output KEYU,DATA1 into Table_2 where KEYU <=17;'
SET ROWCOUNT 10;
WHILE @StayingAlive = 1
BEGIN
exec (@SQLSTR);
SET @TOTALDEL = @TOTALDEL + @@ROWCOUNT;
IF @TOTALDEL >= 17
BEGIN
SET @StayingAlive = 0
END
END
As the table are the same in the both database, i have got the manage the Identity_insert on or off.
If i have it in two separate exec().
EXEC ('Set IDENTITY_INSERT Table_2 ON;');
EXEC('DELETE from Table_1 output KEYU,DATA1 into Table_2 where KEYU <=17;');
EXEC ('Set IDENTITY_INSERT Table_2 OFF;');
That doesn't work. I have got the message that i need to use Identity_insert
if i put identity insert in the same exec,
SET @SQLSTR = 'Set IDENTITY_INSERT Table_2 ON;DELETE from Table_1 output KEYU,DATA1 into Table_2 where KEYU <=17;Set IDENTITY_INSERT Table_2 OFF;'
that works, but in that case, @@rowcount = 0.
I could make my test differently, but i need to know exactly the number of rows deleted.
an idea ?
Thanks
January 13, 2020 at 6:52 pm
You need to use sp_executesql rather than just exec(), because sp_exec allows value(s) to be returned:
DECLARE @SQLSTR nvarchar(500)
DECLARE @DELCOUNT int
...
SET @SQLSTR = 'Set IDENTITY_INSERT Table_2 ON;DELETE from Table_1 output KEYU,DATA1 into Table_2 where KEYU <=17;Set @rowcount = @@ROWCOUNT; Set IDENTITY_INSERT Table_2 OFF;'
EXEC sp_executesql @SQLSTR, N'@rowcount int OUTPUT', @DELCOUNT OUTPUT
SET @TOTALDEL = @TOTALDEL + @DELCOUNT;
...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 13, 2020 at 7:36 pm
There is quite a lot wrong with your SQL, I've just pasted in something that will work.
drop table [dbo].[Table_1]
GO
drop table [dbo].[Table_2]
GO
CREATE TABLE [dbo].[Table_1](
[KEYU] [int] IDENTITY(1,1) NOT NULL,
[DATA1] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table_2](
[KEYU] [int] IDENTITY(1,1) NOT NULL,
[DATA1] [varchar](50) NULL
) ON [PRIMARY]
GO
DECLARE @CPT integer=0;
SET NOCOUNT ON;
WHILE @CPT < 100
BEGIN
insert into Table_1 select concat('comm_',@cpt);
set @CPT += 1
END
SELECT * FROM Table_1
SELECT * FROM [dbo].[Table_2]
-- begin of script
DECLARE @SQLSTR nvarchar(500);
DECLARE @TOTALDEL INTEGER = 0;
DECLARE @LASTDEL INTEGER = 0;
declare @StayingAlive INTEGER = 1;
SET @SQLSTR = 'SET IDENTITY_INSERT Table_2 ON;
DELETE TOP(10) from Table_1 output deleted.KEYU,deleted.DATA1 into Table_2(KEYU,DATA1) where KEYU <=17;
SET @LASTDEL = @@ROWCOUNT
SET IDENTITY_INSERT Table_2 ON;'
WHILE @StayingAlive = 1
BEGIN
EXEC sp_executesql @SQLSTR, N'@LASTDEL INTEGER OUTPUT', @LASTDEL OUTPUT;
SET @TOTALDEL += + @LASTDEL ;
PRINT CONCAT('@TOTALDEL=',@TOTALDEL)
IF @TOTALDEL >= 17 or @LASTDEL = 0 BEGIN
SET @StayingAlive = 0
END
END
SELECT * FROM Table_1
SELECT * FROM [dbo].[Table_2]
January 14, 2020 at 10:19 am
Thanks for your help.
That works fine.
Here the complete storeprocedure which could be useful.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Frederic Leurs
-- Create date: 2020/01/07
-- Description:Delete or archive rows from one table to another table
-- =============================================
ALTER PROCEDURE [dbo].[SP_Archive_Rows]
@SourceTable varchar(200) , -- source table to purge [tablename] format
@TargetTable varchar(200), -- dest table [database].[schema].[tablename] format
@ColumnToCheck varchar(200), -- date column name to check in source table
@AdditionnalWhere varchar(200)='', -- Additionnal where ' AND 1=1'
@purge_date varchar(50) = '19900101', -- default to 19900101 YYYYMMDD format
@Mode varchar(10) = 'ARCHIVE', -- ARCHIVE (COPY and DELETE) or DELETE
@RowBlock int = 50 ,-- default to 50 for batch size
@MaxElapseTime int = 10-- default to 10 minutes to run
AS
BEGIN
DECLARE @SqlStr NVARCHAR(max)
DECLARE @StayingAlive INT = 1
DEClARE @StartTime DATETIME = GETDATE()
DECLARE @NUMMINUTES INTEGER
DECLARE @CURRTIME DATETIME = GETDATE()
DECLARE @TABLEROWS INTEGER = 0
DECLARE @TOTALDEL INTEGER = 0
DECLARE @LASTDEL INTEGER = 0
DECLARE @ArchiveOrDelete VARCHAR(max) = ''
DECLARE @DeletedColumns VARCHAR(max) = ''
DECLARE @SelectedColumns VARCHAR(max) = ''
DECLARE @ParmDefinition nvarchar(500);
DECLARE @Champ varchar(50) = ',DELETED.'
DECLARE @SqlStrIdentityOnOff varchar(200);
DECLARE @SqlWhere as varchar(max);
--- count number of rows to delete or Archive
SET @SqlWhere = @ColumnToCheck + ' < ' + '''' + @purge_date + ''' ' + @AdditionnalWhere
SET @SqlStr = 'Set @Dynamicint = (select count(*) from ' + @SourceTable + ' where ' + @SqlWhere + ')'
EXEC sp_executesql
@SqlStr
,N'@Dynamicint int OUTPUT'
,@TABLEROWS OUTPUT
SET ROWCOUNT @RowBlock -- set the rowcount to the passed batch size
SET NOCOUNT ON -- DONT DISPLAY NUMBER OF ROWS MESSAGES
PRINT 'Execution Mode : ' + @Mode
PRINT 'Source Table : ' + @SourceTable
PRINT 'Target Table : ' + @TargetTable
PRINT 'Purge : ' + @SqlWhere
PRINT CONVERT(VARCHAR(10),@TABLEROWS) + ' rows will be ' + @Mode
-- Construct sql delete statement
IF (@Mode = 'ARCHIVE')
BEGIN
------Create the destination table if it doesn't exist
IF OBJECT_ID(@TargetTable, N'U') IS NULL
BEGIN
SET @SqlStr = 'Select * into ' + @TargetTable + ' from ' + @SourceTable + ' where 1 = 0'
EXEC (@SqlStr)
PRINT 'Destination Table ' + @TargetTable + ' has been created successfuly'
END
SET @SqlStr = 'Set @DynamicChar = (SELECT stuff( (SELECT '''+@Champ+'''+C.name
FROM sys.schemas AS S
INNER JOIN sys.tables AS T
ON S.schema_id = T.schema_id
INNER JOIN sys.columns AS C
ON C.object_id = T.object_id
WHERE T.name = @SourceTable
ORDER BY T.name, C.column_id
FOR XML PATH(''''), TYPE).value(''.'', ''varchar(max)'')
,1,1,'''')
AS Colonnes)';
SET @ParmDefinition = N'@SourceTable varchar(50)
,@DynamicChar varchar(4000) OUTPUT';
EXEC sp_executesql
@SqlStr
,@ParmDefinition
,@SourceTable
,@DeletedColumns OUTPUT;
SET @SelectedColumns = stuff(REPLACE(@DeletedColumns,',DELETED.',','),1,8,'');
SET @ArchiveOrDelete = ' OUTPUT ' + @DeletedColumns + ' INTO ' + @TargetTable + '(' + @SelectedColumns + ')';
END
SET @SqlStr = 'DELETE ' + @SourceTable + @ArchiveOrDelete + ' where ' + @SqlWhere+';'
IF (@Mode = 'ARCHIVE')
BEGIN
set @SqlStrIdentityOnOff = 'Set IDENTITY_INSERT ' + @TargetTable ;
set @SqlStr = @SqlStrIdentityOnOff + ' ON; '+ @SqlStr;
SET @SqlStr += 'SET @LASTDEL = @@ROWCOUNT; ';
SET @SqlStr += @SqlStrIdentityOnOff + + ' OFF; ';
END
--PRINT @SqlStr;
--GOTO EndSP;
-----------------------------------
--- main loop
-----------------------------------
WHILE @StayingAlive = 1
BEGIN
BEGIN try
BEGIN TRANSACTION SQLDELETE;
EXEC sp_executesql @SQLSTR, N'@LASTDEL INTEGER OUTPUT', @LASTDEL OUTPUT;
SET @TOTALDEL += @LASTDEL;
COMMIT
END Try
BEGIN catch
SELECT
@Mode as 'Mode',@SourceTable as 'SourceTable', CONVERT(VARCHAR(10),@TABLEROWS) as 'ToProcess',CONVERT(VARCHAR(10),@TOTALDEL) as 'Processed', CONVERT(VARCHAR(10),@NUMMINUTES) as 'ElapseTime',@SQLWhere as WhereClause,
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
Rollback Transaction SQLDELETE;
GOTO EndSP;
end catch;
SET @CURRTIME = GETDATE()
SET @NUMMINUTES = datediff(MINUTE, @StartTime,@CURRTIME)
PRINT 'Total Processed so far = ' + CONVERT(VARCHAR(10),@TOTALDEL) + ' in ' + CONVERT(VARCHAR(10),@NUMMINUTES)+' Minute(s)'
--check if all rows processed
IF @TOTALDEL >= @TABLEROWS
BEGIN
PRINT 'TOTAL ROWS DELETED = ' + CONVERT(VARCHAR(10),@TOTALDEL)
SET @StayingAlive = 0
END
---- check if number of minutes to run has expired.
IF @NUMMINUTES > @MaxElapseTime
BEGIN
PRINT 'RUN TIME EXCEDDED'
PRINT 'TOTAL ROWS DELETED = ' + CONVERT(VARCHAR(10),@TOTALDEL)
SET @StayingAlive = 0
END
---------------------------------------------
--- end WHILE
---------------------------------------------
END
-- commit last transaction if still open
IF @@TRANCOUNT > 0
BEGIN
commit
END
SELECT @Mode as 'Mode',@SourceTable as 'SourceTable', CONVERT(VARCHAR(10),@TABLEROWS) as 'ToProcess',CONVERT(VARCHAR(10),@TOTALDEL) as 'Processed', CONVERT(VARCHAR(10),@NUMMINUTES) as 'ElapseTime',@SQLWhere as WhereClause, 0 as ErrorNumber, '' as ErrorMessage;
---------------------------------------------
--- end StoreProcedure
---------------------------------------------
EndSP:
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply