469,579 Members | 1,051 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

Insert Into with Select

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.

Jul 23 '05 #1
2 3989
Ctal (bo********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

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 ***
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
20 posts views Thread by Mark Harrison | last post: by
6 posts views Thread by lenygold via DBMonster.com | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.