CODEWITHSUNDEEP
sqlsql-server
John W. Mnisi
John W. Mnisi

Reputation: 907

How to Insert SQL message tab output into a table?

How do I insert the message tab result of the below sql query into a TableTest2?

CREATE TABLE TableTest2 (InsertOutput VARCHAR(100))
CREATE TABLE TestTable (ID INT, msg VARCHAR(50))    
INSERT TestTable (ID, msg)
VALUES (1, 'Message 1'), (2,'Message 2')    
DROP table TestTable

Here's the result of running the above query:

(2 row(s) affected)

How do I insert the above result into TableTest2?

Upvotes: 5

Views: 2006

Answers (2)

Sebastian Meine
Sebastian Meine

Reputation: 11823

The only way to capture the actual output is by using C# (or any other non-sql language) to execute the command. That can be done in a CLR extension. The tSQLt database unit testing framework contains the procedure tSQLt.CaptureOutput to capture output for testing purposes. You can see how it uses an OnInfoMessage event handler in http://sourceforge.net/p/tsqlt/code/219/tree/tSQLtCLR/tSQLtCLR/TestDatabaseFacade.cs within the executeCommand method (starting at line 64):

    public SqlDataReader executeCommand(SqlString Command)
    {
        infoMessage = SqlString.Null;
        connection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
        SqlCommand cmd = new SqlCommand();

        cmd.Connection = connection;
        cmd.CommandText = Command.ToString();

        SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);

        return dataReader;
    }

    protected void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
    {
        if (infoMessage.IsNull)
        {
            infoMessage = "";
        }
        infoMessage += args.Message + "\r\n";
    }

If you just want to use this for testing purposes have a look at tSQLt.org The opensource tSQLt unit testing framework helps with a lot of things that are frequently encountered during automated testing.

Upvotes: 3

Yuriy Galanter
Yuriy Galanter

Reputation: 39807

In SQL SERVER @@ROWCOUNT return number of rows affected by an operation. You can do something like 

DECLARE @iRec int

INSERT TestTable (ID, msg)
VALUES (1, 'Message 1'), (2,'Message 2')    

SET @iRec = @@ROWCOUNT;

INSERT TableTest2(InsertOutput) 
VALUES (CAST(@iRec as VARCHAR(10)) + ' row(s) affected')

Upvotes: 2

Related Questions