none
How to switch data from a Table A to Table B when there is no query running on Table B

    질문

  • 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!

    • 편집됨 Phalondon 2018년 6월 15일 금요일 오전 4:19
    2018년 6월 15일 금요일 오전 4:12

모든 응답

  • 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.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    2018년 6월 15일 금요일 오전 5:51
  • See illustration here

    https://www.cathrinewilhelmsen.net/2015/04/19/table-partitioning-in-sql-server-partition-switching/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 15일 금요일 오전 6:41
  • In addition to the other posts, you can also do:

    BEGIN TRANSACTION

    EXEC sp_rename B, C
    EXEC sp_rename A, B
    EXEC sp_rename C, A

    COMMIT TRANSACTION

    Or if you don't want to keep the data in B:

    BEGIN TRANSACTION

    DROP TABLE B
    EXEC sp_rename A, B

    COMMIT TRANSACTION

    No matter how you do it, the table will be unavailable for a short moment, but that should be less than a second.

    2018년 6월 15일 금요일 오전 7:08
  • If there is a process in place to know when to switch, perhaps you can use a SYNONYM to point to the table you want used at the time, and query it instead.
    2018년 6월 15일 금요일 오전 11:24
    중재자
  • If there is a process in place to know when to switch, perhaps you can use a SYNONYM to point to the table you want used at the time, and query it instead.

    Absolutely the best idea so far!

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    2018년 6월 15일 금요일 오후 9:28
  • Wow, thank you. I consider that high praise coming from you.

    Fwiw, i was working on a project in Oracle where a batch process had to check a user list against an LDAP data file that we FTPed and loaded from a source every night. To keep everything running smoothly, we had two tables with a synonym. When the synonym pointed to A, at night time we loaded B and then changed the synonym to point to B. The following night we would load A and then point the synonym to A. (The process was automated.) This way the data was fresh, but the system never went down. Worked beautifully.

    2018년 6월 16일 토요일 오전 12:12
    중재자