Insert Statement Fails

All,

Trying to format some data before I drop it into a grid. I have this in a stored proc but it fails

CREATE TABLE dbo.tmpSummary (
AE NVARCHAR(50)
, PRODUCT_LINE NVARCHAR(20)
, ANNUAL_REV NUMERIC (9)
, [GRWTH/ACQ] NUMERIC (9)
, RETENTION NUMERIC (9)
, CATEGORY NVARCHAR(20)

)

INSERT INTO dbo.tmpSummary (
[AE]
, [PRODUCT_LINE]
, [ANNUAL_REV]
, [GRWTH/ACQ]
, RETENTION
, CATEGORY
)
SELECT
A.AE
, A.PRODUCT_LINE
, A.ANNUAL_REV
, A.[GRWTH/ACQ]
, A.RETENTION
, B.PRODUCT_CATEGORY AS CATEGORY

FROM
tmpSummary A RIGHT OUTER JOIN PRODUCT B
On A.PRODUCT_LINE=B.PRODUCT_CATEGORY

I keep getting an error "Invalid Column name CATEGORY" Anyone know why? Thanks

Never mind. Maybe if I learn to read i could see that i am trying to insert data BACK into the same table. It should have been something else.

|||

Only reason why you would get that error is that you don't have a column in your table named CATEGORY. Check your table defenition again to make sure you have the spelling of the column name correct. I know that gets me alot

Insert or Change Identity

How can I insert or drop identity for a column from the query analyser?I don't think you can...

You can create a new column, insert the data from it, drop the column, add a new column with the old name, and move the data back...don't know if you can rename a coulmn..

Gotta look more closley at ALTER..

anyway, I was hacking around with this

USE Northwind
GO

CREATE TABLE myTable99 (Col1 int IDENTITY, Col2 Char(1))
GO

INSERT INTO myTable99 (Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
GO

ALTER TABLE myTable99 ALTER Column Col1 int
GO

INSERT INTO myTable99 (Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Well, I gotta take that back...you can change it in Enterprise manager...so you should be able to do an ALTER...just couldn't see it...|||Originally posted by Brett Kaiser
Well, I gotta take that back...you can change it in Enterprise manager...so you should be able to do an ALTER...just couldn't see it...

I know it is possible in Enterprise Manager but I like to do it through Query Analyser.But the question is how?|||You can add a new column which has the identity property.
You can drop the column.

Don't think you can remove the identity property though.
e-m will probably create a new table and copy the data - it tends to do that even if it doesn't need to.

As Brett says you can create a new column, copy the data and drop the old.