SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
CREATE OR REPLACE PROCEDURE `sincere-apex-416007.cap_project.compare_tables` ( table1_name STRING, table2_name STRING, columns_to_exclude ARRAY<STRING> ) OPTIONS (strict_mode = false) BEGIN DECLARE table1_checksum STRING DEFAULT ''; DECLARE table2_checksum STRING DEFAULT ''; DECLARE columns_list STRING DEFAULT ''; DECLARE columns_to_compare ARRAY<STRING> DEFAULT []; DECLARE failure_reason_query STRING DEFAULT ''; DECLARE columns_filtered ARRAY<STRING> DEFAULT []; CREATE TEMP FUNCTION ArrayToString (arr ARRAY<STRING>) RETURNS STRING LANGUAGE js AS """ return arr.join(', '); """; CREATE TEMP FUNCTION GenerateChecksumExpression (columns ARRAY<STRING>) RETURNS STRING LANGUAGE js AS """ return columns.map(col => `MD5(IFNULL(CAST(${col} AS STRING), " NULL "))`).join(' || '); """; SET columns_to_compare = ( SELECT ARRAY_AGG(column_name) FROM ( SELECT column_name FROM `sincere-apex-416007.cap_project.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = table1_name UNION DISTINCT SELECT column_name FROM `sincere-apex-416007.cap_project.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = table2_name ) ); SET columns_to_compare = ARRAY( SELECT col FROM UNNEST(columns_to_compare) AS col WHERE NOT col IN UNNEST(columns_to_exclude) ); IF ARRAY_LENGTH(columns_to_compare) > 0 THEN SET columns_filtered = ARRAY( SELECT col FROM UNNEST(columns_to_compare) AS col ); SET table1_checksum = GenerateChecksumExpression(columns_filtered); SET table2_checksum = GenerateChecksumExpression(columns_filtered); SET columns_list = ArrayToString(columns_filtered); ELSE SET table1_checksum = '1'; -- Dummy value to avoid null SET table2_checksum = '1'; -- Dummy value to avoid null SET columns_list = ''; -- Empty string for columns list END IF; IF ARRAY_LENGTH(columns_filtered) > 0 THEN SET failure_reason_query = ( SELECT STRING_AGG( 'SELECT CASE WHEN CAST(t1.' || col || ' AS STRING) != CAST(t2.' || col || ' AS STRING) THEN "Mismatch in ' || col || '" ELSE NULL END AS Reason', ' UNION ALL ' ) FROM UNNEST(columns_filtered) AS col ); ELSE SET failure_reason_query = 'SELECT NULL AS Reason'; END IF; -- Debugging outputs SELECT table1_checksum AS debug_table1_checksum, table2_checksum AS debug_table2_checksum, columns_list AS debug_columns_list, failure_reason_query AS debug_failure_reason_query; IF table1_checksum IS NOT NULL AND table2_checksum IS NOT NULL AND columns_list IS NOT NULL THEN EXECUTE IMMEDIATE """ WITH Table1 AS ( SELECT BusinessDate, LoadDate""" || (CASE WHEN columns_list != '' THEN ', ' || columns_list ELSE '' END) || """, """ || table1_checksum || """ AS Table1_Checksum FROM """ || table1_name || """ ), Table2 AS ( SELECT BusinessDate, LoadDate""" || (CASE WHEN columns_list != '' THEN ', ' || columns_list ELSE '' END) || """, """ || table2_checksum || """ AS Table2_Checksum FROM """ || table2_name || """ ), Comparison AS ( SELECT '""" || table1_name || """' AS Table1Name, '""" || table2_name || """' AS Table2Name, t1.BusinessDate, t1.LoadDate, CASE WHEN t1.Table1_Checksum = t2.Table2_Checksum THEN 'Pass' ELSE 'Fail' END AS ResultPass, CASE WHEN t1.Table1_Checksum != t2.Table2_Checksum THEN ( SELECT STRING_AGG(Reason, '; ') FROM (""" || failure_reason_query || """) WHERE Reason IS NOT NULL ) ELSE NULL END AS FailureReason FROM Table1 t1 JOIN Table2 t2 ON t1.BusinessDate = t2.BusinessDate ) SELECT Table1Name, Table2Name, BusinessDate, LoadDate, ResultPass, FailureReason FROM Comparison ORDER BY BusinessDate; """; ELSE -- Handle case where checksums or columns list is null RAISE USING MESSAGE = 'Checksum expressions or columns list cannot be NULL'; END IF; END;
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear