Merging two tables into one table

The script below was written by a member (OMG Ponies) for the question i posted last night. There were syntax error in the script i could not figure it out. Can someone please give it a try? It used MS SQL Server 2000/2005/2008

select 
'Steve'     as name, 
'4/20/1960' as DOB,
'12456'     as agentID, 
'Smith'     as agentName
into #TABLE1

insert into #TABLE1
select 
'Steve'        as name, 
'4/20/1960'    as DOB,
'12456'        as agentID, 
'John'         as agentName

insert into #TABLE1
select 
'Steve'        as name, 
'4/20/1960'    as DOB,
'12456'        as agentID, 
'Lary'         as agentName

select * from #TABLE1

.

+---------+-----------+----------+------------------+
|  Name   |    DOB      | AgentID |  AgentName      | 
+---------+-----------+----------+------------------+
| Steve    | 4/20/1960 | 12456     | John           |   
+---------+-----------+----------+------------------+
| Steve    | 4/20/1960 | 12456     | Lary           | 
+---------+-----------+-------开发者_运维知识库---+------------------+
| Steve    | 4/20/1960 | 12456     | Smith          | 
+---------+-----------+----------+------------------+


+---------+-----------+----------+----------------------+
|  Name   |    DOB      | AgentID |    AgentName        | 
+---------+-----------+----------+----------------------+
| Steve    | 4/20/1960 | 4444     | John,Larry, Smith   | 
+---------+-----------+----------+----------------------+

.

SELECT DISTINCT 
       t.name, 
       t.dob, 
       t.agentid, 
       STUFF(ISNULL(SELECT ', ' + x.agentname 
                      FROM TABLE1 x 
                     WHERE x.agentid = t.agentid 
                  GROUP BY x.agentname 
                   FOR XML PATH ('')), ''), 1, 2, '') 
  FROM TABLE1 t

[Error] Script lines: 1-10 ------------------------- Incorrect syntax near the keyword 'SELECT'. Msg: 156, Level: 15, State: 1, Procedure: , Line: 5

[Error] Script lines: 1-10 ------------------------- Incorrect syntax near ')'. Msg: 102, Level: 15, State: 1, Procedure: , Line: 9

---

This Works !

select 'Steve' as name, '4/20/1960' as DOB, '12456' as agentID, 'Smith' as agentName into #TABLE1

insert into #TABLE1 select 'Steve' as name, '4/20/1960' as DOB, '12456' as agentID, 'John' as agentName

insert into #TABLE1 select 'Steve' as name, '4/20/1960' as DOB, '12456' as agentID, 'Lary' as agentName

select * from #TABLE1

SELECT DISTINCT t.name, t.dob, t.agentid, STUFF(ISNULL((SELECT ', ' + x.agentname FROM #TABLE1 x WHERE x.agentid = t.agentid GROUP BY x.agentname FOR XML PATH ('')), ''), 1, 2, '') FROM #TABLE1 t
DROP TABLE #Table1

--+---------+-----------+----------+------------------+ | Name | DOB | AgentID | AgentName | +---------+-----------+----------+------------------+ | Steve | 4/20/1960 | 12456 | John |
--+---------+-----------+----------+------------------+ | Steve | 4/20/1960 | 12456 | Lary | +---------+-----------+----------+------------------+ | Steve | 4/20/1960 | 12456 | Smith | +---------+-----------+----------+------------------+

--+---------+-----------+----------+----------------------+ | Name | DOB | AgentID | AgentName | +---------+-----------+----------+----------------------+ | Steve | 4/20/1960 | 4444 | John,Larry, Smith | +---------+-----------+----------+----------------------+