Need to insert a value into a column during insert. 1

[dbleyl]

Hi,

I'm inserting 2 columns of data into a table with three columns. The third column is a foreign key. Here's the scenario in more detail:

sp1 calls sp2 and inserts the results into a temporary table.

sp1 then performs a join on the temp table and another table, returning one column from each table.

I need to populate the third column with a value that's the same for each row, but different each time sp1 is called. In other words, when I call sp1, I want to pass it the value for column 3.

I'm trying DEFAULT on the third column, but I get this:
Insert Error: Column name or number of supplied values does not match table definition.

Here's the attempt:
CREATE TABLE #Cars ([QUANTITY] [int] NULL,
[DESC] [varchar](25) NOT NULL,
[AREA] [smallint] NULL DEFAULT ( 7) )

INSERT #Cars
EXEC MyStoredProc @X=@Y --Returns 2 columns.

--Need below to be an insert, instead of select.
SELECT D.[COUNT], B.[VehicleID] , D.[AREA]
FROM #Cars as D INNER JOIN PolkToNapaCars as B ON
D.MX = B.DESC

DROP TABLE #DCCars

It doesn't have to use default, I just need the value I pass to populate the 3rd column for that call.

Thanks for your help,
-Don

 

[tlbroadbent]

The value you want in the 3rd column can be passed to the sp1 as @param.

Insert yourtbl
Select #Cars.Quantity, #Cars.Desc, @param
FROM #Cars as D INNER JOIN PolkToNapaCars as B
ON D.MX = B.DESC

The value of @param will be inserted in the 3rd column of each row.
Terry

http://members.home.net/tlbroadbent/index.htm