An INSERT statement cannot contain a SELECT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

An INSERT statement cannot contain a SELECT

Author

Topic

parameshjoy
Starting Member

12 Posts

Posted - 2012-03-27 : 12:27:13

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

52326 Posts

Posted - 2012-03-27 : 12:31:11

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/

parameshjoy
Starting Member

12 Posts

Posted - 2012-03-27 : 12:32:41

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 12:34:02

you current query doent have an update. it will keep inserting new rows as its written

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

parameshjoy
Starting Member

12 Posts

Posted - 2012-03-27 : 12:44:56

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

Paramesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 14:57:22

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/

parameshjoy
Starting Member

12 Posts

Posted - 2012-03-27 : 22:33:27

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-27 : 22:52:43

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
[spoiler]Time is always against us[/spoiler]

parameshjoy
Starting Member

12 Posts

Posted - 2012-03-27 : 23:36:05

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-27 : 23:54:42

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
[spoiler]Time is always against us[/spoiler]

parameshjoy
Starting Member

12 Posts

Posted - 2012-03-28 : 00:11:07

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-28 : 00:13:26

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
[spoiler]Time is always against us[/spoiler]

parameshjoy
Starting Member

12 Posts

Posted - 2012-03-28 : 00:16:52

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

Paramesh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-28 : 02:50:10

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
[spoiler]Time is always against us[/spoiler]

parameshjoy
Starting Member

12 Posts

Posted - 2012-03-28 : 03:17:36

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-28 : 03:20:49

that is your original query right ? Have you try Visakh's query Posted - 03/27/2012 : 12:31:11 ?

KH
[spoiler]Time is always against us[/spoiler]

parameshjoy
Starting Member

12 Posts

Posted - 2012-03-28 : 03:28:15

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

Paramesh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-28 : 03:33:34

Houston, we have a problem here. We've had a infinite loop.

Beam me up Scotty !

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-28 : 03:40:45

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
[spoiler]Time is always against us[/spoiler]

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-28 : 04:25:19

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

parameshjoy
Starting Member

12 Posts

Posted - 2012-03-28 : 04:55:45

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@total".

Getting this error for query provided

Paramesh

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources