SQL Server Creating a temp table for this query

Question

I have this query   DECLARE   ProjectID int   3   Year int   2010   MeterTypeID int   1   StartDate datetime   EndDate datetime  SET  StartDate    07 01     CAST  Year as VARCHAR  SET  EndDate    06 30     CAST  Year 1 as VARCHAR   SELECT  tblMEP Sites Name AS SiteName  convert varchar 10  BillingMonth  101  AS BillingMonth  SUM Consumption  AS Consumption FROM tblMEP Projects  JOIN tblMEP Sites ON tblMEP Projects ID   tblMEP Sites ProjectID  JOIN tblMEP Meters ON tblMEP Meters SiteID   tblMEP Sites ID  JOIN tblMEP MonthlyData ON tblMEP MonthlyData MeterID   tblMEP Meters ID  JOIN tblMEP CustomerAccounts ON tblMEP CustomerAccounts ID   tblMEP Meters CustomerAccountID  JOIN tblMEP UtilityCompanies ON tblMEP UtilityCompanies ID   tblMEP CustomerAccounts UtilityCompanyID  JOIN tblMEP MeterTypes ON tblMEP UtilityCompanies UtilityTypeID   tblMEP MeterTypes ID  WHERE tblMEP Projects ID    ProjectID AND tblMEP MonthlyData BillingMonth Between  StartDate AND  EndDate AND tbLMEP MeterTypes ID    MeterTypeID GROUP BY BillingMonth  tblMEP Sites Name ORDER BY month BillingMonth    I just want store it in a temp table so that I can do something with it  It would be great if anybody can just include the syntax for creating a temp table in SQL Server    I tried different ways but I was lost and did not get the result I want

User · Accepted Answer

If you want to just create a temp table inside the query that will allow you to do something with the results that you deposit into it you can do something like the following:

DECLARE @T1 TABLE (
Item 1 VARCHAR(200)
, Item 2 VARCHAR(200)
, ...
, Item n VARCHAR(500)
)

On the top of your query and then do an

INSERT INTO @T1
SELECT
FROM
(...)

User · Answer

DECLARE  MyTempTable TABLE  SiteName varchar 50   BillingMonth varchar 10   Consumption float   INSERT INTO  MyTempTable  SiteName  BillingMonth  Consumption  SELECT  tblMEP Sites Name AS SiteName  convert varchar 10  BillingMonth  101  AS BillingMonth  SUM Consumption  AS Consumption FROM tblMEP Projects         --your joining statements   Here    - use this to create table inside tempdb   - use this to create table as variable

User · Answer

Like this   Make sure you drop the temp table  at the end of the code block  after you re done with it  or it will error on subsequent runs   SELECT       tblMEP Sites Name AS SiteName       convert varchar 10  BillingMonth  101  AS BillingMonth       SUM Consumption  AS Consumption INTO       MyTempTable FROM      tblMEP Projects     JOIN tblMEP Sites         ON tblMEP Projects ID   tblMEP Sites ProjectID     JOIN tblMEP Meters         ON tblMEP Meters SiteID   tblMEP Sites ID     JOIN tblMEP MonthlyData         ON tblMEP MonthlyData MeterID   tblMEP Meters ID     JOIN tblMEP CustomerAccounts         ON tblMEP CustomerAccounts ID   tblMEP Meters CustomerAccountID     JOIN tblMEP UtilityCompanies         ON tblMEP UtilityCompanies ID   tblMEP CustomerAccounts UtilityCompanyID     JOIN tblMEP MeterTypes         ON tblMEP UtilityCompanies UtilityTypeID   tblMEP MeterTypes ID WHERE      tblMEP Projects ID    ProjectID     AND tblMEP MonthlyData BillingMonth Between  StartDate AND  EndDate     AND tbLMEP MeterTypes ID    MeterTypeID GROUP BY      BillingMonth  tblMEP Sites Name  DROP TABLE  MyTempTable

User · Answer

If you want to query the results from a temporary table inside the same query  you can use   temp tables  or   table variables  I personally prefer     for querying outside of the scope you would either want to use    global temp tables or create a new table with the results   DECLARE   ProjectID int   3   Year int   2010   MeterTypeID int   1   StartDate datetime   EndDate datetime  SET  StartDate    07 01     CAST  Year as VARCHAR  SET  EndDate    06 30     CAST  Year 1 as VARCHAR   DECLARE  MyTempTable TABLE  SiteName varchar 50   BillingMonth varchar 10   Consumption float   INSERT INTO  MyTempTable  SiteName  BillingMonth  Consumption  SELECT  tblMEP Sites Name AS SiteName  convert varchar 10  BillingMonth  101  AS BillingMonth  SUM Consumption  AS Consumption FROM tblMEP Projects

User · Answer

IF OBJECT ID  tempdb   MyTempTable   IS NOT NULL DROP TABLE  MyTempTable  CREATE TABLE  MyTempTable  SiteName varchar 50   BillingMonth varchar 10   Consumption float   INSERT INTO  MyTempTable  SiteName  BillingMonth  Consumption  SELECT  tblMEP Sites Name AS SiteName  convert varchar 10  BillingMonth  101  AS BillingMonth  SUM Consumption  AS Consumption FROM tblMEP Projects

User · Answer

If you want to create a temp table after check exist table You can use the following code  DROP TABLE IF EXISTS tempdb dbo  temptable CREATE TABLE  temptable        SiteName             NVARCHAR 50      BillingMonth         varchar 10      Consumption          INT        After creating the temporary table  you can insert data into this table as a regular table   INSERT INTO  temptable SELECT COLUMN1     FROM         or  INSERT INTO  temptable VALUES  value1  value2  value3          The SELECT statement is used to select data from a temp table   SELECT   FROM  temptable   you can manually remove the temporary table by using the DROP TABLE statement   DROP TABLE  temptable

[sql] SQL Server Creating a temp table for this query

Examples related to sql

Examples related to sql-server

Examples related to temp-tables