Define Field in an INSERT into CREATE Table Query

WEhalt
WEhalt used Ask the Experts™
on
What is the proper syntax to add fields with an create/insert query like this.  I would like to add them for later population.  I am trying to avoid inserting dummy data.

  SELECT CLID,
    LOB,
    @WEEK AS WEEK_ID,
    VARCHAR (10) AS CLAIM_LOC ,
    INT as DAYS_IN_PROC
  INTO #CLAIMS_BY_WEEK        
  FROM CMC_CLCL_CLAIM CLCL
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
By 'add fields' do you mean create new fields?  

If yes,

ALTER TABLE #CLAIMS_BY_WEEK
ADD CLID whatever, LOB whatever, WEEK_ID int, CLAIM_LOC varchar(10), DAYS_IN_PROC int

If no, you're trying to insert data, then...

INSERT INTO #CLAIMS_BY_WEEK (CLID, LOB, WEEK_ID, CLAIM_LOC, DAYS_IN_PROC)
SELECT CLID, LOB, @WEEK, 'No idea what you mean here by varchar(10),', 'Same here'
FROM CMC_CLCL_CLAIM

Author

Commented:
I believe I have seen a way to declare the columns with their types in the select statement.  That is what I mean by these lines

    VARCHAR (10) AS CLAIM_LOC ,
    INT as DAYS_IN_PROC

Author

Commented:
I got the following to work, but I really thought there was a way to embed the declaration right into the INSERT statement.

DECLARE @CLAIM_LOC VARCHAR(10)
DECLARE @CLAIM_PROC_DAYS_GRP VARCHAR(50)

  SELECT CLCL.*,
    @CLAIM_LOC AS CLAIM_LOC,
    @CLAIM_PROC_DAYS_GRP AS CLAIM_PROC_DAYS_GRP
  INTO #CLAIMS_BY_WEEK        
  FROM CLCL
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

I believe I have seen a way to declare the columns with their types in the select statement.  That is what I mean by these lines
No, that's not possible. However, you can cast it like the following:

SELECT CLCL.*,
    cast(@CLAIM_LOC as varchar(10)) AS CLAIM_LOC,
    cast(@CLAIM_PROC_DAYS_GRP as varchar(10)) AS CLAIM_PROC_DAYS_GRP
  INTO #CLAIMS_BY_WEEK        
  FROM CLCL
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>I believe I have seen a way to declare the columns with their types in the select statement.
ralmada is correct.  Data Defininition Language (DDL), aka edit the schema of something, and Data Manipulation Language (DML), aka select/insert/update stuff, can not occur in the same statement.

Also..

>SELECT CLCL.*,
>    @CLAIM_LOC AS CLAIM_LOC,
>    @CLAIM_PROC_DAYS_GRP AS CLAIM_PROC_DAYS_GRP

You can't return values and assign values to variables in the same statement.  They must be done separately.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the split.  Good luck with your project.  -Jim

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial