CODEWITHSUNDEEP
sql
user2547340
user2547340

Reputation: 325

Create Table based on SELECT Statement Results

I'm new to this site and to SQL in general. I'm hoping you might be able to help. I've created the following SQL query. I'd like to now create a new table and transfer the query results into this new table.

Is it possible to add syntax to the following query to create a table automatically?

SELECT
    Value [Value],
        DateTime [DateTime], 
            DatePart (Week, DateTime) [WeekNumber],
                hs.StorageItemID [StorageItemId]

FROM History_StorageItem hs

    JOIN History_Float hf ON hs.StorageItemId = hf.StorageItemId

GROUP BY DatePart (Week,DateTime), hs.StorageItemId, Value
ORDER BY hs.StorageItemId DESC

Also, the results of this query relating to DateTime is returning yyyy-mm-dd hh:mm:ss...... Is it possible to drop the time from the resulting values that my query and data being added to my table is just "Date".

I really appreciate your support as my limited knowledge of SQL has only managed to take me so far.

Thanks.

Upvotes: 0

Views: 3145

Answers (5)

Brinnis
Brinnis

Reputation: 906

You should use the date formatting methods, these change depending on your sql server type

Postgres

MySQL

Oracle

Upvotes: 0

Scott
Scott

Reputation: 298

On the other hand, if you wanted to create a view instead of a table, you could do something like this:

CREATE VIEW [newView] AS
SELECT
Value [Value],
    DateTime [DateTime], 
        DatePart (Week, DateTime) [WeekNumber],
            hs.StorageItemID [StorageItemId]
FROM History_StorageItem hs
JOIN History_Float hf ON hs.StorageItemId = hf.StorageItemId
GROUP BY DatePart (Week,DateTime), hs.StorageItemId, Value

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

Answers may vary by RDBMS. You can use INTO to create a table from SELECT results, and you can cast the DATETIME as DATE:

SELECT
    Value [Value],
        CAST (DateTime AS DATE) [DateTime], 
            DatePart (Week, DateTime) [WeekNumber],
                hs.StorageItemID [StorageItemId]
INTO newTable
FROM History_StorageItem hs

    JOIN History_Float hf ON hs.StorageItemId = hf.StorageItemId

GROUP BY DatePart (Week,DateTime), hs.StorageItemId, Value
ORDER BY hs.StorageItemId DESC

Upvotes: 1

chetan
chetan

Reputation: 2886

create table YOURTABLENAME AS
SELECT
    Value [Value],
        DateTime [DateTime], 
            DatePart (Week, DateTime) [WeekNumber],
                hs.StorageItemID [StorageItemId]

FROM History_StorageItem hs

    JOIN History_Float hf ON hs.StorageItemId = hf.StorageItemId

GROUP BY DatePart (Week,DateTime), hs.StorageItemId, Value
ORDER BY hs.StorageItemId DESC

Upvotes: 0

Luis LL
Luis LL

Reputation: 2993

sure you can just INTO clause to the select right before the FROM

SELECT Value [Value], DateTime [DateTime], DatePart (Week, DateTime) [WeekNumber], hs.StorageItemID [StorageItemId]
INTO [TABLENAME]
FROM History_StorageItem hs

JOIN History_Float hf ON hs.StorageItemId = hf.StorageItemId
GROUP BY DatePart (Week,DateTime), hs.StorageItemId, Value ORDER BY hs.StorageItemId DESC

I recommend you these tutorials

http://www.w3schools.com/sql/sql_select_into.asp

Upvotes: 0

Related Questions