Inserting into a table using a subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inserting into a table using a subquery

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2012-07-10 : 09:52:14

Hello, I am trying to insert into a table using two subqueries and I am running into some difficulty. What am I doing wrong?

INSERT INTO dbo.Members  
SELECT * FROM ( 
SELECT DISTINCT C.[MBR_ID], USER_FULL_NM
FROM
(SELECT DISTINCT [MBR_ID]
FROM dbo.MEM_TABLE) C 
LEFT OUTER JOIN [MDR].[dbo].[CUST_TABLE] E 
    ON C.[MBR_ID] = E.[MBR_ID])

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-10 : 10:05:14

you should have stated specifically what the "some difficulty" is. But syntactically youo are missing a final table alias.


INSERT INTO dbo.Members  
SELECT * 
FROM ( 
      SELECT DISTINCT C.[MBR_ID], USER_FULL_NM
      FROM  (
            SELECT DISTINCT [MBR_ID]
            FROM dbo.MEM_TABLE
            ) C 
      LEFT OUTER JOIN [MDR].[dbo].[CUST_TABLE] E 
          ON C.[MBR_ID] = E.[MBR_ID]
       ) d

YOu have a lot of nested derived tables and DISTINCT key words there - wouldn't this work?


insert dbo.members
select C.[MBR_ID]
      ,USER_FULL_NM
from  dbo.mem_table c
left outer join [MDR].[dbo].[CUST_TABLE] E 
          ON e.[MBR_ID] = c.[MBR_ID]
group by C.[MBR_ID]
      ,USER_FULL_NM

Be One with the Optimizer
TG

Johnph
Posting Yak Master

103 Posts

Posted - 2012-07-10 : 10:28:41

Hey thanks, that did the trick

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-10 : 10:47:40

no sweat. :)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources