Help on inserting results into Table

Question

Post reply

Help on inserting results into Table

MickyD

SSCarpal Tunnel

Points: 4601
More actions
August 15, 2013 at 9:02 am

#282091

Hi everyone.
Could someone show my how to insert this result into an SQL table.
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Total Pages';
;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;
I know the error is in relation to declaring the variable , but I am at a bit of loss of how to correct it ?
Any help appreciated.
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

;WITH src AS

(

SELECT

database_id, db_buffer_pages = COUNT_BIG(*)

FROM sys.dm_os_buffer_descriptors

--WHERE database_id BETWEEN 5 AND 32766

GROUP BY database_id

)

SELECT

[db_name] = CASE [database_id] WHEN 32767

THEN 'Resource DB'

ELSE DB_NAME([database_id]) END,

db_buffer_pages,

db_buffer_MB = db_buffer_pages / 128,

db_buffer_percent = CONVERT(DECIMAL(6,3),

db_buffer_pages * 100.0 / @total_buffer)

INTO MyNewTable

FROM src

ORDER BY db_buffer_MB DESC;

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MickyD SSCarpal Tunnel Points: 4601 More actions · Answer 2

Chris

Many thanks for your quick response appreciate it.Works great.

Final question I have is , how would you amend the script to write to the same table again. So it can contain many rows etc collected at different times is what I am after.

If I run the script again says object is already exists.

Once I have it working , will work out how it works.

Cheers

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

If the table already exists, you need a different approach.

;WITH src AS

(

SELECT

database_id, db_buffer_pages = COUNT_BIG(*)

FROM sys.dm_os_buffer_descriptors

--WHERE database_id BETWEEN 5 AND 32766

GROUP BY database_id

)

INSERT INTO MyExistingTable(

db_name,

db_buffer_pages,

db_buffer_MB,

db_buffer_percent)

SELECT

[db_name] = CASE [database_id] WHEN 32767

THEN 'Resource DB'

ELSE DB_NAME([database_id]) END,

db_buffer_pages,

db_buffer_MB = db_buffer_pages / 128,

db_buffer_percent = CONVERT(DECIMAL(6,3),

db_buffer_pages * 100.0 / @total_buffer)

FROM src

ORDER BY db_buffer_MB DESC;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

;WITH src AS

(

SELECT

database_id, db_buffer_pages = COUNT_BIG(*)

FROM sys.dm_os_buffer_descriptors

--WHERE database_id BETWEEN 5 AND 32766

GROUP BY database_id

)

INSERT INTO MyNewTable ([db_name], db_buffer_pages, db_buffer_MB, db_buffer_percent)

SELECT

[db_name] = CASE [database_id] WHEN 32767

THEN 'Resource DB'

ELSE DB_NAME([database_id]) END,

db_buffer_pages,

db_buffer_MB = db_buffer_pages / 128,

db_buffer_percent = CONVERT(DECIMAL(6,3),

db_buffer_pages * 100.0 / @total_buffer)

FROM src

-- ORDER BY db_buffer_MB DESC; -- order of rows is not guaranteed on INSERT

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MickyD SSCarpal Tunnel Points: 4601 More actions · Answer 5

MickyD

SSCarpal Tunnel

Points: 4601

August 15, 2013 at 9:20 am

#1641843

Perfect thanks Luis.