September 30, 2011 at 3:37 am
Hello,
I've created a multiple unions between 4 tables to combines the results of these select statement. Can someone tell me the syntax/or example of how to insert the results from the union into a new table. I've looked on the internet and some of the syntax used does not seem to work.
Here is my attempt so far
CREATE TABLE mytable (ItemNumber nvarchar(50),
SalesRegionLevel4Code nvarchar(50),
SalesRegionLevel4Description nvarchar(50),
CustomerTypeLevel1Code nvarchar(50),
CustomerTypeLevel2Description nvarchar(50),
RowRecordedDateTime datetime,
Name nvarchar(50))
GO
INSERT INTO mytable
SELECT T.ItemNumber
, T.SalesRegionLevel4Code
, T.SalesRegionLevel4Description
, T.CustomerTypeLevel1Code
, T.CustomerTypeLevel2Description
, T.RowRecordedDateTime
, CASE T.Name when 'Null'
THEN ''
ELSE '1'
END AS Name
FROM TEMP1 T
union all
SELECT T2.ItemNumber
, T2.SalesRegionLevel4Code
, T2.SalesRegionLevel4Description
, T2.CustomerTypeLevel1Code
, T2.CustomerTypeLevel2Description
, T2.RowRecordedDateTime
, CASE T2.Name when 'Null'
THEN ''
ELSE '2'
END AS Name
FROM TEMP2 T2
union all
SELECT T3.ItemNumber
, T3.SalesRegionLevel4Code
, T3.SalesRegionLevel4Description
, T3.CustomerTypeLevel1Code
, T3.CustomerTypeLevel2Description
, T3.RowRecordedDateTime
, CASE T3.Name when 'Null'
THEN ''
ELSE '3'
END AS Name
FROM TEMP3 T3
union all
SELECT T4.ItemNumber
, T4.SalesRegionLevel4Code
, T4.SalesRegionLevel4Description
, T4.CustomerTypeLevel1Code
, T4.CustomerTypeLevel2Description
, T4.RowRecordedDateTime
, CASE T4.Name when 'Null'
THEN ''
ELSE '4'
END AS Name
FROM TEMP4 T4
Thanks in advance
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
September 30, 2011 at 4:03 am
Jnrstevej (9/30/2011)
some of the syntax used does not seem to work.
Steve
Please will you be a bit more specific. Error messages? Unexpected results?
Thanks
John
September 30, 2011 at 5:18 am
Hi John
Thanks for your reply I've managed to get it working now. The error i got before were invalid object and syntax near ')' in my older version i was missing a bracket which i have now included.
CREATE TABLE Test(ItemNumber nvarchar(50),
SalesRegionLevel4Code nvarchar(50),
SalesRegionLevel4Description nvarchar(50),
CustomerTypeLevel1Code nvarchar(50),
CustomerTypeLevel2Description nvarchar(50),
RowRecordedDateTime datetime,
Name nvarchar(50)) -- originally missing a bracket in my older version
GO
INSERT INTO Test
SELECT T.ItemNumber
, T.SalesRegionLevel4Code
, T.SalesRegionLevel4Description
, T.CustomerTypeLevel1Code
, T.CustomerTypeLevel2Description
, T.RowRecordedDateTime
, CASE T.Name when 'Null'
THEN ''
ELSE '1'
END AS Name
FROM TEMP1 T
union all
SELECT T2.ItemNumber
, T2.SalesRegionLevel4Code
, T2.SalesRegionLevel4Description
, T2.CustomerTypeLevel1Code
, T2.CustomerTypeLevel2Description
, T2.RowRecordedDateTime
, CASE T2.Name when 'Null'
THEN ''
ELSE '2'
END AS Name
FROM TEMP2 T2
union all
SELECT T3.ItemNumber
, T3.SalesRegionLevel4Code
, T3.SalesRegionLevel4Description
, T3.CustomerTypeLevel1Code
, T3.CustomerTypeLevel2Description
, T3.RowRecordedDateTime
, CASE T3.Name when 'Null'
THEN ''
ELSE '3'
END AS Name
FROM TEMP3 T3
union all
SELECT T4.ItemNumber
, T4.SalesRegionLevel4Code
, T4.SalesRegionLevel4Description
, T4.CustomerTypeLevel1Code
, T4.CustomerTypeLevel2Description
, T4.RowRecordedDateTime
, CASE T4.Name when 'Null'
THEN ''
ELSE '4'
END AS Name
FROM TEMP4 T4
__________________________________________________________________________________
Steve J
Jnr Developer
BSc(Hon)
September 30, 2011 at 5:28 am
...in my older version i was missing a bracket which i have now included.
Oops! 😛
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply