CODEWITHSUNDEEP
sql
Bill Singh
Bill Singh

Reputation: 21

How do I save the results of a query to a table?

I need to save the results of this code which is just one table with two columns and twelve rows, into a separate table. If I use the INTO clause at the end, i get an error message "Incorrect Syntax near INTO". I am not sure what this means as i am using the same command in the middle of the code without any issues. The INTO clause (which does not work) is part of the code below. 

-- The code below generates a table that marks the last 12 months based on the value for @querydate1
        DECLARE @queryDate1 datetime SET @queryDate1 = '03/30/2018';
        DECLARE @queryDate2 datetime set @queryDate2 = EOMONTH(@queryDate1,-1);
        DECLARE @queryDate3 datetime SET @queryDate3 = EOMONTH(@queryDate1,-2);
        DECLARE @queryDate4 datetime set @queryDate4 = EOMONTH(@queryDate1,-3);
        DECLARE @queryDate5 datetime SET @queryDate5 = EOMONTH(@queryDate1,-4);
        DECLARE @queryDate6 datetime set @queryDate6 = EOMONTH(@queryDate1,-5);
        DECLARE @queryDate7 datetime SET @queryDate7 = EOMONTH(@queryDate1,-6);
        DECLARE @queryDate8 datetime set @queryDate8 = EOMONTH(@queryDate1,-7);
        DECLARE @queryDate9 datetime SET @queryDate9 = EOMONTH(@queryDate1,-8);
        DECLARE @queryDate10 datetime set @queryDate10 = EOMONTH(@queryDate1,-9);
        DECLARE @queryDate11 datetime SET @queryDate11 = EOMONTH(@queryDate1,-10);
        DECLARE @queryDate12 datetime set @queryDate12 = EOMONTH(@queryDate1,-11);

        DROP TABLE GA_Financial.dbo.tmpTblTTM
        SELECT
             convert(varchar(10),@queryDate1,101) as [M1]
            ,convert(varchar(10),@queryDate2,101) as [M2]
            ,convert(varchar(10),@queryDate3,101) as [M3]
            ,convert(varchar(10),@queryDate4,101) as [M4]
            ,convert(varchar(10),@queryDate5,101) as [M5]
            ,convert(varchar(10),@queryDate6,101) as [M6]
            ,convert(varchar(10),@queryDate7,101) as [M7]
            ,convert(varchar(10),@queryDate8,101) as [M8]
            ,convert(varchar(10),@queryDate9,101) as [M9]
            ,convert(varchar(10),@queryDate10,101) as [M10]
            ,convert(varchar(10),@queryDate11,101) as [M11]
            ,convert(varchar(10),@queryDate12,101) as [M12]
        INTO ga_financial.dbo.tmpTblTTM

SELECT
            'M1' AS [MX], M1 AS [Date] FROM ga_financial.dbo.tmpTblTTM
            UNION ALL SELECT 'M2', M2 AS [Date] FROM ga_financial.dbo.tmpTblTTM
            UNION ALL SELECT 'M3', M3 AS [Date] FROM ga_financial.dbo.tmpTblTTM
            UNION ALL SELECT 'M4', M4 AS [Date] FROM ga_financial.dbo.tmpTblTTM
            UNION ALL SELECT 'M5', M5 AS [Date] FROM ga_financial.dbo.tmpTblTTM
            UNION ALL SELECT 'M6', M6 AS [Date] FROM ga_financial.dbo.tmpTblTTM
            UNION ALL SELECT 'M7', M7 AS [Date] FROM ga_financial.dbo.tmpTblTTM
            UNION ALL SELECT 'M8', M8 AS [Date] FROM ga_financial.dbo.tmpTblTTM
            UNION ALL SELECT 'M9', M9 AS [Date] FROM ga_financial.dbo.tmpTblTTM
            UNION ALL SELECT 'M10', M10 AS [Date] FROM ga_financial.dbo.tmpTblTTM
            UNION ALL SELECT 'M11', M11 AS [Date] FROM ga_financial.dbo.tmpTblTTM
            UNION ALL SELECT 'M12', M12 AS [Date] FROM ga_financial.dbo.tmpTblTTM
        INTO ga_financial.dbo.tmpTblTTM2

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

In a union all/union query the into goes after the first select:

SELECT 'M1' AS [MX], M1 AS [Date]
INTO ga_financial.dbo.tmpTblTTM2
FROM ga_financial.dbo.tmpTblTTM
        UNION ALL SELECT 'M2', M2 AS [Date] FROM ga_financial.dbo.tmpTblTTM
        UNION ALL SELECT 'M3', M3 AS [Date] FROM ga_financial.dbo.tmpTblTTM
        UNION ALL SELECT 'M4', M4 AS [Date] FROM ga_financial.dbo.tmpTblTTM
        UNION ALL SELECT 'M5', M5 AS [Date] FROM ga_financial.dbo.tmpTblTTM
        UNION ALL SELECT 'M6', M6 AS [Date] FROM ga_financial.dbo.tmpTblTTM
        UNION ALL SELECT 'M7', M7 AS [Date] FROM ga_financial.dbo.tmpTblTTM
        UNION ALL SELECT 'M8', M8 AS [Date] FROM ga_financial.dbo.tmpTblTTM
        UNION ALL SELECT 'M9', M9 AS [Date] FROM ga_financial.dbo.tmpTblTTM
        UNION ALL SELECT 'M10', M10 AS [Date] FROM ga_financial.dbo.tmpTblTTM
        UNION ALL SELECT 'M11', M11 AS [Date] FROM ga_financial.dbo.tmpTblTTM
        UNION ALL SELECT 'M12', M12 AS [Date] FROM ga_financial.dbo.tmpTblTTM

Upvotes: 2

Related Questions