Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select query output to a table Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2007 12:51 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
I have a query similar to this...

Select field1, MAX(SomeDateField) AS XDATE
From MyStuff SomeJoins...
Where SomeDateField IS NOT NULL
Group By field1

It returns some stuff like this...
field1 XDATE
ABCD 1900-12-31 00:00:00:000
DEFG 1900-12-30 00:00:00:000
HIJK 1900-12-29 00:00:00:000
and so on....

I want the output to go into a table with the following columns...where VIOLATOR is varchar(25) and INCIDENTDATE is DateTime.

VIOLATOR INCIDENTDATE
ABCD 1900-12-31 00:00:00:000
DEFG 1900-12-30 00:00:00:000
HIJK 1900-12-29 00:00:00:000

Thanks

Post #416738
Posted Tuesday, October 30, 2007 1:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:16 AM
Points: 1,157, Visits: 3,263
I'm not sure I understand your question - are you just looking to create a table for your select statement? i.e.

create table #BLAH
(VIOLATOR varchar(25),INCIDENTDATE datetime)


insert into #BLAH
select VIOLATOR, max(INCIDENTDATE) AS INCIDENTDATE
from SOMETABLE
group by VIOLATOR

select *
from #BLAH

DROP TABLE #BLAH


Tommy

Post #416758
Posted Tuesday, October 30, 2007 1:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:50 PM
Points: 5,322, Visits: 25,235
From BOL
The SELECT subquery in the INSERT statement can be used to add values into a table from one or more other tables or views. Using a SELECT subquery also lets more than one row be inserted at one time.

This INSERT statement inserts into a separate table some of the data from all the rows in titles whose type is modern cooking:

USE pubs
INSERT INTO MyBooks
SELECT title_id, title, type
FROM titles
WHERE type = 'mod_cook'

The select list of the subquery must match the column list of the INSERT statement. If no column list is specified, the select list must match the columns in the table or view being inserted into


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #416760
Posted Tuesday, October 30, 2007 2:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
Perfect!

Thanks everyone.
Post #416810
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse