Insert Stored Procedure Results Into Table

Since stored procedures are generally the most efficient way to call and retrieve data, it’s natural to want to encapsulate logic in them and call them when needed. With this use also comes the need to retrieve the data they return. When you already know the datatypes and columns being returned from the procedure, this is a relatively easy task. Let’s start by looking at an example call. [cc lang=”sql”] — Create a sample stored procedure CREATE PROCEDURE GetList AS BEGIN SELECT ListName = ‘MyList’ ,ListNumber = 1 END GO — this table will house our results CREATE TABLE #List ( ListName varchar(25), ListNumber int ) — finally, execute and insert into our table INSERT INTO #List ( ListName, ListNumber ) EXEC dbo.GetList [/cc] That was relatively easy, however dynamically retrieving the result set from a stored procedure without knowing the columns and datatypes returned in advance has historically proven to be a difficult task. Fortunately with the advent of OPENROWSET, our life as developers has become much simpler. Using openrowset is very simple when we do not have procedures that take parameters, However when parameters become involved the game changes, however there is a workaround. In using OPENROWSET we need to turn on the ability to run distributed queries on your server. To enable this is simple, given you have the appropriate permissions. It’s a simple process when we do not have to pass in parameters. [cc lang=”sql”] sp_configure ‘Ad Hoc Distributed Queries’,1 RECONFIGURE WITH OVERRIDE [/cc] Using OPENROWSET […] Continue reading ...

Insert Results of Query Into Table

There are a few ways to insert query results into another table. The most common way is to use the standard insert statement. This would insert query results into a table that already contains data. [cc lang=”sql”] INSERT INTO dbo.Users ( Username ,FirstName ,LastName ,IsSuperUser ,AffiliateId ,Email ,DisplayName ,UpdatePassword ) SELECT Username ,FirstName ,LastName ,IsSuperUser ,AffiliateId ,Email ,DisplayName = DisplayName + ‘ SuperUser’ ,UpdatePassword FROM dbo.NewUsers nu WHERE nu.IsSuperUser = 1 [/cc] This way is most efficient if you are inserting records into a new table. It will automatically create the table for you and copy the datatypes from the existing table and all the rows: [cc lang=”sql”] SELECT * INTO dbo.EventLogCopy FROM dbo.EventLog [/cc]

Performance Counters

The following list of performance counters have been modified to include the most pertinent in identifying the bottleneck of your system. This list is for reference purposes. If needing to troubleshoot slowness read troubleshooting sql server slowness. Memory Object: – Memory Counter: – Available Mbytes Preferred Value: – > 2000MB Description: With 64 bit OS’s the available bytes recommendation has increased. Object: – Memory Counter: – Pages/Sec Preferred Value: – < 50 Description: - Pages/sec is the rate at which pages are read from or written to disk to resolve hard page faults. Indicative of possible page file usage, and definitely a memory bottleneck somewhere on the system. Object: - Paging File Counter: - %Usage Preferred Value: - < 5% Description: - Current amount of Page file being used. This is a key number when weighing the amount of memory allocated to the OS. If this number is high, then the OS is choked for RAM. Either increase the RAM on the box or deallocate from SQL. If this is between 10% and 25% you should reboot. Object: - Paging File Counter: - %Usage Peak Preferred Value: - < 15% Description: - Show the peak the page file got to since the last reboot. Usually this is not too far off from the page file usage %. Unfortunately when page file issues happen, they do not correct themselves without a reboot. Object: - SQL Server:Buffer Manager Counter: - Page Life Expectancy Preferred Value: - > 300 Description: – The single […] Continue reading ...

Rowcount for All Tables

SELECT SUM(rowcnt) FROM sysindexes WHERE indid < 2