Select query output to a table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Select query output to a table

Select query output to a table

Rate Topic

Display Mode

Topic Options

Author

Message

Warren Peace

Posted Tuesday, October 30, 2007 12:51 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 06, 2012 9:48 AM
Points: 207, Visits: 450

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

Tommy Bollhofer

Posted Tuesday, October 30, 2007 1:28 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:25 PM
Points: 1,148, Visits: 3,149

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

bitbucket-25253

Posted Tuesday, October 30, 2007 1:29 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 5,103, Visits: 20,215

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

Warren Peace

Posted Tuesday, October 30, 2007 2:57 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 06, 2012 9:48 AM
Points: 207, Visits: 450

Perfect!

Thanks everyone.

Post #416810

« Prev Topic | Next Topic »

Permissions