I'm trying to insert some data into a SQL Server table in batches, using multiple insert statements. I want it to print a statement after each insert, indicating whether the data has successfully been inserted.

I have the T-SQL code shown here. Instead of printing the 'Token 1 completed successfully'and 'Token 2 completed successfully' after each insert, it's printing both at the end, after each insert has completed.

How do I get each statement to print directly after each successful insert, instead of all at once at the end?

-- Token 1
BEGIN TRANSACTION;
BEGIN TRY

    INSERT INTO [my_db].[tokens_table] (table_name, record_id, token_type, token_value)
    SELECT DISTINCT 'crim_justice', CAST(id AS VARCHAR(16)), 'token_1', token_1 
    FROM crim_justice_tokens
    WHERE (id is not null) and (token_1 NOT LIKE '%XXX -%')

    COMMIT TRANSACTION;
    RAISERROR ('Token 1 completed successfully', 0, 1) WITH NOWAIT;
END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();  -- Store the error message in the variable

    ROLLBACK TRANSACTION;  -- Perform the rollback

    -- Print the error message
    RAISERROR ('Error inserting Token 1, rolling back', 0, 1) WITH NOWAIT;
    RAISERROR (@ErrorMessage, 0, 1) WITH NOWAIT;
END CATCH;

-- Token 2
BEGIN TRANSACTION;
BEGIN TRY
    INSERT INTO [my_db].[tokens_table] (table_name, record_id, token_type, token_value)
    SELECT DISTINCT 'crim_justice', CAST(id AS VARCHAR(16)), 'token_2', token_2 
    FROM crim_justice_tokens
    WHERE (id is not null) and (token_2 NOT LIKE '%XXX -%')

    COMMIT TRANSACTION;
    RAISERROR ('Token 2 completed successfully', 0, 1) WITH NOWAIT;
END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();  -- Store the error message in the variable

    ROLLBACK TRANSACTION;  -- Perform the rollback

    -- Print the error message
    RAISERROR ('Error inserting Token 2, rolling back', 0, 1) WITH NOWAIT;
    RAISERROR (@ErrorMessage, 0, 1) WITH NOWAIT;
END CATCH;

I'm trying to insert some data into a SQL Server table in batches, using multiple insert statements. I want it to print a statement after each insert, indicating whether the data has successfully been inserted.

I have the T-SQL code shown here. Instead of printing the 'Token 1 completed successfully'and 'Token 2 completed successfully' after each insert, it's printing both at the end, after each insert has completed.

How do I get each statement to print directly after each successful insert, instead of all at once at the end?

-- Token 1
BEGIN TRANSACTION;
BEGIN TRY

    INSERT INTO [my_db].[tokens_table] (table_name, record_id, token_type, token_value)
    SELECT DISTINCT 'crim_justice', CAST(id AS VARCHAR(16)), 'token_1', token_1 
    FROM crim_justice_tokens
    WHERE (id is not null) and (token_1 NOT LIKE '%XXX -%')

    COMMIT TRANSACTION;
    RAISERROR ('Token 1 completed successfully', 0, 1) WITH NOWAIT;
END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();  -- Store the error message in the variable

    ROLLBACK TRANSACTION;  -- Perform the rollback

    -- Print the error message
    RAISERROR ('Error inserting Token 1, rolling back', 0, 1) WITH NOWAIT;
    RAISERROR (@ErrorMessage, 0, 1) WITH NOWAIT;
END CATCH;

-- Token 2
BEGIN TRANSACTION;
BEGIN TRY
    INSERT INTO [my_db].[tokens_table] (table_name, record_id, token_type, token_value)
    SELECT DISTINCT 'crim_justice', CAST(id AS VARCHAR(16)), 'token_2', token_2 
    FROM crim_justice_tokens
    WHERE (id is not null) and (token_2 NOT LIKE '%XXX -%')

    COMMIT TRANSACTION;
    RAISERROR ('Token 2 completed successfully', 0, 1) WITH NOWAIT;
