如何使用SELECT into将这些字段插入临时表 [英] How do I insert these fields into a temp table using SELECT into

问题描述

我需要使用SELECT INTO将这些字段选择到临时表中，而不是先创建临时表及其所有列。区DBA只想添加或删除字段而无需每次都更新表：

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:

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

这应该相当容易 - 我使用SELECT INTO很多ti mes。



我尝试了什么：



我试过包括在第一个FROM之前的INTO #temptable，然后将它包含在每个FROM中，但这不起作用。我也使用谷歌搜索，我检查了MDSN网站。

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.

推荐答案

如果我正确理解这个问题，INTO子句只在第一次选择之后，而不是在内联中观点。此外，如果临时表不存在，则会根据选择列表自动创建。



请考虑以下示例

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

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

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

这篇关于如何使用SELECT into将这些字段插入临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！