Insert Into with Select

[Ctal]

I'm having a problem with a SQL statment. I've got two tables:

Table Stage:

Location Date Sales Exp Tax

NewYork 1/1/01 100.50 5.75 11.25

and so on with about 20 account columns.

I want to move this data into another table like this:

Table PlanData:

Location Date Account Amount

NewYork 1/1/01 Sales 100.50
NewYork 1/1//01 Exp 5.75
NewYork 1/1/01 Tax 11.25

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.

[Erland Sommarskog]

Ctal (bo********@yah oo.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_IDENTIFI ER 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, es****@sommarsk og.se

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

[Chandra]

Hi,
the query that you have written:
INSERT INTO PlanData (Location, Date, Account, Amount)
SELECT Location, Date, "Sales" AS Account, Sales
FROM Stage

is same as

INSERT INTO PlanData (Location, Date, Account, Amount)
SELECT Location, Date, Sales AS Account, Sales
FROM Stage

thats the reason, why the value of Sales column is getting inserted. If
you want the "Sales" to be inserted, then u need to put it as an hard
coded string.

INSERT INTO PlanData (Location, Date, Account, Amount)
SELECT Location, Date, 'Sales' AS Account, Sales
FROM Stage
and this will give you the results that you expected.

Please let me know if this answers the question

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***