0

At the top of my query I have a few statements that drop tables if they exist, such as:

IF OBJECT_ID('dbo.vanguardsummary', 'U') IS NOT NULL 
DROP TABLE dbo.vanguardsummary;

Later on I drop a particular column from one of the newly dropped and recreated tables:

alter table vanguardsummary drop column SRDid_

But sometimes when I try to rerun the whole script, I get this error:

Msg 207, Level 16, State 1, Line 191

Invalid column name 'srdid_'.

Why is it throwing this error (on a line much farther down) for a table that I am dropping at the very top of the script?

When the whole script runs, it will run again just fine. This happens when the script query is interrupted and I try to fix it and run it again. I have to run the drop table statements by themselves in order to get the query to execute again, and then the entire query will execute. What is wrong with my syntax? Any extra advice is appreciated as I am just starting to learn SQL.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Baker Lee
  • 1
  • If you dropped the table (`vanguardsummary`) that `SRDid_` is found in at the start of the batch, why would you *not* expect the SQL to error? – Thom A Feb 26 '20 at 16:43
  • The vanguardsummary table is created during the course of the query. So I first drop the table if it exists, then create it, then drop a column from it. I know there should be a better way to do this and I'm all ears. – Baker Lee Feb 26 '20 at 16:48
  • You are saying that the whole thing runs fine, but if you happen to run portions of it, it doesn't work? Is this run inside a transaction? If it is, and it happens to be interrupted, it means whatever portion was run would be rolled back, so you would need to start over. – Julia Leder Feb 26 '20 at 16:50
  • 2
    So just leave it in the table - why does it need to be dropped? This sounds like a [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Another alternative is to simply divide your script into batches. Put the drop statements at the top in the first batch. Everything else follows in the second batch. – SMor Feb 26 '20 at 16:51
  • 1
    I think we need to full set here. I tried to [replicate the problem](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9e42cf46bf4d8e1e5c37ca823d60dc09), guessing what the problem was, but no luck. If you are recreating the table though, why create it with a column you're going to `DROP`? – Thom A Feb 26 '20 at 16:53
  • @smor I am dropping the column because it's redundant. I had to rename it to "SRDid_" in order to join with another table's SRDid and avoid having to list out every column in the select statement. I think you have the answer I was looking for - to seperate the script into batches. There are probably some basic concepts like this that I have missed – Baker Lee Feb 26 '20 at 17:01
  • 1
    @BakerLee And again - WHY do you need to drop it? Just ignore it - redundant or otherwise. Peering through the fog, you are likely trying to kludge around a problem that you create in your code. And no - you NEVER need to rename a column to join it . Your inexperience is likely driving you down the wrong path. – SMor Feb 26 '20 at 17:14
  • @smor I did this in order to use * for the join. I understand it's better practice not to do so, but I'm changing the other column names and adding different ones all the time, so it seemed more efficient. if selecting all columns my solution was to rename one of them. And to answer your other question, this is an output table so I didn't want the redundant column. – Baker Lee Feb 26 '20 at 18:52
  • You're getting the error because the table exists. The SQL parser sees that later on you're dropping the column, but the column is not in the table (because you already dropped it) Drop the table, then you can run it without error. You can also put the drop table part at the end of the script, so the table will not exist when the parser is doing it's thing. Alternatively, put an "Is exists" check on the drop column part. – the Ben B Feb 27 '20 at 15:24

0 Answers0