t sql select into existing table new column

Question

Hi I have a temp table (#temptable1) and I want to add a column from another temp table (#temptable2) into that, my query is as follows:

select 
Customer
,CustName
,KeyAccountGroups
,sum(Weeksales) as Weeksales
into #temptable1
group by Customer
,CustName
,KeyAccountGroups


select
SUM(QtyInvoiced) as MonthTot
,Customer
into #temptalbe2
from SalesSum
where InvoiceDate between @dtMonthStart and @dtMonthEnd
group by Customer


INSERT INTO #temptable1
SELECT MonthTot FROM #temptable2
where #temptable1.Customer = #temptable2.Customer

I get the following: Column name or number of supplied values does not match table definition.

[Check this post,](http://stackoverflow.com/questions/1152932/column-name-or-number-of-supplied-values-does-not-match-table-definition) i think you face the same issue — gefrag, May 08 '13 at 09:08

Edwin Stoteler · Answer 1 · 2013-05-08T09:30:11.963

0

If I understand it correctly you want to do two things. 1: Alter table #temptable1 and add a new column. 2: Fill that column with the values of #temptable2

ALTER #temptable1 ADD COLUMN MothTot DATETIME

UPDATE #temptable1 SET MothTot = (
    SELECT MonthTot 
    FROM #temptable2
    WHERE #temptable2.Customer = #temptable1.Customer)

edited May 08 '13 at 09:30

answered May 08 '13 at 09:07

Edwin Stoteler

1,218
1
10
25

Hi I updated #temptable1 so that all the NULL values are zero, but now I get Invalid column name 'Monthtot'. – Wilest May 08 '13 at 09:12
@wilest Update your question with your #temptable1 description. I assumed your #temptable1 had a Monthtot column. – Edwin Stoteler May 08 '13 at 09:15
No I want to add a new column from temptable2 to temptable1, I'll update my question – Wilest May 08 '13 at 09:17
@wilest Ok I think I understand your question now, will update my answer. give me a few minuts. – Edwin Stoteler May 08 '13 at 09:23

score 0 · Accepted Answer · answered May 08 '13 at 09:20

In an INSERT statement you cannot reference the table you are inserting into. An insert works under the assumption that a new row is to be created. That means there is no existing row that could be referenced.

The functionality you are looking for is provided by the UPDATE statement:

UPDATE t1
SET MonthTot = t2.MonthTot 
FROM #temptable1 t1
JOIN #temptable2 t2
ON t1.Customer = t2.Customer;

Be aware however, that this logic requires the Customer column in t2 to be unique. If you have duplicate values in that table the query will seem to run fine, however you will end up with randomly changing results.

For more details on how to combine two tables in an UPDATE or DELETE check out my A Join A Day - UPDATE & DELETE post.

t sql select into existing table new column

2 Answers2