March 3, 2022 at 7:16 am
Hi Everyone,
I have a table A that has 205 columns and table B that has 160 columns. Some of the columns in two tables matches but there are differnces. is there a simple way to do a union of two tables and get null for column that doesnt belong in either table. For fewer numbr of columns this is an easy task but but tables with more than 100 columns can be manual. I am trying to find a better way to do this.
select col1, col2, col3............... from table1
union
select col1, col2, col3, col5 .... from table2
I know column order has to be the same in 2 queries. But I want to find a automated way to write this query using sql.
Any good ideas to do this?
Thanks.
March 3, 2022 at 9:24 am
You could find all of the columns sys.columns, but there is no way to know which ones are alligned.
So, as far as I know, you have to do it manually.
select col1, col2, col3, col5=NULL,............... from table1
union
select col1, col2, col3, col5 .... from table2
On the other hand, I suppose you could build a table that has 4 columns
Table-A-Name, Table-B-Name, Table-A-ColumnName, Table-B-ColumnName
The use dynamic SQL to use the new table to build the SQL statements, adding NULL as needed.
March 3, 2022 at 1:51 pm
Unfortunately Automated\Custom aren’t in the same realm. You can use Scrip Table as in SSMS. Then order you columns in the order you want. Combine them in one query with UNION. In the Select use "Null as ColName” on columns that don’t match. Far from automated but will get what you want.
March 3, 2022 at 2:03 pm
Also, be aware that UNION will be much slower that UNION ALL, as it will only return a DISTINCT result, which requires an additional sort operation
March 3, 2022 at 3:35 pm
That's relatively easy to do:
DECLARE @table1 sysname
DECLARE @table2 sysname
SET @table1 = 'dbo.table1' --<<--!!CHANGE TABLES NAMES HERE!!
SET @table2 = 'dbo.table2' --<<--!!CHANGE TABLES NAMES HERE!!
IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
DROP TABLE #sql;
CREATE TABLE #sql ( id int IDENTITY(1, 1) NOT NULL PRIMARY KEY, sql varchar(8000) NULL )
INSERT INTO #sql
SELECT 'SELECT '
INSERT INTO #sql
SELECT SPACE(4) + ISNULL(c1.name, 'NULL AS ' + c2.name) + ', '
FROM (
SELECT *
FROM sys.columns c1
WHERE c1.object_id = OBJECT_ID(@table1)
) AS c1
FULL OUTER JOIN (
SELECT *
FROM sys.columns c2
WHERE c2.object_id = OBJECT_ID(@table2)
) AS c2 ON c2.name = c1.name
UPDATE #sql
SET sql = STUFF(sql, LEN(sql), 1, '')
WHERE id = (SELECT MAX(id) FROM #sql)
INSERT INTO #sql
SELECT 'FROM ' + @table1 + ' '
INSERT INTO #sql
SELECT 'UNION ALL '
INSERT INTO #sql
SELECT 'SELECT '
INSERT INTO #sql
SELECT SPACE(4) + ISNULL(c2.name, 'NULL AS ' + c1.name) + ','
FROM (
SELECT *
FROM sys.columns c1
WHERE c1.object_id = OBJECT_ID('dbo.NonBusinessDate_Controls')
) AS c1
FULL OUTER JOIN (
SELECT *
FROM sys.columns c2
WHERE c2.object_id = OBJECT_ID('dbo.NonBusinessDates')
) AS c2 ON c2.name = c1.name
UPDATE #sql
SET sql = STUFF(sql, LEN(sql), 1, '')
WHERE id = (SELECT MAX(id) FROM #sql)
INSERT INTO #sql
SELECT 'FROM ' + @table2 + ' '
SELECT *
FROM #sql
ORDER BY id
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 7, 2022 at 4:21 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply