Define Field in an INSERT into CREATE Table Query
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
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
ASKER
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
VARCHAR (10) AS CLAIM_LOC ,
INT as DAYS_IN_PROC
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the split. Good luck with your project. -Jim
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