April 13, 2006 at 12:07 pm
Would someone please guide me in the syntax for this. As you can hopefully see from my attempt I am trying to get distinct values from two columns in Old_Table and create a New_Table with one column.
Thanks.
SELECT DISTINCT Column1 AS Name
INTO New_Table
FROM Old_Table
UNION ALL
SELECT DISTINCT Column2 AS Name
INTO New_Table
FROM Old_Table
April 13, 2006 at 12:20 pm
In what way is this not working?
If the problem is that it's storing values that exist in both columns, try this:
SELECT DISTINCT t.[Name]
INTO New_Table
FROM (SELECT Column1 AS [Name]
FROM Old_Table
UNION ALL
SELECT Column2 AS [Name]
FROM Old_Table) t
April 13, 2006 at 12:47 pm
Jeff,
If a given Name exists in Column1 and Column2 try dropping the ALL from the UNION. Without the ALL sql will remove the duplicates for you.
Mike
April 13, 2006 at 2:30 pm
You need to remove the INTO from the second select in the UNION.
You can:
select into newtable
Select columns from table1 ....
union
select select columns from table2
jg
April 14, 2006 at 2:26 am
INTO : only for first select
UNION : If distinct is needed, do not use UNION ALL, but use UNION
Correct syntax is :
SELECT DISTINCT Column1 AS Name
INTO New_Table
FROM Old_Table
UNION
SELECT DISTINCT Column2 AS Name
FROM Old_Table
April 14, 2006 at 8:06 am
Thanks Bert that was exactly how I ended up.
Thanks everyone else for the replies. After the first reply as soon I I saw the single 'INTO' duhhh
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply