Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Posts
    38

    Unanswered: Trouble importing data

    Hi, and thank you for your time

    I'm using SQLite engine

    Here's what I have so far, a table called CLIENT with 36 columns none of which are have any unique value, for example there is FirstName, LastName, Height, Weight, HairColour ...

    UserA wants to import the clients from UserB's database without repeating the clients already in the database

    I have attached UserB to UserA and the following SQL to get the unique data from UserB

    Select CLIENT.* from UserB.CLIENT
    Except
    Select CLIENT.* from CLIENT

    this works however my problem is I need the UserB.Client.ROWID so I can do the rest of the updating from other tables.

    I tried

    Select CLIENT.ROWID from UserB.CLIENT
    where
    (Select CLIENT.* from UserB.CLIENT
    Except
    Select CLIENT.* from CLIENT)

    But that returns empty every time. Any suggestions ?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I guess ROWID won't help much here; you'd perhaps better stick to unique client identifier (let's call it CLIENT_ID) and try something like this (or its variants):
    Code:
    INSERT INTO usera.CLIENT 
      (client_id, col2, ..., coln) 
    SELECT b.client_id, b.col2, ... b.coln
      FROM userb.CLIENT b
      WHERE b.client_id NOT IN (SELECT a.client_id FROM usera.CLIENT a);

  3. #3
    Join Date
    Jun 2005
    Posts
    38
    No this database can not have a Unique CLIENT_ID it just won't work. John Doe may work for 3 or 4 different companies so when Company A inputs him as Employee #234 and Company B inputs him as Employee #543 the head hunter will have him in their database twice because the CLIENT_ID will be different. Though it's nice to think one can use a government ID for this, however it's illegal to do, and not many people want that information passed around.

    I can find the Unique entries in the file being imported, I just need to get the rowID's from the row(s) because it's a one to many table that relies on the rowID for this

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I still think that ROWID isn't the right choice; the same record inserted in two tables in two schemas is likely to have different ROWID. Look at this example:

    First, create tables and insert some records in there:
    Code:
    SQL> CREATE TABLE tab_1 (empno NUMBER, ename VARCHAR2(20));
    
    Table created.
    
    SQL> CREATE TABLE tab_2 (empno NUMBER, ename VARCHAR2(20));
    
    Table created.
    
    SQL> INSERT  INTO tab_1 VALUES (1, 'John');
    
    1 row created.
    
    SQL> INSERT  INTO tab_2 VALUES (1, 'John');
    
    1 row created.
    
    SQL> INSERT  INTO tab_1 VALUES (2, 'Mick');
    
    1 row created.
    
    SQL> INSERT  INTO tab_2 VALUES (2, 'Mick');
    
    1 row created.
    
    SQL> INSERT  INTO tab_1 VALUES (3, 'Anna');
    
    1 row created.
    
    SQL> INSERT  INTO tab_2 VALUES (3, 'Anna');
    
    1 row created.
    Now select those records with ROWIDs - they will be equal:
    Code:
    SQL> SELECT ROWID, empno, ename FROM tab_1;
    
    ROWID                   EMPNO ENAME
    ------------------ ---------- --------------------
    AAAOOLAAEAAA1MVAAA          1 John
    AAAOOLAAEAAA1MVAAB          2 Mick
    AAAOOLAAEAAA1MVAAC          3 Anna
    
    SQL> SELECT ROWID, empno, ename FROM tab_2;
    
    ROWID                   EMPNO ENAME
    ------------------ ---------- --------------------
    AAAOOMAAEAAA1RFAAA          1 John
    AAAOOMAAEAAA1RFAAB          2 Mick
    AAAOOMAAEAAA1RFAAC          3 Anna
    OK, now delete some records and insert them back:
    Code:
    SQL> DELETE FROM tab_1 WHERE empno < 3;
    
    2 rows deleted.
    
    SQL> INSERT  INTO tab_1 VALUES (1, 'John');
    
    1 row created.
    
    SQL> INSERT  INTO tab_1 VALUES (2, 'Mick');
    
    1 row created.
    Finally, select again all records - pay attention, ROWIDs are no longer equal, although those records are equal to the original ones:
    Code:
    SQL> SELECT ROWID, empno, ename FROM tab_1 order by empno;
    
    ROWID                   EMPNO ENAME
    ------------------ ---------- --------------------
    AAAOOLAAEAAA1MVAAD          1 John
    AAAOOLAAEAAA1MVAAE          2 Mick
    AAAOOLAAEAAA1MVAAC          3 Anna
    
    SQL> SELECT ROWID, empno, ename FROM tab_2 order by empno;
    
    ROWID                   EMPNO ENAME
    ------------------ ---------- --------------------
    AAAOOMAAEAAA1RFAAA          1 John
    AAAOOMAAEAAA1RFAAB          2 Mick
    AAAOOMAAEAAA1RFAAC          3 Anna
    Try to find a way to uniquely identify those clients - maybe not CLIENT_ID but "name + surname + day of birth + last salary" ... something. The easiest way to find column combination is perhaps trying to create a unique index on those columns - it will fail if columns aren't unique.

  5. #5
    Join Date
    Jun 2005
    Posts
    38
    We are not on the same page. I am not using the ROWID to compare the data from the tables, I'm using the ROWID for 1 to MANY relationships in the database file. So to import the data from one database file to the other, I need the ROWID from the one being imported so all the stuff in the OTHER tables also gets imported.

    CLIENT_COLOUR table for example from the Database has the columns CLIENT_ROWID & COLOUR

    Yes I know I can make a very long SQL statement comparing all 50 columns, and then getting ROWID from that but I was hoping there was a trick where I wouldn't have to.

Posting Permissions

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