select into use case situations

Question

select into use case situations

Jonathan Brotto 1,076

Was wondering where would I use a select into keywords as it seems to make a view or another temp like table to get data from based on my understanding.

Accepted answer

3 additional answers

Your answer

Answer 1

Hi @Jonathan Brotto
The SELECT INTO statement creates a new table from the result set of a query. The columns of the new table inherit their names, data types, nullability, and IDENTITY property from the query’s result set.
SELECT INTO doesn’t copy constraints, indexes, or triggers from the query’s source. If you need the results in a table with the same indexes, constraints, and triggers as the source, you have to add them afterward.
Remember that the SELECT INTO statement both creates the target table (DDL) and populates it with the result set produced by the query (DML).

I often use SELECT INTO to create temporary tables for intermediate results. And sometimes use SELECT INTO to create backup copies of tables or to archive records.
For example,

SELECT * INTO Persons_backup FROM Persons

If you need a quick and dirty empty copy of some table, SELECT INTO allows you to obtain such a copy very simply. You don’t have to script the CREATE TABLE statement and change the table’s name—you just need to issue the following statement:

SELECT * INTO target_table FROM source_table WHERE 1 = 2;

Suppose you want to insert the result set of a stored procedure or a dynamic batch into a new table, but you don’t know what table structure you need to create. You can use a SELECT INTO statement, specifying OPENQUERY in the FROM clause, referring to your own server as if it were a linked server:

EXEC sp_serveroption <your_sever> , 'data access', true;   
SELECT * INTO <target_table>  
FROM OPENQUERY (<your_sever>, 'EXEC { <proc_name> | (<dynamic_batch>)}') AS O;

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Erland Sommarskog 121.5K Reputation points MVP Volunteer Moderator

2022-02-16T22:27:27.327+00:00

Very good write-up on SELECT INTO! Although, the last thing... Yes, you can do that, but you can also run into a lot of problems with it. It is definitely nothing I recommend.

I bash the method in more detail here: https://www.sommarskog.se/share_data.html#OPENQUERY.

Answer 2

Naomi Nosonovsky 8,431

I use this quite often if I need to use intermediate results but think cte may perform poorly or need to use results of the query in a stored procedure, for example, but don't want to actually define the structure of the temp table. I also often use it in ad-hoc queries.

Jonathan Brotto 1,076

I was thinking to if select into would improve the query. Will post the reset of the code in comment below.

DECLARE @Slp AS INT
SET @Slp =  '39'

;
WITH 
-- only fetch items with sales
ItemsInfo (CardCode,ItemCode,TotalQuantity,Total,SalesYear) 
    AS  (
        SELECT
        [Items].[CardCode]
        ,[Items].[ItemCode]
        ,Sum([Items].[Quantity])
        ,Sum([Items].[Total])
        ,[Items].[Year]
        FROM
        (
                SELECT
                     [T2].[CardCode]
                    ,[T0].[ItemCode]
                    ,ISNULL(SUM([T0].[Quantity]),0) AS [Quantity]
                    ,ISNULL(SUM(T0.LineTotal),0) AS [Total]
                    ,YEAR([T0].[DocDate]) AS [Year]
                FROM [INV1] T0 
                INNER JOIN [OINV] T1 ON [T0].[DocEntry] = [T1].[DocEntry]
                INNER JOIN [OCRD] T2 ON [T2].[CardCode] = [T1].[CardCode]
                WHERE 
                YEAR([T0].[DocDate]) >= '2019' AND YEAR([T0].[DocDate]) <= '2021'
                AND T2.SlpCode = @Slp
                AND T1.CANCELED = 'N' /*only not canceled Documents*/
                GROUP By [T2].[CardCode], [T0].[ItemCode],YEAR([T0].[DocDate])
            UNION ALL

SELECT
[T2].[CardCode]
,[T0].[ItemCode]
,ISNULL(SUM([T0].[Quantity])-1,0) AS [Quantity]
,ISNULL(SUM(T0.LineTotal)-1,0) AS [Total]
,YEAR([T0].[DocDate]) AS [Year]
FROM [RIN1] T0
INNER JOIN [ORIN] T1 ON [T0].[DocEntry] = [T1].[DocEntry]
INNER JOIN [OCRD] T2 ON [T2].[CardCode] = [T1].[CardCode]
WHERE
YEAR([T0].[DocDate]) >= '2019' AND YEAR([T0].[DocDate]) <= '2021'

Tom Phillips 17,771 Reputation points

2022-02-16T17:37:00.447+00:00

Select into will not likely have any affect in your query.

If you are having a problem with query performance, please post a new question with the query plan.

Jonathan Brotto 1,076

other half of CTE and eventually main query

AND T2.SlpCode = @Slp  
				AND T1.CANCELED = 'N' /*only not canceled Documents*/  
				GROUP By [T2].[CardCode], [T0].[ItemCode],YEAR([T0].[DocDate])  
		) [Items]  
  
  
		GROUP BY [Items].CardCode, [Items].[ItemCode], [Items].[Year]  
		)

Jonathan Brotto 1,076

Main query

