CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
user3241738
user3241738

Reputation: 67

Insert results from Stored Procedure into table and add an extra column

I'm trying to insert data from a stored procedure into a table for example,

INSERT INTO FailedLogins
EXEC sp_readerrorlog 0, 1, 'Login failed'

This works fine, however, I'm running this against hundreds of databases using SSIS so I need to add @@SERVERNAME into my FailedLogins table for each record to match the corresponding server.

So the result set should look as follows:

LogDate  ServerName  ProcessInfo  Text

Upvotes: 3

Views: 11489

Answers (1)

Anon
Anon

Reputation: 10908

DECLARE @readerrorlog_results TABLE (
  LogDate date,
  ProcessInfo varchar(max),
  Text varchar(max)
)

INSERT INTO @readerrorlog_results
      (LogDate,ProcessInfo,Text)
EXEC sp_readerrorlog 0, 1, 'Login failed'

INSERT INTO FailedLogins
      (LogDate,  ServerName,ProcessInfo,Text)
SELECT LogDate,@@SERVERNAME,ProcessInfo,Text FROM @readerrorlog_results

Upvotes: 1

Related Questions