Posted by Erland Sommarskog on 10/01/70 11:22

Ctal (bob_kaleel@yahoo.com) writes:
> Here's my statement:
>
> INSERT INTO PlanData (Location, Date, Account, Amount)
> SELECT Location, Date, "Sales" AS Account, Sales
> FROM Stage
>
> This is executing but I'm getting an integer into Column 3 in my
> PlanData table and the value in the amount table, like this:
>
> NewYork 1/1/01 100 100.50
>
> Can anyone help? I'm a newb so I'm sure it's a stupid error on my
> part.

You bet. :-)

In T-SQL there are two string delimiters ' and ". However, " only works
as a string delimiter if the setting QUOTED_IDENTIFIER is off, and in
most contexts it's on by default these days. When this setting is on,
" is not a string delimiter but an identifier delimiter, which permits
you to have tables called things like "Order Details". Thus "Sales" refer
to the column Sales, not the string Sales.

Replace "Sales" with 'Sales' and it works better.

Another note: I don't know if you have already thought of it, but it
is a very good idea to add a constraint for the Account column that
restricts which values that are permitted, so that you don't suddenly
have both Exp and Expo in the table. This consraint can either be a
CHECK constraints that lists the permitted values, or a foreign-key
constraint that refers to a table that defines the permitted values.
My preference would be for the latter.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]