Could you explain and give a workaround on what is causing an UNION ALL to consider an original char(8) column by using MSSQLServer?
Hi Experts
Could you explain and give a workaround on what is causing an UNION ALL to consider an original char(8) column from the constituent tables that had be uncorrectly considered as an int column at the resultant table, by using MSSQLServer?
Accordingly to:
Thanks in advance
Could you explain and give a workaround on what is causing an UNION ALL to consider an original char(8) column from the constituent tables that had be uncorrectly considered as an int column at the resultant table, by using MSSQLServer?
Accordingly to:
SELECT *
INTO TargetTable
FROM
(
SELECT * FROM dbo.LOG_LOGRADOURO_AC_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_AL_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_AM_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_AP_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_BA_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_CE_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_DF_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_ES_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_MA_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_MG_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_MS_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_MT_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_PA_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_PB_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_PE_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_PI_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_PR_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_RJ_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_RN_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_RO_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_RR_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_RS_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_SC_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_SE_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_SP_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_TO_OK
UNION ALL
SELECT * FROM dbo.LOG_LOGRADOURO_GO_OK
) aThanks in advance
ste5an
A UNION determines the result data types from the first SELECT statement only. Thus it gets the types from LOG_LOGRADOURO_AC_OK, which is missing in your screenshot
SELECT INTO Query will generate the meta data of the column based on the data available in that column.
You might have all the data in that column CEP as integer. You need to explicitly indicate data type in queries or Create the TargetTable with Required Data type and use INSERT INTO rather than SELECT INTO.
You might have all the data in that column CEP as integer. You need to explicitly indicate data type in queries or Create the TargetTable with Required Data type and use INSERT INTO rather than SELECT INTO.
DROP TABLE IF EXISTS Test2
SELECT
*
INTO Test2
FROM
(
SELECT
CAST(Con_ID AS VARCHAR(100)) AS Con_ID
,Name
,City
,State
FROM Tb_Consumer_2
UNION ALL
SELECT
CAST(Con_ID AS VARCHAR(100)) AS Con_ID
,Name
,City
,State
FROM Tb_Consumer_1
) AS A
ASKER
Hi
All the tables have the correct dimension char(8) that must to be used since a zipcode like '02206000' is a valid one.
The 1st table
Just after the UNION this column is incorrectly converted to int.
I adjusted the question that was wrong formulared, sorry!
All the tables have the correct dimension char(8) that must to be used since a zipcode like '02206000' is a valid one.
The 1st table
Just after the UNION this column is incorrectly converted to int.
I adjusted the question that was wrong formulared, sorry!
Post the query plan, probably parallelization kicks in.
but on the other hand, just create the table explicitly.
but on the other hand, just create the table explicitly.
ASKER
Unfortunatelly execution plan is not allowed to be obtained at my case:
SHOWPLAN permission denied in database 'XXXXXX'.
SHOWPLAN permission denied in database 'XXXXXX'.
Aren't you in a different database?
Couldn't the TargetTable be some old one? What will be the structure when you SELECT * INTO SomeOtherName FROM ...?
Also try to reduce UNIONs to 2 tables only.
Couldn't the TargetTable be some old one? What will be the structure when you SELECT * INTO SomeOtherName FROM ...?
Also try to reduce UNIONs to 2 tables only.
ASKER
I really had recreated at the correct DB ...
I tried to reproduce it on my SQL Server and it works correctly:
USE [testDB1]
GO
IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = 'test1')
CREATE TABLE test1(Id CHAR(8))
GO
IF (SELECT Count(*) FROM test1)=0
INSERT INTO test1 VALUES('012345');
GO
SELECT * FROM test1 -- checking
GO
IF NOT EXISTS(SELECT * FROM sys.tables WHERE name='test2new')
SELECT * INTO test2new FROM (
SELECT Id FROM test1
UNION ALL
SELECT Id FROM test1
) a
GO
SELECT * FROM test2new
GO
test2new:
I have: Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64) Feb 9 2021 12:08:50 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 19042: ) (Hypervisor)
USE [testDB1]
GO
IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = 'test1')
CREATE TABLE test1(Id CHAR(8))
GO
IF (SELECT Count(*) FROM test1)=0
INSERT INTO test1 VALUES('012345');
GO
SELECT * FROM test1 -- checking
GO
IF NOT EXISTS(SELECT * FROM sys.tables WHERE name='test2new')
SELECT * INTO test2new FROM (
SELECT Id FROM test1
UNION ALL
SELECT Id FROM test1
) a
GO
SELECT * FROM test2new
GO
test2new:
I have: Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64) Feb 9 2021 12:08:50 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 19042: ) (Hypervisor)
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Hi
Just to clear my picture above.
Firstly at dbo.TargetTable_ANTERIOR one of the tables at the UNION really had an int dimension.
After running your query, another column remains...
I'm going to test it as soon as I could...
Yes! that worked out...
Just to clear my picture above.
Firstly at dbo.TargetTable_ANTERIOR one of the tables at the UNION really had an int dimension.
After running your query, another column remains...
I'm going to test it as soon as I could...
Yes! that worked out...
ASKER
Pcelba
Thank you for another solution!!!
Thank you for another solution!!!