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?

Why do you have **multiple DB tags**? – Lalit Kumar B Jan 08 '15 at 09:39 — Lalit Kumar B, Jan 08 '15 at 09:39

score 3 · Answer 1 · edited May 23 '17 at 10:24

3

EXCEL method

No need of creating external tables and loading etc.

The simplest way would be -

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

UPDATE Some more short cuts -

Pure SQL method

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

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

For example,

select user from db_table
MINUS
select user from excel_table

edited May 23 '17 at 10:24

Community

1
1

answered Jan 08 '15 at 09:42

Lalit Kumar B

47,486
13
97
124

' Thanks. That is a good idea- upvoted. but I still would like to do it via a query. – user3165438 Jan 08 '15 at 09:43
I will tell you a small secret. You can directly create `INSERT` statements in your `EXCEL` sheet. All you need to do is, just create a table with `USER` column. And execute all the `insert` statements. And then a simple `MINUS` query to compare. Let me edit my answer and add this. – Lalit Kumar B Jan 08 '15 at 09:46
Thanks. Is it a virtual table? can I delete it easily after use? – user3165438 Jan 08 '15 at 10:01
you can just do `DROP TABLE table_name PURGE` when you are done with your analysis. – Lalit Kumar B Jan 08 '15 at 10:09

score 1 · Answer 2 · answered Jan 08 '15 at 09:32

1

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.

answered Jan 08 '15 at 09:32

Thanks, how can I delete the temporary table after use? – user3165438 Jan 08 '15 at 10:58
You can simply use "DROP TABLE ...". Note also that the temporary table is only visible for the current connection, it is automatically dropped if you close the connection. – Jan 08 '15 at 15:07

score 1 · Answer 3 · answered Jan 08 '15 at 09:36

1

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.

answered Jan 08 '15 at 09:36

adarsh hota

327
11
23

how can I delete the temporary table after use? – user3165438 Jan 08 '15 at 09:41
Drop temporary table: https://riptutorial.com/mysql/example/20332/drop-temporary-table – cbilliau Aug 19 '22 at 16:21

Abecee · Answer 4 · 2015-01-12T09:32:22.340

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.

Compare table content with external file- SQL

4 Answers4