Truncate or Insert data with conditions

  • Good day,

    I have two tables where A is the source and B is the destination, so what needs to be achieved, when A has no data don't truncate B when A has data truncate B and insert data from A. Need help

    if (select count(*) from [A]) > 0

    truncate table B

    Insert into B ([name],[userid],[ItemCreatedWhen])

    SELECT [Full Name],[UserID],getdate()

    FROM A

    go

    It's better to fail while trying, rather than fail without trying!!!

  • You just need BEGIN/END around the statement.

    Also, it will be more efficient to use EXISTS than SELECT COUNT(*).

    IF EXISTS(SELECT * FROM [A]) BEGIN

    TRUNCATE TABLE B;

    INSERT INTO B
    (
    [name],
    [userid],
    [ItemCreatedWhen]
    )
    SELECT [Full Name],
    [UserID],
    GETDATE()
    FROM A;

    END
  • To give the best chance of using minimal logging, be sure to lock the table that is being INSERTed into:

    ...

    INSERT INTO dbo.B WITH (TABLOCK)

    ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Jonathan AC Roberts wrote:

    You just need BEGIN/END around the statement.

    Also, it will be more efficient to use EXISTS than SELECT COUNT(*).

    IF EXISTS(SELECT * FROM [A]) BEGIN

    TRUNCATE TABLE B;

    INSERT INTO B
    (
    [name],
    [userid],
    [ItemCreatedWhen]
    )
    SELECT [Full Name],
    [UserID],
    GETDATE()
    FROM A;

    END

    What would happen to the data in table B if the INSERT fails?  Assume this is a staging process and some type of data validation is being done during the insert - and it fails, for example - the staging table has all varchars and there is an attempt to convert to a datetime data type but the values in the varchar column cannot be converted.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Jonathan AC Roberts wrote:

    You just need BEGIN/END around the statement.

    Also, it will be more efficient to use EXISTS than SELECT COUNT(*).

    IF EXISTS(SELECT * FROM [A]) BEGIN

    TRUNCATE TABLE B;

    INSERT INTO B
    (
    [name],
    [userid],
    [ItemCreatedWhen]
    )
    SELECT [Full Name],
    [UserID],
    GETDATE()
    FROM A;

    END

    What would happen to the data in table B if the INSERT fails?  Assume this is a staging process and some type of data validation is being done during the insert - and it fails, for example - the staging table has all varchars and there is an attempt to convert to a datetime data type but the values in the varchar column cannot be converted.

    The OP could wrap it in a BEGIN TRANSACTION / COMMIT TRANSACTION and a ROLLBACK in the CATCH if they are worried about that.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply