Append - INSERT INTO Problem...

Results 1 to 2 of 2

Thread: Append - INSERT INTO Problem...

  1. #1
    Join Date
    Dec 1969

    Default Append - INSERT INTO Problem...

    Hello,<BR><BR>Scenario:<BR><BR>* Two tables named, "Current_Data" and "History_Data." No Unique<BR>Identifiers or Primary Keys in both tables and the "History..." table<BR>possibly contains some NULL or duplicate values that could be found in the<BR>"Current..." table - recent history values, perhaps.<BR><BR>* Created 3rd table called, "New_Data" with Primary Keys - consisting of<BR>two columns making a record unique and not allowing NULL values in those two<BR>columns, understandably.<BR><BR>* Function of the 3rd table is to populate itself by synchronizing the<BR>unique values from "Current_Data" and "History_Data" tables. So at the end,<BR>"New_Data" includes all the UNIQUE rows (values) of both of the earlier<BR>mentioned tables.<BR><BR>* Now, I can easily APPEND values from, let&#039;s say, the "Current_Data" to<BR>the "New_Data" table, but because the "New_Data" table cannot allow NULL in<BR>the columns defined as Primary Keys and also requires UNIQUE values in those<BR>columns, my second APPEND (INSERT INTO New_Data.* FROM History_Data.*)<BR>statement fails giving me an error something like this, "Insert Into<BR>statement was terminated because the destination table (columns) doesn&#039;t<BR>allow NULL values and has no DEFAULT specified either." The sole purpose of<BR>this INSERT is to avoid those NULL and duplicate values so I can&#039;t drop the<BR>primary keys nor allow NULL values in this new table.<BR><BR>* My question is that how can I run this INSERT query so that it inserts<BR>all the UNIQUE values and discards duplicates and NULL values? How can I<BR>accomplish the task of having a table with UNIQUE values only? Is there a<BR>way to run the query so that if there were 200 records and 50 of which were<BR>duplicates or NULL, it at least adds the remaining 150 and gives me a<BR>message that the other 50 weren&#039;t inserted because of the primary key<BR>constraints instead of having to terminate the whole insert statement and<BR>not adding any data at all? I know you could do this in MS Access, but my<BR>tables are huge and it simply freezes if I try to run this query in MS<BR>Access!<BR><BR>If you could suggest anything or are able to direct me where I can learn<BR>more on this, please-please reply!<BR><BR>Thank you much,<BR>Jayna

  2. #2
    Join Date
    Dec 1969

    Default RE: Append - INSERT INTO Problem...

    (hopefully this works in Access, but no guarantees)<BR><BR>SELECT DISTINCT * INTO New_Data FROM <BR> (SELECT * FROM Current_Data<BR> UNION <BR> SELECT * FROM History_Data) as A<BR>WHERE [field1] IS NOT NULL AND [field2] IS NOT NULL<BR><BR>Of course you have to replace the [field1] with your own field names.<BR><BR>Please note:<BR><BR>The distinct function works for all the fields, so if you have a row in Current_Data and a row in History_Data, and all fields *except* one are the same both rows are inserted into the new table.

Posting Permissions

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