Inserting query result into table

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

General SQL Server Forums

New to SQL Server Programming

Inserting query result into table

Author

Topic

petroyd
Starting Member

5 Posts

Posted - 2011-02-08 : 13:06:49

Hello.. I have this simple query looking like this:

SELECT GETDATE() AS time, COUNT(KOR_ordernr) AS rowcount
FROM Kundorderrad
WHERE (KOR_Radklar <> 1)

This gives me exactly the result im looking for.
But how do I insert it to a table instead of just presenting the result on screen?, I've managed to insert the "GETDATE()" result into a table but trying to insert the "COUNT" result only gives me errors.

Question no.2

Is there any easy way to schedule this query to be done in like 15 minutes intervals?

Thanks in advance /Kim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-08 : 13:24:49

Both columns have to exist in the table you are trying to insert into. So create the table first and then insert into it. Also, your column names are reserved words. Could you change them to something like OrderTime and OrderCount?

Jim

Everyday I learn something that somebody else already knew

petroyd
Starting Member

5 Posts

Posted - 2011-02-08 : 14:21:43

Thanks Jimf. I will try this out first thing tomorow at work,
Yes, the column names does not make me any difference.

Im not sure how to build up the code. (Im a total noob at this) =)
lets say i create a new table named "mytable" and builds up the query as folows...

INSERT INTO mytable (OrderTime, OrderCount)
VALUES (getDate())
SELECT COUNT(KOR_ordernr) FROM Kundorderrad
WHERE (KOR_Radklar <> 1)

Im pretty sure i got this all messed up ;-p
But it would really help if someone could take a look on how the code should be built up.

Thanx

petroyd
Starting Member

5 Posts

Posted - 2011-02-08 : 14:24:50

Im feeling like a 3-year old just learing to spell my own name.. lol

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-08 : 14:45:54

Just a tweak

INSERT INTO mytable (OrderTime, OrderCount)
SELECT getdate(),COUNT(KOR_ordernr) FROM Kundorderrad
WHERE (KOR_Radklar <> 1)

Jim

Everyday I learn something that somebody else already knew

petroyd
Starting Member

5 Posts

Posted - 2011-02-08 : 15:04:15

thanx alot Jim!

Any ideas on how to get this data into table "mytable" with
a preset time interval?

I only have access to "MS SQL Server Management Studio"
I know -nothing- about PHP-scripting

Thanx

Kim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-08 : 15:33:41

You can get the dba to create a job (or perhaps you can do it as well) that will execute your script at whatever interval you need.

Jim

Everyday I learn something that somebody else already knew

petroyd
Starting Member

5 Posts

Posted - 2011-02-09 : 13:11:10

I got this all working fine today, even managed to set up the 15min interval with "SQL Server Agent" but there is one thing i found a bit strange.. Why dont the last Insertment always end up in the bottom of the table.. my result ended up something like this:

2011-02-09 16:15:00 233
2011-02-09 16:45:00 255
2011-02-09 17:00:00 247
2011-02-09 14:45:00 317
2011-02-09 15:00:00 320

I really can't see the logic.. I guess i can "SORT" my query result, but it would be nice if it just could end up sorted from the beginning..

Is there any way to do that or is the "random" just the nature of databases?

Thanx again jimf for helping me out with the code-part

/Kim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-09 : 13:28:37

The only way to guarantee order in the table is to put a clustered index on the column you want to order by.
CREATE CLUSTERED INDEX <index name> on myTable(OrderTime)

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources