如果表B上没有运行查询，如何将数据从表A切换到表B.

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） 结束 返回@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要么创建一个新的 **  基于分区列的分区（如果还没有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 / > 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  - 意味着我们需要创建一个新分区 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 解决方案 你好， 当表格具有完全相同的结构时，可以使用 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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[fn_GetPartitionRangeValueForPartitionFunctionAndNumber](@PFName sysname,@PartitionNumber INT)RETURNS SQL_VARIANTASBEGIN/***************************************************************************Procedure: dbo.fn_GetPartitionRangeValueForPartitionFunctionAndNumberCreated By: Saru RadhakrishnanPurpose: To get the partition value for a given partition number and   given partition functionModification History:Date NameComment----------------------------------------------------------------------------07/07/2011Saru Radhakrishnan Initial version. ****************************************************************************/DECLARE @Value SQL_VARIANTIF @PartitionNumber > 0BEGINSELECT @Value = prv.value  FROM sys.partition_range_values prv  JOIN sys.partition_functions pfON pf.function_id = prv.function_idWHERE pf.name = @PFName   AND prv.boundary_id = @PartitionNumberENDRETURN @ValueENDGOQuery 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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue](@PFName sysname,@Value varchar(20))RETURNS INTASBEGIN/**************************************************************************Procedure: dbo.fn_GetPartitionNumberForPartitionFunctionAndValueCreated By: Saru RadhakrishnanPurpose: To get the partition number for a given partition function and  its valueModification History:Date NameComment----------------------------------------------------------------------------------------------------------07/07/2011Saru Radhakrishnan Initial version. ***********************************************************************************************************/DECLARE @PartitionNumber INTIF ISDATE(@Value) = 1 AND ISNUMERIC(@Value) = 0BEGINSELECT @PartitionNumber = prv.boundary_id  FROM sys.partition_range_values prv  JOIN sys.partition_functions pfON pf.function_id = prv.function_idWHERE pf.name = @PFName   AND prv.value <= CONVERT(SMALLDATETIME, @Value)   AND prv.value > DATEADD(DD, -1, CONVERT(SMALLDATETIME, @Value))   ENDIF ISNUMERIC(@Value) = 1BEGINSELECT @PartitionNumber = ROW_NUMBER() OVER (ORDER BY prv.value ASC)  FROM sys.partition_range_values prv  JOIN sys.partition_functions pfON pf.function_id = prv.function_idWHERE pf.name = @PFName   AND prv.value <= CONVERT(INT, @Value)ENDRETURN @PartitionNumberENDGOQuery 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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[Proc_CreateORGetPartitionNumber](@TableName sysname,@DateValue DATE = NULL,@SmallDateTimeValue SMALLDATETIME = NULL,@PartitionNumber INT = NULL OUTPUT)ASBEGIN/*** 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** DateName Comment** --------------------------------------------------------------------------------------------------------------** 07/07/2011Saru Radhakrishnan Initial Version** --------------------------------------------------------------------------------------------------------------*/SET NOCOUNT ONDECLARE @ErrorSeverityINTDECLARE @ErrorStateINTDECLARE @ExecutingProcedure VARCHAR(100)DECLARE @ErrorNumberINTDECLARE @ErrorMessageVARCHAR(256)DECLARE @ErrorProcedureVARCHAR(100)DECLARE @ErrorLineINTBEGIN TRY-- Pricing.ResultHistoryIF @TableName = 'dbo.SalesPartitionTable'BEGINIF @DateValue IS NOT NULLBEGINSELECT @PartitionNumber = [dbo].[fn_GetPartitionNumberForPartitionFunctionAndValue]('PF_dbo_SalesPartitionTable_SalesDate_ByWeek',@DateValue)IF @PartitionNumber IS NULL  -- means we need to create a new partitionBEGINALTER PARTITION FUNCTION PF_dbo_SalesPartitionTable_SalesDate_ByWeek()SPLIT RANGE (@DateValue)ALTER PARTITION SCHEME PS_dbo_SalesPartitionTable_SalesDate_ByWeekNEXT 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 TRYBEGIN CATCHSELECT@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 CATCHSET NOCOUNT OFF  ENDGO 解决方案 Hello,When the tables have exact the same structure incl indizes, then you can useALTER 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.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！