| Author |
Topic |
|
averyoo7
Starting Member
6 Posts |
 Posted - 2012-03-29 : 19:17:18
|
| In SQL Server 2005, I'm writing a script that tries to insert data into a table variable (and subsequently migrate it elsewhere) if a certain column still exists. However, if the column doesn't exist, I attempt to skip that code with an 'if' statement. The problem is that SQL Server gripes about my SELECT DISTINCT reference to the Phase column even if it doesn't have to execute it. I've tested my 'if' statement and it would truly skip this code during runtime if Phase doesn't exist. How can I get my script to nicely execute when the Phase column doesn't exist without getting errors?Error:------Msg 207, Level 16, State 1, Line 24Invalid column name 'Phase'.Code:-----if EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Sequence' AND COLUMN_NAME = 'Phase')BEGIN DECLARE @DataMove Table (Phase varchar(100)); INSERT INTO @DataMove SELECT DISTINCT ltrim(rtrim(Phase)) FROM [Sequence] WHERE Phase is not Null and ltrim(rtrim(Phase)) <> ''-- <<data migration>>END~Avery |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
averyoo7
Starting Member
6 Posts |
Posted - 2012-03-29 : 19:28:54
|
| Is there an easy way to fill @DataMove from a T-SQL statement without having to convert all my migration code to T-SQL as well? I'm using a cursor to do it, so I don't mind if @DataMove turns out to be empty.~Avery |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
averyoo7
Starting Member
6 Posts |
Posted - 2012-03-29 : 19:39:57
|
| Can I execute a T-SQL statement that will return and populate the table var @DataMove that is sitting outside of the scope of the T-SQL statement? I'm under the impression that if I put "INSERT INTO @DataMove... " into a T-SQL statement, then @DataMove ceases to exist when the T-SQL finishes execution and I won't be able to reference it for the rest of my code.~Avery |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
averyoo7
Starting Member
6 Posts |
Posted - 2012-03-29 : 19:48:02
|
| Thanks.~Avery |
|
|
|
averyoo7
Starting Member
6 Posts |
Posted - 2012-03-29 : 21:57:24
|
| What I ended up with looks like a clean dynamic solution using a table var instead of a temp table and EXEC() instead of sp_executesql():if EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Sequence' AND COLUMN_NAME = 'Phase')BEGIN DECLARE @DataMove TABLE (Phase varchar(100)); SET @SQL = N'SELECT DISTINCT ltrim(rtrim(Phase)) FROM [Sequence] WHERE Phase is not Null and ltrim(rtrim(Phase)) <> '''' '; INSERT @DataMove (Phase) EXEC(@SQL);-- <<data migration>>END~Avery |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
averyoo7
Starting Member
6 Posts |
Posted - 2012-04-17 : 12:00:44
|
| Yep, it's working. And thanks for all your suggestions Tara. ...Clean--not because I didn't use temp tables or sp_executesql--just because it was done with only a few lines of code. I didn't need to use cursors or line after line of dynamic SQL, etc.~Avery |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Trying to avoid errors running a script twice