SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 An INSERT statement cannot contain a SELECT
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

parameshjoy
Starting Member

India
12 Posts

Posted - 03/27/2012 :  12:27:13  Show Profile  Reply with Quote
DECLARE @total INT

Insert into dbo.CPUdata ([database], [system cpu], [database cpu], [%])



SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)

join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid

SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'

FROM sys.sysprocesses sp (NOLOCK)

JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid

--WHERE sp.status = 'runnable'

GROUP BY sb.name

ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc



Getting error

Msg 199, Level 15, State 1, Line 0

An INSERT statement cannot contain a SELECT statement that assigns values to a variable



Paramesh

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/27/2012 :  12:31:11  Show Profile  Reply with Quote
should be this i guess

DECLARE @total INT


SELECT @total=sum(cpu) FROM sys.sysprocesses sp (NOLOCK)

join sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid


Insert into dbo.CPUdata ([database], [system cpu], [database cpu], [%])
SELECT sb.name 'database', @total 'system cpu', SUM(cpu) 'database cpu', CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) '%'

FROM sys.sysprocesses sp (NOLOCK)

JOIN sys.sysdatabases sb (NOLOCK) ON sp.dbid = sb.dbid

--WHERE sp.status = 'runnable'

GROUP BY sb.name

ORDER BY CONVERT(DECIMAL(4,1), CONVERT(DECIMAL(17,2),SUM(cpu)) / CONVERT(DECIMAL(17,2),@total)*100) desc


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

parameshjoy
Starting Member

India
12 Posts

Posted - 03/27/2012 :  12:32:41  Show Profile  Reply with Quote
But i will be scheduling this query for every one hour so the its updates table

The above query you provide will create a new table but i cant update the same table for every one hour

Paramesh

Edited by - parameshjoy on 03/27/2012 12:33:35
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/27/2012 :  12:34:02  Show Profile  Reply with Quote
you current query doent have an update. it will keep inserting new rows as its written

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

parameshjoy
Starting Member

India
12 Posts

Posted - 03/27/2012 :  12:44:56  Show Profile  Reply with Quote
I need the same just inserting new rows into table CPU data

Paramesh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/27/2012 :  14:57:22  Show Profile  Reply with Quote
quote:
Originally posted by parameshjoy

I need the same just inserting new rows into table CPU data

Paramesh


then what was the purpose of below statement??

The above query you provide will create a new table but i cant update the same table for every one hour



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

parameshjoy
Starting Member

India
12 Posts

Posted - 03/27/2012 :  22:33:27  Show Profile  Reply with Quote
Sorry instead of inserting rows i mentioned as update....

My intention is to create a cpu process trend report by caputring the out of this query to a table and at the end of the month create a report on this and show to management.

Hence the query will give the output and i need to capture in one table till month end.

Kindly help me.

Paramesh
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17439 Posts

Posted - 03/27/2012 :  22:52:43  Show Profile  Reply with Quote
quote:
Originally posted by parameshjoy

Sorry instead of inserting rows i mentioned as update....

My intention is to create a cpu process trend report by caputring the out of this query to a table and at the end of the month create a report on this and show to management.

Hence the query will give the output and i need to capture in one table till month end.

Kindly help me.

Paramesh


?
then the query Visakh posted should do the work. Isn't it ?


KH
Time is always against us

Go to Top of Page

parameshjoy
Starting Member

India
12 Posts

Posted - 03/27/2012 :  23:36:05  Show Profile  Reply with Quote
Hi Khtan,

The query provided by Visakh will create a new table and then insert, i will be running this query every one hour so that the table gets insert with output of every one hours...

I need to run the query every oen hour...

Paramesh
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17439 Posts

Posted - 03/27/2012 :  23:54:42  Show Profile  Reply with Quote
quote:
Originally posted by parameshjoy

Hi Khtan,

The query provided by Visakh will create a new table and then insert, i will be running this query every one hour so that the table gets insert with output of every one hours...

I need to run the query every oen hour...

Paramesh



the query did not create a new table. It only insert records to it


KH
Time is always against us

Go to Top of Page

parameshjoy
Starting Member

India
12 Posts

Posted - 03/28/2012 :  00:11:07  Show Profile  Reply with Quote
It creates a new table and even output got inserted to that table.

Now i need to schedule this query for every one hour and the output will append in the same table every one hour for this i need to alter this query.. Need help on this.

Paramesh
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17439 Posts

Posted - 03/28/2012 :  00:13:26  Show Profile  Reply with Quote
quote:
Originally posted by parameshjoy

It creates a new table and even output got inserted to that table.

Now i need to schedule this query for every one hour and the output will append in the same table every one hour for this i need to alter this query.. Need help on this.

Paramesh



are you referring to Visakh's query ? His query does not create a new table.


KH
Time is always against us

Go to Top of Page

parameshjoy
Starting Member

India
12 Posts

Posted - 03/28/2012 :  00:16:52  Show Profile  Reply with Quote
Am referring to the Visakh's query it creates new a table....

Paramesh
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17439 Posts

Posted - 03/28/2012 :  02:50:10  Show Profile  Reply with Quote
quote:
Originally posted by parameshjoy

Am referring to the Visakh's query it creates new a table....

Paramesh



Can you highlight which part of his query that creates new a table ?

Are you referring to his post on 03/27/2012 : 12:31:11 ?


KH
Time is always against us


Edited by - khtan on 03/28/2012 02:51:38
Go to Top of Page

parameshjoy
Starting Member

India
12 Posts

Posted - 03/28/2012 :  03:17:36  Show Profile  Reply with Quote
i have cpudata table already exists in my database, now from the query i posted 03/27/2012 : 12:27:13 need to update the table cpudata with the output of the query, and this query will run every one hour, now am getting the error

Getting error

Msg 199, Level 15, State 1, Line 0

An INSERT statement cannot contain a SELECT statement that assigns values to a variable





Paramesh
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17439 Posts

Posted - 03/28/2012 :  03:20:49  Show Profile  Reply with Quote
that is your original query right ? Have you try Visakh's query Posted - 03/27/2012 : 12:31:11 ?


KH
Time is always against us

Go to Top of Page

parameshjoy
Starting Member

India
12 Posts

Posted - 03/28/2012 :  03:28:15  Show Profile  Reply with Quote
Visakh's query will create new table, i dont want to create another table.

Paramesh
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17439 Posts

Posted - 03/28/2012 :  03:33:34  Show Profile  Reply with Quote
Houston, we have a problem here. We've had a infinite loop.

Beam me up Scotty !


KH
Time is always against us


Edited by - khtan on 03/28/2012 03:33:57
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17439 Posts

Posted - 03/28/2012 :  03:40:45  Show Profile  Reply with Quote
quote:
Originally posted by parameshjoy

Visakh's query will create new table, i dont want to create another table.

Paramesh



OK. Let's start all over again.

Obviuosly, you didn't try Visakh's query at all. How do you know it will create a new table if you haven't try it ?
Which statement in his query will create a new table ?


KH
Time is always against us

Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 03/28/2012 :  04:25:19  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
quote:
Originally posted by parameshjoy

Visakh's query will create new table, i dont want to create another table.

Paramesh



It does NOT. There is no CREATE TABLE in his script, there is no INTO clause, the query he wrote for you will insert rows into an existing table, nothing more.

If you'd tested it, you'd see that.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

parameshjoy
Starting Member

India
12 Posts

Posted - 03/28/2012 :  04:55:45  Show Profile  Reply with Quote
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@total".

Getting this error for query provided

Paramesh
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000