locked
Insert data from one table to another table RRS feed

  • Question

  • I created a table using two other tables. Following is the Query that i used

    SELECT     AccountBase.AccountId AS [ID], AccountBase.Name AS [UserName], AccountBase.Telephone1 AS Telephone,
                          AccountExtensionBase.New_CustomerClass AS [CustomerClass]
    INTO            [SMSAccount]
    FROM         AccountBase INNER JOIN
                          AccountExtensionBase ON AccountBase.AccountId = AccountExtensionBase.AccountId

    Now i want to insert data into the above table i only want a data that present in parent but that's not present in new. I created a insert statemet but the pblm is it will insert the entire parent table data and will cause duplication .

    This is the insert statement that i tried

    Insert into SMSAccount( [ID] , [UserName] , [Telephone], [CustomerClass])
    select AccountBase.AccountId,AccountBase.Name,AccountBase.Telephone1, AccountExtensionBase.New_CustomerClass from AccountBase,AccountExtensionBase
    WHERE AccountBase.AccountId NOT IN (SELECT ID FROM SMSAccount)

    Please help
    Tuesday, March 9, 2010 7:26 AM

Answers

  • I think the issue could be that your FROM clause in your Insert statement has an old style join syntax (FROM TableA, TableB) with no "column clause", indicating a CROSS join. That may explain why you are getting duplicates.

    Look to change the JOIN syntax to that similar to your top query (INNER JOIN AccountExtensionBase ON AccountBase.AccountId = AccountExtensionBase.AccountId).
    every day is a school day
    • Marked as answer by Sd4u Tuesday, March 9, 2010 9:31 AM
    Tuesday, March 9, 2010 9:15 AM

All replies

  • Here some methods to test for non-existance.


    Create table #a
    (
    id integer primary key
    )
    Create table #b
    (
    id integer
    )
    insert into #a values(1)
    insert into #b values(1)
    insert into #b values(2)
    go
    select * from #b left join #a
              on #b.id = #a.id
    where #a.id is null
    
    go
    select * from #b
    where not exists (select 1 from #a where #a.id = #b.id)
    go
    select * from #b
    where #b.id not in (select id from #a )
    You're NOT IN should work , but are you getting confused as you havent joined AccountBase and AccountExtensionBase ?

    Dave Ballantyne ---- http://sqlandthelike.blogspot.com
    Tuesday, March 9, 2010 8:56 AM
  • I think the issue could be that your FROM clause in your Insert statement has an old style join syntax (FROM TableA, TableB) with no "column clause", indicating a CROSS join. That may explain why you are getting duplicates.

    Look to change the JOIN syntax to that similar to your top query (INNER JOIN AccountExtensionBase ON AccountBase.AccountId = AccountExtensionBase.AccountId).
    every day is a school day
    • Marked as answer by Sd4u Tuesday, March 9, 2010 9:31 AM
    Tuesday, March 9, 2010 9:15 AM
  • Thanks richbrownesq thats works fine for me.
    Tuesday, March 9, 2010 9:32 AM