How do I insert these fields into a temp table using SELECT into

Question

1.00/5 (1 vote)

See more:

I need to select these fields into a temp table using SELECT INTO instead of creating the temp table and all of its columns first. The District DBA wants to just add or remove fields without having to update the table every time:

SQL

BEGIN TRAN

  SELECT DISTINCT 
                    @C1 AS C1 ,
                    @C2 AS C2 ,
                    @C3 AS C3 ,
                    @C4 AS C4 ,
                    @C5 as C5,
                    @C6 as C6,
                    @C7 as C7,
                    @C8 as C8,
                    @C9 as C9,
                    @C10 as C10,
                    @C11,
					@C12,
					@C13,
					@C14,
					@C15,
                    C16,
                    C17,
                    C18,
                    C19,
                    C20,
                    C21,
                    0,0,0,0, 0, 0, 0, @C22 
            FROM    ( SELECT    Field1 ,
                                Field2,
                                Field3,
                                Field4,
                                Field5,
                                Field6
                      FROM      ( SELECT    Field1,
                                            UPPER(ISNULL(Field2, 'UNDEFINED')) AS Field2 ,
                                            UPPER(ISNULL(Field3, 'UNDEFINED')) AS Field3,
                                            UPPER(ISNULL(Field4,
                                                         'UNDEFINED')) AS Field4 ,
                                            UPPER(ISNULL(Field5, 'UNDEFINED')) AS Field5,
                                            UPPER(ISNULL(Field6,
                                                         'UNDEFINED')) AS Field6  
                                  FROM      dbo.MyTableOne
                                  WHERE Booktype = 'Fiction'
                                            
                                  GROUP BY  Field1,
                                            UPPER(ISNULL(Field2, 'UNDEFINED')) ,
                                            UPPER(ISNULL(Field3, 'UNDEFINED')) ,
                                            UPPER(ISNULL(Field4,
                                                         'UNDEFINED')) ,
                                            UPPER(ISNULL(Field5, 'UNDEFINED')) ,
                                            UPPER(ISNULL(Field6,
                                                         'UNDEFINED'))                                                       
                                 
                                  UNION ALL
                                  SELECT    Field1,
                                           Field2, ,
                                             Field3,
                                           Field4,
                                             Field5,
                                           Field6,
                                  FROM      dbo.MyTableTwo

				 WHERE Booktype = 'Fiction'
										
                                 UNION ALL
                                  SELECT    'TitlesAll' as Field1,
                                            Field2,
                                            Field3,
                                            Field4,
                                            Field5,
                                            Field6
                                  FROM      dbo.MyTableThree

										
                               Where Booktype = 'Fiction'
                                            

                                ) X
                    ) Y

This should be fairly easy - I have used SELECT INTO many times.

What I have tried:

I have tried including the INTO #temptable before the first "FROM" and then including it in every "FROM", but that did not work. I also used google search and I checked the MDSN website.

Posted 9-Jan-17 11:53am

Member 10379103

Updated 9-Jan-17 17:46pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2017-01-09T17:46:00

If I understand the question corrrectly, the INTO clause goes only after the first select, not in the inline views. Also if the temporary table doesn't exist, it is automatically created based on the select list.

Consider the following examples

SQL

BEGIN
   IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES tc  WHERE tc.TABLE_NAME like '#temptable1__%') BEGIN
      DROP TABLE #temptable1
   END
   SELECT * 
   INTO #temptable1
   FROM ( SELECT t.TABLE_NAME
          FROM INFORMATION_SCHEMA.TABLES t) a
END

SELECT * FROM #temptable1

produces something like

TABLE_NAME
----------
table1
table2
table3

and running the following

SQL

BEGIN
   IF EXISTS (SELECT 1 FROM tempdb.INFORMATION_SCHEMA.TABLES tc  WHERE tc.TABLE_NAME like '#temptable1__%') BEGIN
      DROP TABLE #temptable1
   END
   SELECT * 
   INTO #temptable1
	FROM ( SELECT t.TABLE_NAME, t.TABLE_SCHEMA
           FROM INFORMATION_SCHEMA.TABLES t) a
END

SELECT * FROM #temptable1

gives

TABLE_NAME   TABLE_SCHEMA
----------   ------------
table1       dbo
table2       dbo
table3       dbo