update script

Question

Post reply

update script

riya_dave

Hall of Fame

Points: 3106
More actions
July 23, 2013 at 7:41 am

#275561

hi
i need to write script like this
BEGIN TRY
--------------------------------------------------------------------
-- Add New columns into product
--------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N' [product]]') AND type in (N'U'))
BEGIN
----------------------------------------------------------------
-- Add New columns into product
--------------------------------------------------------------------
BEGIN TRANSACTION
ALTER TABLE product
ADD Type varchar(25) NULL
ALTER TABLE product
ADD ToDisplay bit NULL
--BEGIN TRANSACTION
INSERT INTO product VALUES
(6,'Not Started',NULL,NULL,0,'Clinical',0)
INSERT INTO product VALUES
(7,'In Progress',NULL,NULL,0,'Clinical',1)
INSERT INTO product VALUES
(8,'Complete - Resolved',NULL,NULL,0,'Clinical',1)
INSERT INTO product VALUES
(9,'Complete - Unresolved',NULL,NULL,0,'Clinical',1)
INSERT INTO product VALUES
(10,'Closed - Member/Client Declined.',NULL,NULL,0,'Clinical',1)
-------------------------------------------------------------------
------------- Update values for Type and Display
-------------------------------------------------------------------------
UPDATE product
SET Type = 'HA',ToDisplay =1 where ID IN(1,2,3,4,5)
ALTER TABLE product
ALTER COLUMN TYPE VARCHAR(25) NOT NULL
alter table product
alter column ToDisplay bit not null
commit trnasaction
END
ELSE
BEGIN
PRINT ' product table does not exists'
END
END TRY
--------------------------------------------------------------------
-- Error Handling for all logic
--------------------------------------------------------------------
BEGIN CATCH
END CATCH
giving me error invalid column name 'type'
invalid column name 'Todisplay'
is there any way i can do all this in 1 script?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

TYPE is a keyword, so you'll need to put it in brackets:

ALTER TABLE product

ALTER COLUMN [TYPE] VARCHAR(25) NOT NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

riya_dave Hall of Fame Points: 3106 More actions · Answer 2

thats not a problem.

i am getting error at update statement

Lowell SSC Guru Points: 323518 More actions · Answer 3

well, i don't have a products table, to alter, and it was not included in your sample script, so here's mny best guesses:

you don't have GO statements after your ALTER TABLE statements. put one after your ALTER>

it looks like the table might have had, say 4 columns, and you added two columns, but then inserted into the table without explicitly naming columns; i'd think the inserts would fail.

regardless of where the [TYPE] column is being used, whether in the ALTER, INSERT, or SELECT, because it's a reserved word, it should be quoted [TYPE]

ALTER TABLE product ADD [Type] varchar(25) NULL;

ALTER TABLE product ADD ToDisplay bit NULL;

GO

INSERT INTO product(ColumnList)

--do more stuff

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

riya_dave (7/23/2013)
thats not a problem.
i am getting error at update statement

can you please post the actual error...

WHile the error you post in your post is about invalid column name type...

As it is reserve word so you have to put in brackets like [Type]

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

riya_dave Hall of Fame Points: 3106 More actions · Answer 5

if i put go , it gives me error ,

i have everything in begin and commit transaction

do i need to refresh manually table after i add column and then i can update table

Lowell SSC Guru Points: 323518 More actions · Answer 6

well, you are mixing up DML and DDL, and they should be completely seperate steps...first alter the tables if needed, then do your transaction.

something like this, and note i explicitly put a placeholder for the column names to be inserted into products; that's a best practice.

--------------------------------------------------------------------

-- Add New columns into product

--------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'product') AND TYPE IN (N'U'))

BEGIN

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE OBJECT_NAME(OBJECT_ID) = 'product' AND name = 'Type')

ALTER TABLE product ADD TYPE VARCHAR(25) NULL;

IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE OBJECT_NAME(OBJECT_ID) = 'product' AND name = 'ToDisplay ')

ALTER TABLE product ADD ToDisplay BIT NULL;

END;

GO

BEGIN TRY

BEGIN TRANSACTION

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'product') AND TYPE IN (N'U'))

BEGIN