Truncate or Insert data with conditions

  • smthembu

    Hall of Fame

    Points: 3451

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

  • Jonathan AC Roberts

    SSCoach

    Points: 17204

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

    SSC Guru

    Points: 98398

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeffrey Williams

    SSC Guru

    Points: 88324

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Jonathan AC Roberts

    SSCoach

    Points: 17204

    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 5 (of 5 total)

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