SELECT DISTINCT TOP 25

     [ItemsInfo].[CardCode]
    ,[OCRD].[CardName]
    ,[ItemsInfo].[ItemCode]
    ,[OITM].[ItemName]
    ,ISNULL((SELECT [T0].[TotalQuantity] FROM [ItemsInfo] [T0] WHERE T0.[CardCode] = [ItemsInfo].[CardCode] AND [T0].[ItemCode] = [ItemsInfo].[ItemCode] AND [T0].[SalesYear] = 2019),0) AS 'Quantity 2019'
    ,ISNULL((SELECT [T0].[TotalQuantity] FROM [ItemsInfo] [T0] WHERE T0.[CardCode] = [ItemsInfo].[CardCode] AND [T0].[ItemCode] = [ItemsInfo].[ItemCode] AND [T0].[SalesYear] = 2020),0) AS 'Quantity 2020'
    ,ISNULL((SELECT [T0].[TotalQuantity] FROM [ItemsInfo] [T0] WHERE T0.[CardCode] = [ItemsInfo].[CardCode] AND [T0].[ItemCode] = [ItemsInfo].[ItemCode] AND [T0].[SalesYear] = 2021),0) AS 'Quantity 2021'
    ,ISNULL((SELECT [T0].[Total] FROM [ItemsInfo] [T0] WHERE T0.[CardCode] = [ItemsInfo].[CardCode] AND [T0].[ItemCode] = [ItemsInfo].[ItemCode] AND [T0].[SalesYear] = 2019),0) AS 'Total $ 2019'
    ,ISNULL((SELECT [T0].[Total] FROM [ItemsInfo] [T0] WHERE T0.[CardCode] = [ItemsInfo].[CardCode] AND [T0].[ItemCode] = [ItemsInfo].[ItemCode] AND [T0].[SalesYear] = 2020),0) AS 'Total $ 2020'
    ,ISNULL((SELECT [T0].[Total] FROM [ItemsInfo] [T0] WHERE T0.[CardCode] = [ItemsInfo].[CardCode] AND [T0].[ItemCode] = [ItemsInfo].[ItemCode] AND [T0].[SalesYear] = 2021),0) AS 'Total $ 2021'
FROM 
    [ItemsInfo] 
INNER JOIN [OCRD] ON [OCRD].[CardCode] = [ItemsInfo].[CardCode]
INNER JOIN [OITM] On [OITM].[ItemCode] = [ItemsInfo].[ItemCode]
ORDER BY 10 DESC

Naomi Nosonovsky 8,431 Reputation points

2022-02-16T17:57:18.463+00:00

It may help, especially when CTE is recursive. By quick glance I see you're using YEAR(date) condition in your WHERE clause. I suggest to change to use date directly and use @StartOfYear @EndOfYear variables or calculate directly. Using YEAR(date) in the query may result in the bad performance. That's just from the very quick cursory glance.
Jonathan Brotto 1,076 Reputation points

2022-02-16T18:23:12.463+00:00

For the year thing then maybe do something like T0.[DocDate] >= '2019-01-01' AND T0.[DocDate] <= '2021-12-31'

OR

T0.[DocDate] Between '2019-01-01' AND '2021-12-31'
Jonathan Brotto 1,076 Reputation points

2022-02-16T18:30:23.837+00:00

I was just curious about the whole performance thing as the last box I worked with was 2005 and even though I had some experience with 2012 and 2014 never rewrote those queries. At my new job since I am starting from scratch on a 2019 box I was trying all the new features to see what works.

Learning what works for performance path for queries.

https://www.mssqltips.com/sqlservertip/4658/performance-improvement-for-sql-server-select-into-tsql-statement/
Naomi Nosonovsky 8,431 Reputation points

2022-02-16T18:30:26.26+00:00

Yes, especially if it's date and not a datetime. I would use between if it's a date type. If it's datetime I may use
datetime>='20190101' and datetime<'20220101' I also like to use dates without - (this is my habit after I read Tibor's blog about 10 or more years ago).
Jonathan Brotto 1,076 Reputation points

2022-02-16T18:31:30.77+00:00

Any idea for the select into as I am still trying to wrap my head around it.
Naomi Nosonovsky 8,431 Reputation points

2022-02-16T18:36:37.573+00:00

I see another problem. It seems like you're pivoting your data. Use case based expressions, it will perform much better. Let me know if you need more help or can review a query in this forum about PIVOT (one was posted today).
Jonathan Brotto 1,076 Reputation points

2022-02-16T18:37:31.303+00:00

Anything will help.
Naomi Nosonovsky 8,431 Reputation points

2022-02-16T18:44:37.51+00:00

Can you please post your first query again (with corrections I suggested)? Then we can work for the main query after that. Please post it as an answer so it would be easier to change.

I think for your case select into #temp or creating temporary ItemsInfo as CREATE TABLE #ItemsInfo may help.

Answer 3

Erland Sommarskog 121.5K MVP Volunteer Moderator

I may use SELECT INTO for ad-hoc operations to create temp tables for intermediate result. But in application code, I prefer to create temp tables explicitly with CREATE TABLE and populate them with INSERT.

SELECT INTO can be faster than CREATE TABLE + INSERT because it can be minimally logged. But so can CREATE TABLE + INSERT as well, if you use the TABLOCK hint. And for temp tables you should not need that hint.

Answer 4

Naomi Nosonovsky 8,431

This may be more that you asked for, but it should show you the concept of pivoting and case expressions. In your case you need non dynamic version.

https://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

Share via

select into use case situations

3 additional answers

Your answer