I have to write a script to update a database but im having problems doing it. The T-Sql i开发者_如何学Pythons
BEGIN TRY
BEGIN TRANSACTION
ALTER TABLE company
ADD RegistrationNumber nvarchar(50)
COMMIT
END TRY
BEGIN CATCH
IF(@@trancount > 0)
ROLLBACK TRANSACTION
END CATCH
update Company set RegistrationNumber = ''
But im getting the error
Invalid column name 'RegistrationNumber'.
However when I run the first bit then the second bit I dont get a problem....how do I get it all into one script?
When the parser analyses your query the RegistrationNumber
field does not exist (yet) and so it reports the error; it would take a much more complex parser to recognise you are creating it earlier in the query.
Separate the two queries with GO
and they will be parsed/executed individually, which should avoid the error.
BEGIN TRY
BEGIN TRANSACTION
ALTER TABLE company
ADD RegistrationNumber nvarchar(50)
COMMIT
END TRY
BEGIN CATCH
IF(@@trancount > 0)
ROLLBACK TRANSACTION
END CATCH
GO -- << Add this
UPDATE Company SET RegistrationNumber = ''
EDIT: If you want both steps to be performed in one go why not just use a default value for the new column?
BEGIN TRY
BEGIN TRANSACTION
ALTER TABLE company
ADD RegistrationNumber nvarchar(50) DEFAULT ''
COMMIT
END TRY
BEGIN CATCH
IF(@@trancount > 0)
ROLLBACK TRANSACTION
END CATCH
精彩评论