END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();  -- Store the error message in the variable

    ROLLBACK TRANSACTION;  -- Perform the rollback

    -- Print the error message
    RAISERROR ('Error inserting Token 2, rolling back', 0, 1) WITH NOWAIT;
    RAISERROR (@ErrorMessage, 0, 1) WITH NOWAIT;
END CATCH;

• sql
• sql-server

Share Follow edited Mar 7 at 20:03 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 7 at 19:53 DiamondJoe12DiamondJoe12 1,83399 gold badges5252 silver badges104104 bronze badges 13

• Could be because you have them both in same transaction? If you just want to print it out, why not just use PRINT('') ? – Brad Commented Mar 7 at 20:16
• PRINT is the same. It prints both statements after both insert statements have run. I need each print statement to be printed after the insert. I dont want them all at once at the end. – DiamondJoe12 Commented Mar 7 at 20:29
• How are you running this script? Raiserror with nowait is known to not work as expected in sql cmd. And in SSMS it only has the desired effect for the first 500 messages. And if you are using SSMS and are not over the 500 message limit are you sure they aren't being printed to the messages tab as it goes but you just don't see them until the end as it hasn't auto switched to it? – Martin Smith Commented Mar 7 at 20:39
• 1 Tds protocol is buffered so the messages don't always arrive immediately. You can run activity monitor instead to see the progress of your script – siggemannen Commented Mar 7 at 20:45
• 1 actually.. when i look in the 'messages' tab I think it's working....? – DiamondJoe12 Commented Mar 7 at 20:50

 |  Show 8 more comments

1 Answer 1

Sorted by: Reset to default 0

What you are using "RAISERROR" function is correct, if you use "PRINT" instead then it will print after all transactions are finished in current connection.

How do I get each statement to print directly after each successful insert, instead of all at once at the end?

it is already printing statement immediately after each successful insert, only thing is your query inserts only few records so both statements completed very quick so it looks like it printing all statement at once at the end of execution

I have already tried both approach with 500000 records insert in each query and found that "RAISERROR" using "WITH NOWAIT" prints messages immediately instead "PRINT" prints messages at the end of all transactions completed in current connection:

DECLARE @ErrorMessage AS NVARCHAR(MAX) = ''

BEGIN TRANSACTION;
BEGIN TRY
    RAISERROR ('Token 1 started', 0, 1) WITH NOWAIT;
    -- PRINT ('Token 1 started')

    INSERT INTO [dbo].[DETAILS]
    SELECT TOP 500000 [EMPNAME],[DEPT],[CONTACTNO],[CITY]
    FROM [dbo].[DETAILS]

    COMMIT TRANSACTION;
    RAISERROR ('Token 1 completed successfully', 0, 1) WITH NOWAIT;
    --PRINT ('Token 1 completed successfully') 
END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();  -- Store the error message in the variable

    ROLLBACK TRANSACTION;  -- Perform the rollback

    RAISERROR ('Error inserting Token 1, rolling back', 0, 1) WITH NOWAIT;
    RAISERROR (@ErrorMessage, 0, 1) WITH NOWAIT;
END CATCH;

-- Token 2
BEGIN TRANSACTION;
BEGIN TRY
    RAISERROR ('Token 2 started', 0, 1) WITH NOWAIT;
    -- PRINT ('Token 2 started')

    INSERT INTO [dbo].[DETAILS]
    SELECT TOP 500000 [EMPNAME],[DEPT],[CONTACTNO],[CITY]FROM [dbo].[DETAILS]

    COMMIT TRANSACTION;
    RAISERROR ('Token 2 completed successfully', 0, 1) WITH NOWAIT;
    --PRINT ('Token 2 completed successfully')
END TRY
BEGIN CATCH
    SET @ErrorMessage = ERROR_MESSAGE();  -- Store the error message in the variable

    ROLLBACK TRANSACTION;  -- Perform the rollback

    RAISERROR ('Error inserting Token 2, rolling back', 0, 1) WITH NOWAIT;
    RAISERROR (@ErrorMessage, 0, 1) WITH NOWAIT;
END CATCH;