Compare table content with external file- SQL

Question

I have an Excel file with users IDs, called users Excel.

In the Database I have a users table: this table holds the data from the users excel file and from another file (administrators file).

I would like to check if I have a user in the users Excel which does not exist in the table.

Should I build a virtual table? how?

Answer 1

EXCEL method

No need of creating external tables and loading etc.

The simplest way would be -

1. SPOOL the table data to a file.
2. Copy the contents of this spooled file and your users Excel to a common file.
3. Just do a compare using VLOOKUP.

UPDATE Some more short cuts -

Pure SQL method

1. Generate the SQL queries using excel string addition operator – &.

Example -

="insert into customers values('" &B3 &"','" & C3 & "','"&D3&"');"

where B3, C3, D3 refer to the cells. See an example here Generate sql insert script from excel worksheet

2. Now, just create a table in your database with the required columns.
3. Copy and Execute the INSERT statements.
4. Compare the two tables using a MINUS query.

For example,

select user from db_table
MINUS
select user from excel_table

Answer 2

You can load your Excel file content into a temporary table and compare the content with the existing table.

I guess you are on a windows environment:

1- Export the content of the excel file to a simple text file (users.txt)

2- Create the temporary table: CREATE TEMPORARY TABLE users_tmp (user_id int) ENGINE = MEMORY;

3- Load the data file content to the tmp table: LOAD DATA LOCAL INFILE 'users.txt' INTO TABLE users_tmp LINES TERMINATED BY '\r\n';

Then compare users to users_tmp content.

Answer 3

Yes, you can try by creating virtual/temporary table having syntax CREATE TEMPORARY TABLE temp_users(user_id INT).

Push the users Excel data into it.

Then compare users table with temp_users by writing query.

Answer 4

You listed several databases and no specific version… In any case, you will need to generate appropriate SELECTs - either in Excel as suggested by Lalit Kumar B, using some text processor, or even (partly) in your database.

For MySQL, you'd work towards the statements to be used in the subselect resulting in "T".

SELECT T.firstName, T.lastName FROM
  (SELECT 'Afirst' firstName, 'Alast' lastName UNION ALL
  SELECT 'Dfirst' firstName, 'Dlast') T
LEFT JOIN Users U
  ON U.firstName = T.firstName
  AND U.lastName = T.lastName
WHERE U.firstName IS NULL AND U.lastName IS NULL;

SQL Fiddle

In Oracle you use DUAL to construct the statements:

SELECT T.firstName, T.lastName FROM
  (SELECT 'Afirst' firstName, 'Alast' lastName FROM DUAL UNION ALL
  SELECT 'Dfirst' firstName, 'Dlast' FROM DUAL) T
LEFT JOIN Users U
  ON U.firstName = T.firstName
  AND U.lastName = T.lastName
WHERE U.firstName IS NULL AND U.lastName IS NULL
;

and in more recent versions, you have the option to factor:

WITH
T (firstName, lastName) AS (
  SELECT 'Afirst' firstName, 'Alast' lastName FROM DUAL UNION ALL
  SELECT 'Dfirst' firstName, 'Dlast' FROM DUAL
)
SELECT T.firstName, T.lastName
FROM T
LEFT JOIN Users U
  ON U.firstName = T.firstName
  AND U.lastName = T.lastName
WHERE U.firstName IS NULL AND U.lastName IS NULL
;

which would even allow to split the columns in the database (If you have more than two or three columns in your Excel to compare with the Users table, you'd probably use one of the more elaborate CSV splitting approaches - if your Oracle version permits.):

WITH
T (string) AS (
  SELECT 'Afirst,Alast' FROM DUAL UNION ALL
  SELECT 'Dfirst,Dlast' FROM DUAL
),
TUser (firstName, lastName) AS (
  SELECT
    SUBSTR(string, 1, INSTR(string, ',') - 1)
    , SUBSTR(string, INSTR(string, ',') + 1, LENGTH(string))
  FROM T
)
SELECT T.firstName, T.lastName
FROM TUser T
LEFT JOIN Users U
  ON U.firstName = T.firstName
  AND U.lastName = T.lastName
WHERE U.firstName IS NULL AND U.lastName IS NULL
;

SQL Fiddle

And if some database is not as relaxed as MySQL (requiring no table in the SELECT), and has no equivalent for Oracle's "DUAL", you can still create such a one column / one row table and proceed.

Please comment, if and as adjustment / further detail is required.