How To Get Output Into SQL Server Table

Have you every needed to get the information returned from a
stored procedure (SP) into a SQL Server table? Or have you wondered how you
might get the contents of an operating system (OS) file into a SQL Server
table? How about placing the output of some Windows executable into a SQL
Server table so you can manipulate it with T-SQL in some way? So, what
methods can you use to accomplish these different, but similar tasks? This
article will show you how to insert rows into a SQL Server table from various
sources.

Getting Output from a Stored Procedure into a Table

The most commonly asked question I get is "How can I
get the output of a SP into a SQL Server table." Now, if you did not know
how to do this you might be inclined to just copy the code from the stored
procedure and paste it into your T-SQL code. This way your code can insert the
record set into a table instead of trying to get it from an existing SP. However,
there are alternatives to this cut and paste method. You can use the “execute_statement”
option on an “INSERT” statement, to get the output of a stored procedure into a
table easily. Here is an example of getting the output of the “sp_who” SP into
a table:

set nocount on

create table #sp_who (

  spid		smallint,

  ecid		smallint,

  status	nchar(30),

  loginame	nchar(128),

  hostname	nchar(128),

  blk		char(5),

  dbname	nchar(128),

  cmd		nchar(16))

insert into #sp_who execute sp_who

select * from #sp_who

drop table #sp_who

As you can see, this is a very simple process. I first
created a table that contains a column for each column returned from the “sp_who”
SP. In my example, each column of my temporary table has the same name, data
type and length as those returned from the “sp_who” SP. After the table is
created, all it takes to get the SP’s output into a table is to issue an INSERT
statement that executes the “sp_who” stored procedure. Once this INSERT
statement completes SQL Server will place all the records displayed by the “sp_who”
SP in the temporary table #sp_who. Using this method allows me then to
manipulate the “sp_who” output however I would like. If a store procedure
outputs multiple record sets then the above method, to get the output of the
stored procedure into a table, will not work. In fact, if you try it you will
receive an error.

Getting the Content of an OS File into a Table

Now suppose that you want to get the contents of an OS file
into a T-SQL table.
I am not talking about the raw, comma delimited text file here, but instead I
am referring to just a plan unformatted text file. Do you have any ideas how
this might be accomplished? It is quite easy to accomplish this with “xp_cmdshell”
and a one-column table. Let’s go through an example of how this would be
done. For my example, let’s get the current SQL Server ERRORLOG file into a
temporary table. Here is the code to do that:

create table #errorlog(line varchar(2000))

insert into #errorlog

  execute xp_cmdshell ‘type “C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG” ‘

select line from #errorlog

drop table #errorlog

As you can see, I have basically used the same method as
getting a stored procedure’s output into a table. Except this time I used the xp_cmdshell
extended stored procedure to execute the DOS “type” command. The DOS “type”
command just displays the content of the file specified. Therefore by using
the execute clause on the INSERT statement in conjunction with the “type”
command of DOS I was able to get the contents of the ERRORLOG OS file into a
SQL Server table.

Now I can use T-SQL to manipulate the data from an OS file
by using this process. The only drawback here is each line in the OS file is
stored in a single column within a row. Therefore, if you want to search and
extract stuff you will need to use the CHARINDEX, PATINDEX, and/or SUBSTRING
functions.

Getting Multiple Record Sets into a Table

Remember earlier when I said if a store procedure returns multiple
record sets that you get an error when trying to use the EXECUTE clause of an
INSERT statement. Now let me show you a way to get around this issue. The
trick to making this work is to first route the output from the SP into an OS
file, then use the method I showed you above to get the OS file into a SQL
Server table. Let me go through an example.

Say I want to get the output of the “sp_spaceused” SP, into
a SQL Server table. Because it produces multiple record sets, I am not able to
use the first method I discussed above. Here is some code that will allow me
to get the output of the “sp_spaceused” SP into a SQL Server table.

create table #errorlog(line varchar(2000))

execute master.dbo.xp_cmdshell ‘osql -SYourSQLMachine

  -E -Q”execute sp_spaceused” -o”c:\temp\sp_out.txt” -s”” ‘

insert into #errorlog

  execute master.dbo.xp_cmdshell ‘type “c:\temp\sp_out.txt” ‘

select line from #errorlog

drop table #errorlog

Here you can see I used xp_cmdshell to execute the OSQL
utility. By using this utility I was able to direct the output of the “sp_spaceused”
SP directly into an OS file using the -o option. I then used the procedure
described in the preceding section to get the OS file created by the OSQL
utility into my temporary table named #errorlog. Once again, using this method
causes each line of output from the “sp_spaceused” SP to be stored in a single
column record in my temporary table.

Output From an Executable Into a Table

For my last example, I will show you how to get the output
of an “.exe” into a SQL Server table. Suppose I want to get my server’s IP
configuration information into a SQL Server table so I can manipulate it. To
display this information you would run the “ipconfig.exe” executable. So here
is the code I would use to get the IP configure information into a table:

create table #ipconfig(line varchar(2000))

insert into #ipconfig

  execute xp_cmdshell ‘ipconfig.exe’

select line from #ipconfig

drop table #ipconfig

Here the code is similar to the code I used to get the
contents of an OS file into a table. The only difference is instead of running
a DOS command with xp_cmdshell , I ran the “ipconfig.exe” executable.

Conclusion

With the use of an INSERT statement, a SQL Server table, and
the occasional execution of the xp_cmdshell extended stored procedure it is
possible to get almost anything into a SQL Server table. I hope that the next
time you need to get the output of a stored procedure, physical file, and/or
the output of an “.exe” into a SQL Server table you will consider using one of
the processes I described in this article.

»


See All Articles by Columnist Gregory A. Larsen