Create a temp table from another temp table and exclude some fields

Question

Create a temp table from another temp table and exclude some fields

Hursh 191

;WITH cte AS
(
SELECT ID, CustNmbr, CustAddr, CustPhone, ProdNum, ProdDesc
FROM Customers
)
SELECT * INTO #TempCust FROM Results

Next, I want to SELECT * INTO #TempData but do not want to include the column "ID" and without explicitly naming each fields in it?

4 answers

Your answer

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

Sorry, no feature to do this.

Overall, I prefer explicit CREATE TABLE over SELECT INTO. And SELECT * is generally considered bad practice.

Hursh 191 Reputation points

2022-07-19T22:54:38.077+00:00

In my case, I have a long list of fields and I am trying to avoid declaring these fields.
And, I am inserting into a temp table and feel comfortable with SELECT INTO.

Answer 2

Tom Phillips 17,771

You can't.

The syntax for columns list is either * for all columns, or you must list every column individually separated by commas.

See:
https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql?view=sql-server-ver16

Answer 3

LiHong-MSFT 10,056

Hi @Hursh
For select list, you cannot use *(all columns) and also remove some columns at the same time.
Considering that you have many columns in your table, you can execute the query below to get all the columns of the temp table, then copy the result of the query and manually delete the columns you don't need, which saves time of manual typing.

DECLARE @Column_Names VARCHAR(MAX)  
  
SELECT @Column_Names=ISNULL(@Column_Names + ',','')+QUOTENAME(name)  
FROM   tempdb.sys.columns  
WHERE  Object_id = Object_id('tempdb..#TempCust');   
  
PRINT @Column_Names

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.

Hursh 191 Reputation points

2022-07-20T02:30:24.973+00:00

When this stored procedure is executed with table name and 4 column name that needed to be excluded from the result set; it returns records with desired columns.

EXEC [dbo].[MyProc] MyTable, ID, OrderNum, CustNum, Product

ALTER PROCEDURE [dbo].[MyProc]
@Table varchar(50) = '',
@Col1 varchar(50) = '',
@Col2 varchar(50) = '',
@Robert Cole varchar(50) = '',
@Col4 varchar(50) = ''

AS

DECLARE @alenzi NVARCHAR(MAX)

SELECT @alenzi = COALESCE(@alenzi + ', ', ' ' ) + name from sys.columns where name not in (@Col1, @Col2, @Robert Cole , @Col4)
and object_id = (Select id from sysobjects where name = @Table)
SELECT @alenzi = 'SELECT ' + @alenzi + ' FROM ' + @Table

EXEC sp_executesql @alenzi
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-07-20T08:00:27.637+00:00

SELECT @column _Names=ISNULL(@column _Names + ',','')+QUOTENAME(name)

Please note that the correct result of this operation is not defined. That is, you may get what you expect, or you may get something else, but that would be a bug.

We have string_agg for this purpose. However, while it is theoretically possible to do this, it is very rarely the correct solution. And even less for a beginner, which Hursh seems to be.

Answer 4

Erland Sommarskog 121.4K MVP Volunteer Moderator

Slightly better than dabbling with dynamic SQL may be to get all columns into a temp table, and then

   ALTER TABLE #temp DROP COLUMN IDontWantThisOne

But, here too, the risk is that you outsmart yourself, if you don't know what you are doing.

The best solution in most cases is to list all columns in CREATE TABLE, no matter how boring it may seem.

Share via

Create a temp table from another temp table and exclude some fields

4 answers

Your answer