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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 I want to insert the resultset into new table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kshahzad
Starting Member

45 Posts

Posted - 07/11/2013 :  14:19:04  Show Profile  Reply with Quote
I have this query running perfectly fine and the result set looks like this

NumberRows Time_stamp
----------- -----------------------------
940 2013-07-11 18:00:00.357


Now i want to insert these two columns values in a new table.

i cant figure out how can i do this.

DECLARE @dCurrentTime DATETIME



DECLARE @dCurrentTimeMinus5 DATETIME

--Declare @counttotal int

SET @dCurrentTime = GETDATE()
SET @dCurrentTimeMinus5 = DATEADD(minute, -5, @dCurrentTime)


--insert into UsersLoggedIn (NumberRows,@dCurrentTime);
SELECT Count(*) As NumberRows, @dCurrentTime as Time_stamp

FROM (

SELECT lID

FROM SessionState_Variables

WHERE dLastAccessed BETWEEN @dCurrentTimeMinus5 AND @dCurrentTime

GROUP BY lID

) As SessionsTable

Regards

Edited by - kshahzad on 07/11/2013 14:22:40

MuMu88
Aged Yak Warrior

549 Posts

Posted - 07/11/2013 :  14:39:28  Show Profile  Reply with Quote
quote:
Originally posted by kshahzad

I have this query running perfectly fine and the result set looks like this

NumberRows Time_stamp
----------- -----------------------------
940 2013-07-11 18:00:00.357


Now i want to insert these two columns values in a new table.

i cant figure out how can i do this.

DECLARE @dCurrentTime DATETIME



DECLARE @dCurrentTimeMinus5 DATETIME

--Declare @counttotal int

SET @dCurrentTime = GETDATE()
SET @dCurrentTimeMinus5 = DATEADD(minute, -5, @dCurrentTime)


insert into UsersLoggedIn (NumberRows, Time_stamp) -- insert the exact column names from the new table here
SELECT Count(*) As NumberRows, @dCurrentTime as Time_stamp

FROM (

SELECT lID

FROM SessionState_Variables

WHERE dLastAccessed BETWEEN @dCurrentTimeMinus5 AND @dCurrentTime

GROUP BY lID

) As SessionsTable

Regards

Go to Top of Page

kshahzad
Starting Member

45 Posts

Posted - 07/11/2013 :  14:47:08  Show Profile  Reply with Quote
No this is a syntax error
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 07/11/2013 :  14:55:28  Show Profile  Reply with Quote
what is the error?
Go to Top of Page

kshahzad
Starting Member

45 Posts

Posted - 07/11/2013 :  15:02:30  Show Profile  Reply with Quote
Msg 207, Level 16, State 1, Line 13
Invalid column name 'NumberRows'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'Time_stamp'.
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 07/11/2013 :  15:21:24  Show Profile  Reply with Quote
Can you show the definition of the table named "UsersLoggedIn"

quote:
Originally posted by kshahzad

Msg 207, Level 16, State 1, Line 13
Invalid column name 'NumberRows'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'Time_stamp'.


Go to Top of Page

kshahzad
Starting Member

45 Posts

Posted - 07/11/2013 :  15:23:03  Show Profile  Reply with Quote
Create TABLE UsersLoggedIn
(
TotalUsers int,
TimeRecorded datetime
);
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 07/11/2013 :  15:25:37  Show Profile  Reply with Quote
Try this:
quote:
Originally posted by MuMu88

quote:
Originally posted by kshahzad

I have this query running perfectly fine and the result set looks like this

NumberRows Time_stamp
----------- -----------------------------
940 2013-07-11 18:00:00.357


Now i want to insert these two columns values in a new table.

i cant figure out how can i do this.

DECLARE @dCurrentTime DATETIME



DECLARE @dCurrentTimeMinus5 DATETIME

--Declare @counttotal int

SET @dCurrentTime = GETDATE()
SET @dCurrentTimeMinus5 = DATEADD(minute, -5, @dCurrentTime)


insert into UsersLoggedIn (TotalUsers, TimeRecorded ) -- insert the exact column names from the new table here
SELECT Count(*) As TotalUsers, @dCurrentTime as TimeRecorded

FROM (

SELECT lID

FROM SessionState_Variables

WHERE dLastAccessed BETWEEN @dCurrentTimeMinus5 AND @dCurrentTime

GROUP BY lID

) As SessionsTable

Regards




Edited by - MuMu88 on 07/11/2013 15:26:36
Go to Top of Page

kshahzad
Starting Member

45 Posts

Posted - 07/11/2013 :  15:53:23  Show Profile  Reply with Quote
Awesome..Thanks
Go to Top of Page
  Previous Topic Topic Next 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.07 seconds. Powered By: Snitz Forums 2000