如果表B上没有运行查询，如何将数据从表A切换到表B. [英] How to switch data from a Table A to Table B when there is no query running on Table B
问题描述
我有2个表A，B，它们具有相同的列。表B用于Tableau报告。表A是一个临时表，其中包含来自源系统的新数据。
如果在表B上没有运行Query，如何将数据从表A切换到表B？
我需要这样做以避免表B停机，并确保表B始终可供用户使用非常感谢！
示例分区和交换分区：
查询1：
IF EXISTS（SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N'[dbo]。[fn_GetPartitionRangeValueForPartitionFunctionAndNumber]'）并输入（N'FN'，N'IF'，N'TF'，N'FS'，N' FT'））
DROP FUNCTION [dbo]。[fn_GetPartitionRangeValueForPartitionFunctionAndNumber]
GO
SET ANSI_NULLS ON < br $>
GO
SET QUOTED_IDENTIFIER ON¥b $ b GO
CREATE FUNCTION [dbo]。[fn_GetPartitionRangeValueForPartitionFunctionAndNumber]
（
@PFName sysname，
@PartitionNumber INT
）
返回SQL_VARIANT
AS
BEGIN
/ ************* ************************************************** ************
程序
：dbo.fn_GetPartitionRangeValueForPartitionFunct ionAndNumber
由 创建
：Saru Radhakrishnan
目的
：获取给定分区号的分区值和
给定分区函数
修改历史记录：
日期 名称
评论
--------- -------------------------------------------------- -----------------
07/07/2011
Saru Radhakrishnan 初始版本。
*************** *************************************** / b
DECLARE @Value SQL_VARIANT
IF @PartitionNumber> 0
BEGIN
SELECT @Value = prv.value
FROM sys.partition_range_values prv
JOIN sys.partition_functions pf
ON pf.function_id = prv.function_id
WHERE pf.name = @PFName
AND prv.boundary_id = @PartitionNumber
结束
RETURN @Value
结束
GO
查询2：
IF EXISTS（SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N'[dbo]。[fn_GetPartitionNumberForPartitionFunctionAndValue]'）并输入（N'FN'，N'IF'，N'TF'，N'FS'，N' FT'））
$
DROP FUNCTION [dbo]。[fn_GetPartitionNumberForPartitionFunctionAndValue]
$
GO
SET ANSI_NULLS ON < br $>
GO
SET QUOTED_IDENTIFIER ON¥b $ b GO
CREATE FUNCTION [dbo]。[fn_GetPartitionNumberForPartitionFunctionAndValue]
（
@PFName sysname，
@Value varchar（20）
）
RETURNS INT
AS
BEGIN
/ ********** ************************************************** **************
程序
：dbo.fn_GetPartitionNumberForPartitionFunctionAndValue
由 创建
：Saru Radhakrishnan
目的
：获取a的分区号给定分区功能和
其值为
修改历史记录：
日期 名称
评论
---------- -------------------------------------------------- ----------------------------------------------
07/07/2011
Saru Radhakrishnan 初始版本。
******** ************************************************** ******************* /
DECLARE @ PartitionNumber INT
IF ISDATE（@Value）= 1 AND ISNUMERIC（@ Value）= 0
BEGIN
SELECT @PartitionNumber = prv.boundary_id
FROM sys.partition_range_values prv
JOIN sys.partition_functions pf
ON pf.function_id = prv.function_id
WHERE pf.name = @PFName
AND prv.value< = CONVERT（SMALLDATETIME，@ Value）
AND prv.value> DATEADD（DD，-1，CONVERT（SMALLDATETIME，@ Value））
结束
IF ISNUMERIC（@ Value）= 1
BEGIN
SELECT @PartitionNumber = ROW_NUMBER（）OVER（ORv BY prv.value ASC）
&NBSP; FROM sys.partition_range_values prv
JOIN sys.partition_functions pf
ON pf.function_id = prv.function_id
WHERE pf.name = @PFName
AND prv.value< = CONVERT（INT，@ Value）
结束
< span style ="white-space：pre">
返回@PartitionNumber
结束
GO
查询3：
IF EXISTS（SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N'[dbo]。[Proc_CreateORGetPartitionNumber]'）并键入（N'P'，N'PC'））
DROP PROCEDURE [ dbo]。[Proc_CreateORGetPartitionNumber]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
创建程序[dbo]。[Proc_CreateORGetPartitionNumber]
（
@TableName sysname，
@DateValue DATE = NULL，
@SmallDateTimeValue SMALLDATETIME = NULL，
@PartitionNumber INT = NULL OUTPUT
）
AS
BEGIN
/ *
**名称
：dbo.Proc_CreateORGetPartitionNumber
**目的
：对于任何已分区的表，此proc要么创建一个新的
< span style ="white-space：pre"> ** 基于分区列的分区（如果还没有b
** 存在）或获取已存在的分区编号
** 基于分区列的分区，使用输入参数
** < /跨度>&NBSP; 。值&NBSP;最后将分区号返回给调用者。
**
** Exmaple
：DECLARE @PartitionNumber INT
** EXEC dbo.Proc_CreateORGetPartitionNumber @TableName ='dbo.SalesPartiotionTable'，
** @DateValue = '07 / 01/2011'，
** @PartitionNumber = @PartitionNumber OUTPUT
**
**修改历史记录
**日期
名称 评论
** ----------------------------------------------- -------------------------------------------------- -------------
** 07/07/2011
Saru Radhakrishnan 初始版本
** ---------------------------------------- -------------------------------------------------- --------------------
* /
设置NOCOUNT ON / br / >
INT
DECLARE @ErrorState
INT
DECLARE @ExecutingProcedure VARCHAR（100）
DECLARE @ErrorNumber
INT
DECLARE @ErrorMessage
VARCHAR（256）
DECLARE @ErrorProcedure
VARCHAR（100）
DECLARE @ErrorLine
INT
BEGIN TRY
- Pricing.ResultHistory
IF @TableName ='dbo.SalesPartitionTable'
BEGIN
IF @ DateValue IS NOT NULL
BEGIN
SELECT @PartitionNumber = [dbo]。[fn_GetPartitionNumberForPartitionFunctionAndValue]
（
'PF_dbo_SalesPartitionTable_SalesDate_ByWeek'，
@DateValue
）
IF @PartitionNumber IS NULL - 意味着我们需要创建一个新分区
BEGIN
ALTER PARTITION FUNCTION PF_dbo_SalesPartitionTable_SalesDate_ByWeek（）
SPLIT RANGE（@DateValue）
ALTER PARTITION SCHEME PS_dbo_SalesPartitionTable_SalesDate_ByWeek
下一个使用[主要]
SELECT @PartitionNumber = [dbo]。[fn_GetPartitionNumberForPartitionFunctionAndValue]
（
'PF_dbo_SalesPartitionTable_SalesDate_ByWeek'，
@DateValue
）
END / * IF @PartitionNumber IS NULL * /
END / *如果@DateValue不是NULL * /
END / * IF @TableName ='dbo.SalesPartitionTable'* / /
END TRY
BEGIN CATCH
SELECT
@ ErrorSeverity = ERROR_SEVERITY（），
@ErrorState = ERROR_STATE（），
@ErrorNumber = ERROR_NUMBER（），
@ErrorMessage = ERROR_MESSAGE（），
@ErrorProcedure = ERROR_PROCEDURE（），
@ErrorLine = ERROR_LINE（ ）
RAISERROR（'过程中遇到的SQL错误：％s。错误：％d。消息：％s过程：％s。行：％d。'，
@ ErrorSeverity，@ ErrorState，@ ExetingProcedure，@ ErrorNumber，@ ErrorMessage，
@ErrorProcedure，@ ErrorLine）
END CATCH
设置NOCOUNT OFF
结束
GO
< blockquote>
你好，
当表格具有完全相同的结构时，可以使用
ALTER TABLE PARTITION SWITCH ，即使在快递版。
目标表必须为空，因此首先将第三个空表添加到Switch中。
i have 2 Table A, B, which have the same columns. Table B is Used for Tableau Reports. Table A is a temporary Table which has new Data from source System. How to switch the Data from Table A to Table B when there is no Query running on Table B?
i need to do that to avoid downtime on Table B and make sure that Table B is always available for Users Thankyou very much!
sample partition and switch partition:
Query 1:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetPartitionRangeValueForPartitionFunctionAndNumber]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetPartitionRangeValueForPartitionFunctionAndNumber]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetPartitionRangeValueForPartitionFunctionAndNumber]
(
@PFName sysname,
@PartitionNumber INT
)
RETURNS SQL_VARIANT
AS
BEGIN
/***************************************************************************
Procedure
: dbo.fn_GetPartitionRangeValueForPartitionFunctionAndNumber
Created By
: Saru Radhakrishnan
Purpose
: To get the partition value for a given partition number and
given partition function
Modification History:
Date Name
Comment
----------------------------------------------------------------------------
07/07/2011
Saru Radhakrishnan Initial version.
****************************************************************************/
DECLARE @Value SQL_VARIANT
IF @PartitionNumber > 0
BEGIN
SELECT @Value = prv.value
FROM sys.partition_range_values prv
JOIN sys.partition_functions pf
ON pf.function_id = prv.function_id
WHERE pf.name = @PFName
AND prv.boundary_id = @PartitionNumber
END
RETURN @Value
END
GO
Query 2:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))DROP FUNCTION [dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]
(
@PFName sysname,
@Value varchar(20)
)
RETURNS INT
AS
BEGIN
/**************************************************************************
Procedure : dbo.fn_GetPartitionNumberForPartitionFunctionAndValue
Created By : Saru Radhakrishnan
Purpose : To get the partition number for a given partition function and
its value
Modification History:
Date Name Comment
----------------------------------------------------------------------------------------------------------
07/07/2011 Saru Radhakrishnan Initial version.
***********************************************************************************************************/
DECLARE @PartitionNumber INT
IF ISDATE(@Value) = 1 AND ISNUMERIC(@Value) = 0
BEGIN
SELECT @PartitionNumber = prv.boundary_id
FROM sys.partition_range_values prv
JOIN sys.partition_functions pf
ON pf.function_id = prv.function_id
WHERE pf.name = @PFName
AND prv.value <= CONVERT(SMALLDATETIME, @Value)
AND prv.value > DATEADD(DD, -1, CONVERT(SMALLDATETIME, @Value))
END
IF ISNUMERIC(@Value) = 1
BEGIN
SELECT @PartitionNumber = ROW_NUMBER() OVER (ORDER BY prv.value ASC)
FROM sys.partition_range_values prv
JOIN sys.partition_functions pf
ON pf.function_id = prv.function_id
WHERE pf.name = @PFName
AND prv.value <= CONVERT(INT, @Value)
END
RETURN @PartitionNumber
END
GO
Query 3:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_CreateORGetPartitionNumber]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[Proc_CreateORGetPartitionNumber]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Proc_CreateORGetPartitionNumber]
(
@TableName sysname,
@DateValue DATE = NULL,
@SmallDateTimeValue SMALLDATETIME = NULL,
@PartitionNumber INT = NULL OUTPUT
)
AS
BEGIN
/*
** Name : dbo.Proc_CreateORGetPartitionNumber
** Purpose : For any table that is partitioned, this proc either creates a new
** partition based on the partitioned column (if one does not already
** exists) or obtains the partitioned number of an already existing
** partition based on the partitioned column, using the input parameter
** value. Finally returns the partition number to the caller.
**
** Exmaple : DECLARE @PartitionNumber INT
** EXEC dbo.Proc_CreateORGetPartitionNumber @TableName = 'dbo.SalesPartiotionTable',
** @DateValue = '07/01/2011',
** @PartitionNumber = @PartitionNumber OUTPUT
**
** Modification History
** Date Name Comment
** --------------------------------------------------------------------------------------------------------------
** 07/07/2011 Saru Radhakrishnan Initial Version
** --------------------------------------------------------------------------------------------------------------
*/
SET NOCOUNT ON
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ExecutingProcedure VARCHAR(100)
DECLARE @ErrorNumber INT
DECLARE @ErrorMessage VARCHAR(256)
DECLARE @ErrorProcedure VARCHAR(100)
DECLARE @ErrorLine INT
BEGIN TRY
-- Pricing.ResultHistory
IF @TableName = 'dbo.SalesPartitionTable'
BEGIN
IF @DateValue IS NOT NULL
BEGIN
SELECT @PartitionNumber = [dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]
(
'PF_dbo_SalesPartitionTable_SalesDate_ByWeek',
@DateValue
)
IF @PartitionNumber IS NULL -- means we need to create a new partition
BEGIN
ALTER PARTITION FUNCTION PF_dbo_SalesPartitionTable_SalesDate_ByWeek()
SPLIT RANGE (@DateValue)
ALTER PARTITION SCHEME PS_dbo_SalesPartitionTable_SalesDate_ByWeek
NEXT USED [PRIMARY]
SELECT @PartitionNumber = [dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]
(
'PF_dbo_SalesPartitionTable_SalesDate_ByWeek',
@DateValue
)
END /* IF @PartitionNumber IS NULL */
END /* IF @DateValue IS NOT NULL */
END /* IF @TableName = 'dbo.SalesPartitionTable' */
END TRY
BEGIN CATCH
SELECT @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorLine = ERROR_LINE()
RAISERROR ('SQL Error trapped in Procedure:%s. Error:%d. Message:%s Procedure:%s. Line:%d.',
@ErrorSeverity, @ErrorState, @ExecutingProcedure, @ErrorNumber, @ErrorMessage,
@ErrorProcedure, @ErrorLine)
END CATCH
SET NOCOUNT OFF
END
GO
Hello,
When the tables have exact the same structure incl indizes, then you can use ALTER TABLE PARTITION SWITCH , that works even in the Express Edition.
The target table must be empty, so add a third empty table to Switch first to that one.
这篇关于如果表B上没有运行查询，如何将数据从表A切换到表B.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
