Create temp table with from existing table adding a new column

Question

Post reply

Create temp table with from existing table adding a new column

beth0802

SSC Enthusiast

Points: 183
More actions
May 20, 2010 at 10:21 am

#141053

I have a table (FOX.YPROFIT) where I need to write 6 of the columns to a new 7 column temp table (DEV2.AATMPPROFIT) where the 7th column is a new Column in the temp table. I'm trying @ARow to create the value for this column. I'm getting this error:
Server: Msg 245, Level 16, State 1, Line 26
Syntax error converting the varchar value 'INSERT INTO DEV2.AATMPPROFIT SELECT NUMREQ_0, GRPID_0, ACTUAL_' to a column of data type int.
DELETE TABLE DEV2.AATMPPROFIT
CREATE TABLE DEV2.AATMPPROFIT
(NumReq INT, GrpId NVARCHAR(20),
Act NUMERIC(18,3),
Bud NUMERIC(18,3),
Pri NUMERIC(18,3),
Arow VARCHAR(2))
DECLARE @rpt VARCHAR(20);
SET @rpt = '279560';
DECLARE @cnt INT;
SET @cnt = '0';
DECLARE @ARow VARCHAR(2);
SET @ARow = '0';
DECLARE @sql VARCHAR(1000);
SET @sql = '';
WHILE @cnt < 25
BEGIN
SET @sql = (
'INSERT INTO DEV2.AATMPPROFIT ' +
' SELECT ' +
' NUMREQ_0, ' +
' GRPID_0, ' +
' ACTUAL_' + @cnt + ', ' +
' BUDGET_' + @cnt + ', ' +
' PRIOR_0' + @cnt + ', ' +
@ARow +
' FROM FOXTAIL.YPROFIT WHERE NUMREQ_0 = ' + @rpt);
exec (@sql);
SET @cnt = @cnt + 1;
SET @ARow = @ARow + 1;
END
SELECT * FROM DEV2.AATMPPROFIT;
The field names in the source table are like: ACTUAL_1, ACTUAL_2, ACTUAL_3, etc.
I've tried both VARCHAR and INT. If I use INT, I get an error. If I use VARCHAR, the variable is 00, 01, 02... which causes a problem because the result must not have a leading 0.
There's much more to be done, but I'm hoping that if I get this resolved, I'll be able to move forward. Any help is appreciated.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Don't use INSERT INTO, rather use SELECT * INTO for the new 7 column temp table. Like:

SELECT COL1, COL2, COL2 , COL4, COL5, COL6, NULL COL7

INTO #TEMP

FROM REALTABLE

And then u implement the logic to fill that column!

Hope this helps you!

Jeff Moden SSC Guru Points: 1004682 More actions · Answer 2

... and, let's say you wanted COL7 to be a NOT NULL INTEGER column...

SELECT COL1, COL2, COL2 , COL4, COL5, COL6, ISNULL(CAST(0 AS INT),0) AS COL7

INTO #TEMP

FROM REALTABLE

It's obvious what the CAST does. The ISNULL is responsible for making the column with a NOT NULL constraint. Works dandy especially when the column is going to be a candidate for a PK.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Jeff Moden (5/21/2010)
The ISNULL is responsible for making the column with a NOT NULL constraint. Works dandy especially when the column is going to be a candidate for a PK.

Ah, thats the new thing i learnt today... Thanks Jeff 🙂