Why does this code only work if executed with other statements?
-
I'm trying to run a check for column existence prior to updating a column. My sample code is as follows:
-- DROP AND CREATE Source table
IF
OBJECT_ID(N'dbo.DoesNotIncludeColumn') IS NOT NULL
AND EXISTS (SELECT 1 FROM sys.objects WHERE name = 'DoesNotIncludeColumn' and TYPE = 'U' )
DROP TABLE dbo.DoesNotIncludeColumn
GO
CREATE TABLE DoesNotIncludeColumn
(
Column1 INT PRIMARY KEY NOT NULL
,Column2 nvarchar(4000) NULL
)
-- DROP AND CREATE Destination table
IF
OBJECT_ID(N'dbo.DestinationTable') IS NOT NULL
AND EXISTS (SELECT 1 FROM sys.objects WHERE name = 'DestinationTable' and TYPE = 'U' )
DROP TABLE dbo.DestinationTable
GO
CREATE TABLE DestinationTable
(
SourceColumn1 INT PRIMARY KEY NOT NULL
,SourceColumn2 nvarchar(4000) NULL
)
INSERT INTO DoesNotIncludeColumn
(Column1
,Column2
)
VALUES (
254
,'Source table text string'
)
INSERT INTO DestinationTable
(
SourceColumn1
,SourceColumn2
)
VALUES
(
254
,'Destination table text string'
)
IF EXISTS
(
SELECT 1
FROM sys.objects objz
INNER JOIN sys.columns colz
ON objz.object_id = colz.object_id
WHERE SCHEMA_NAME(objz.schema_id) = 'dbo'
AND objz.name = N'DoesNotIncludeColumn'
AND colz.name = N'ThisColumnDoesNotExist'
)
BEGIN
UPDATE e
SET
e.SourceColumn2 = t.ThisColumnDoesNotExist
FROM DoesNotIncludeColumn t
JOIN DestinationTable e
ON t.Column1 = e.SourceColumn1
END
As I've written it, the code works as expected.
However, if I separately run the last part (from 'If Exists down) on its own, I get a message stating
Msg 207, Level 16, State 1, Line 14
Invalid column name 'ThisColumnDoesNotExist'.
Is there something I can do to run the last bit separately?
-
You can to do it dynamically
EXEC('UPDATE e
SET
e.SourceColumn2 = t.ThisColumnDoesNotExist
FROM DoesNotIncludeColumn t
JOIN DestinationTable e
ON t.Column1 = e.SourceColumn1')
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Wow, nice one Mark, got it working now.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply