Truncate or Insert data with conditions

Question

Post reply

Truncate or Insert data with conditions

smthembu

Hall of Fame

Points: 3464
More actions
December 24, 2019 at 8:55 am

#3710182

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

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

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

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: 101095 More actions · Answer 2

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

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.