Join two temp tables

Question

I want to join two temp tables.

Here are my tables:

CREATE TABLE #Result ( Process varchar(50), SuccessCount int, FailureCount int)
CREATE TABLE #SuccessResult ( Process varchar(50), SuccessCount int)
CREATE TABLE #FailureResult ( Process varchar(50), FailureCount int)

I have some data in my tables, and here is my query:

INSERT INTO #Result (Process, SuccessCount, FailureCount)
SELECT s.Process,s.SuccessCount,f.FailureCount
FROM #SuccessResult s, #FailureResult as f
INNER JOIN #SuccessResult on f.Process = #SuccessResult.Process

I'm getting this error:

The multi-part identifier f.Process could not be found

I'm using Microsoft SQL Server 2008.

What am I doing wrong ?

Answer 1

Try this way:

INSERT INTO #Result (Process, SuccessCount, FailureCount)
SELECT s.Process,s.SuccessCount,f.FailureCount
FROM #SuccessResult s 
INNER JOIN #FailureResult f on f.Process = #SuccessResult.Process

Answer 2

This is the right syntax for INSERT INTO ... SELECT ...:

INSERT INTO #Result (Process, SuccessCount, FailureCount)
SELECT s.Process, s.SuccessCount, f.FailureCount
FROM #FailureResult as f
INNER JOIN #SuccessResult s on f.Process = s.Process