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
 General SQL Server Forums
 New to SQL Server Programming
 I want to insert the resultset into new table
 New Topic  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

547 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

547 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

547 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

547 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  
 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.12 seconds. Powered By: Snitz Forums 2000