December 24, 2019 at 8:55 am
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!!!
December 24, 2019 at 2:22 pm
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
December 24, 2019 at 3:34 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
December 24, 2019 at 4:43 pm
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
December 24, 2019 at 5:34 pm
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;
ENDWhat 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