Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169

    Unanswered: SQL insert into query

    I have a small SQL question, I want to insert into a table the unique values from another table, I have tried using a Select Distinct query but it still seems to return values that have more than one value in the table.

    The query is as follows

    Code:
    SQL = "Insert Into [Maintainance Temp] ( SiteMNSiteID )"
        SQL = SQL + " Select Distinct [Site Maintainance Due].SiteMNSiteID"
        SQL = SQL + " From [Site Maintainance Due]"
    Like i say, it still seems to return values that have more than one record in the table? I dont know if distinct is even the word i am looking for for it to work??

    There is only two records in my table to make it simple and they are both the same, any help?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your sql is correct

    if you run the SELECT by itself, you will see

    if it appears to produce duplicates, it is possible that you have values that are identical except one of them has a trailing blank
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Is the table you are inserting into already populated with data? If so, you may be adding data that already exists, causing the duplicates.

    If that is the case, try this:
    Code:
    SQL = "INSERT INTO [Maintainance Temp] ( SiteMNSiteID )" & _
          "SELECT DISTINCT [Site Maintainance Due].SiteMNSiteID " & _
          "FROM [Site Maintainance Due] LEFT JOIN [Maintainance Temp] " & _
          "ON [Site Maintainance Due].SiteMNSiteID = " & _
          "[Maintainance Temp].SiteMNSiteID " & _
          "WHERE [Maintainance Temp].SiteMNSiteID Is Null;"
    Using the Left Join gets all records on the desired table and the "Is Null" returns only the ones that are not found in the other table; the rest is the same.

    Have fun!
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  4. #4
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    I want to insert into the table only the records from the other table where there is one of the records.

    I have only two records in the source table, they are both EXACTLY the same, except for the autonumber.

    I have written the SQL and clicked the button to run it and in my eyes there should be no records being inserted into the other table? I only want records from the source table to be inserted into the other table if there is only one site in the source table.

    I dont know if that makes sense.

    Here is what the table is for, might make more sense if i tell you what i am doing.

    I am trying to insert records into a table so i can query the whole table to find out Sites that ar due a renewal contract for their maintainance, the maintainance table could have 6 years of renewals in for the same site so if they have more than one contract in the maintainance table then I dont want to send them a renewal contract.

    At the moment the maintainance table has exactly the same information in twice so I know that there should be no records going into the table i want to query because that site has more than one contract in the table?

    You see what I mean?

    At the moment it is trying to insert 1 record into the other table and that isnt what I want it to do?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am afraid that if you want further help on this, you are going to have to show a few sample rows in their entirety from each table

    "when there is only one of the records" is a not the same as DISTINCT SiteMNSiteID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169

    OK here goes

    This is the layout of the table

    SiteMNID SiteMNSiteID SiteMNDue
    699 15 01/03/2005
    700 15 01/03/2005

    These are the only two records in the Site Maintainance Due table

    If i run the query I want the query to just extract unique SiteMNSiteID field

    So in the table at the moment it wouldnt extract any information

    If the table was like this

    SiteMNID SiteMNSiteID SiteMNDue
    699 15 01/03/2005
    700 15 01/03/2005
    701 16 01/03/2005

    Then it would extract the last line and insert it into the Temporary table so i can analyse what i need to analyse

    So in the new temp table there would be one line

    SiteMNID SiteMNSiteID SiteMNDue
    701 16 01/03/2005

    Hope that makes a bit more sense

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it's clear now

    instead of "just extract unique SiteMNSiteID" what you want is "extract all SiteMNSiteID that occur just once"
    Code:
    insert 
      into [Maintainance Temp] 
         ( SiteMNSiteID )
    select SiteMNSiteID
      from [Site Maintainance Due]
    group 
        by SiteMNSiteID
    having count(*) = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169

    Thanks!!!!!

    Works a treat that, need to brush up a little on my SQL i think, Ive only need to do really simple inserts up until that really! I think i ould get a lot more out of SQL if i read a little of the books!

    Thanks again!

    A* Answer, straight to the point!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •