November 25, 2009 at 12:16 pm
Dear Greater SQL Intellect,
After searching the forum, and other places online...I'm still not sure why my UNION ALL statements aren't working.
Here's what I have:
CREATE TABLE #Production
(
Item TEXT NULL,
DescriptionTEXT NULL,
Price SMALLMONEYNULL
)
GO
INSERT INTO #Production
(Item, Description, Price)
SELECT Apple, Fruit, 1 UNION ALL
SELECT Orange, Fruit, 2 UNION ALL
SELECT Pear, Fruit, 3
GO
Creating the table is no problem. However, after running the INSERT INTO statement I get:
msg 207, Invalid column name 'Apple'
msg 207, Invalid column name 'Fruit'
msg 207, Invalid column name 'Orange'
and so on.
However, if I construct the INSERT INTO statement this way:
INSERT INTO #Production
(Item, Description, Price)
SELECT 'Apple', 'Fruit', '1' UNION ALL
SELECT 'Orange',' Fruit', '2' UNION ALL
SELECT 'Pear', 'Fruit', '3'
GO
It works.
I've read that data types may be the reason for this error, but the data types look good to me.
Missing something obvious...pls. enlighten.
Regards,
Mike G.
Seattle, WA
November 25, 2009 at 12:30 pm
mgodinez (11/25/2009)
INSERT INTO #Production(Item, Description, Price)
SELECT Apple, Fruit, 1 UNION ALL
SELECT Orange, Fruit, 2 UNION ALL
SELECT Pear, Fruit, 3
GO
Creating the table is no problem. However, after running the INSERT INTO statement I get:
msg 207, Invalid column name 'Apple'
msg 207, Invalid column name 'Fruit'
msg 207, Invalid column name 'Orange'
and so on.
Because there are no quotes around the strings. Without quotes SQL assumes that they are column names, and when it looks, they don't match columns for anything available. By wrapping single quotes around, you're telling SQL they are string literals, not objects or columns.
INSERT INTO #Production
(Item, Description, Price)
SELECT 'Apple', 'Fruit', '1' UNION ALL
SELECT 'Orange',' Fruit', '2' UNION ALL
SELECT 'Pear', 'Fruit', '3'
GO
You don't need quotes around the numbers, unless they are supposed to be strings rather than integers. You do need quotes around string values.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 25, 2009 at 12:37 pm
"You don't need quotes around the numbers, unless they are supposed to be strings rather than integers. You do need quotes around string values."
************************
and this is the answer to my inquiry. string values need to be defined w/ quotes...I see now.
Thanks, Gail!
Regards,
Mike G.
Seattle, WA
November 27, 2009 at 10:55 am
I'm curious why you used the data type text and not varchar(255) or a smaller value? Is there a need for very large character data and that's why text.
CREATE TABLE #Production
(
Item TEXT NULL,
Description TEXT NULL,
Price SMALLMONEY NULL
)
CREATE TABLE #Production
(
Item varchar(255) NULL,
Description varchar(255) NULL,
Price SMALLMONEY NULL
)
November 30, 2009 at 7:16 am
I do wonder how you came to use the UNION ALL statement when looking at inserts in this case. What drove that thought of enquiry, as such statements are not often found so readily (except in example texts)
November 30, 2009 at 8:27 am
SW_Lindsay (11/27/2009)
I'm curious why you used the data type text and not varchar(255) or a smaller value? Is there a need for very large character data and that's why text.CREATE TABLE #Production
(
Item TEXT NULL,
Description TEXT NULL,
Price SMALLMONEY NULL
)
CREATE TABLE #Production
(
Item varchar(255) NULL,
Description varchar(255) NULL,
Price SMALLMONEY NULL
)
hello,
yes, i was going to use varchar data types, but wanted to simplify things as much as possible during the troubleshooting process. the data types i was trying to use (char, varchar) were good...my not indicating string values w/ the union statements was the problem.
Logicalman1998 (11/30/2009)
I do wonder how you came to use the UNION ALL statement when looking at inserts in this case. What drove that thought of enquiry, as such statements are not often found so readily (except in example texts)
hello,
exactly...i usually just use:
insert into table (column1, column2, ...n)
values (value1, value2, ...n)
but after reading jeff moden's artcle: Jeff Moden's article: Cross Tabs and Pivots, Part 1 - http://www.sqlservercentral.com/articles/T-SQL/63681/
i started using union all statements to insert data to all rows from a single insert statement. it's cleaner for me...
thanks again to everyone for their replies. love the resources here...
regards,
mike g.
seattle, wa
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply