임시 테이블이 존재하는지 확인하고 존재하는지 삭제 한 후 임시 테이블을 생성합니다.

임시 테이블이 존재하는지 확인하고 존재하는지 삭제 한 후 임시 테이블을 생성합니다.

---

다음 코드를 사용하여 임시 테이블이 있는지 확인하고 테이블이 있는지 다시 생성하기 전에 삭제합니다. 열을 변경하지 않는 한 잘 작동합니다. 나중에 열을 추가하면 "잘못된 열"이라는 오류가 표시됩니다. 내가 뭘 잘못하고 있는지 알려주세요.

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
)

select company, stepid, fieldid from #Results

--Works fine to this point

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
    NewColumn            NVARCHAR(50)
)

select company, stepid, fieldid, NewColumn from #Results

--Does not work

---

오류를 재현 할 수 없습니다.

아마도 나는 문제를 이해하지 못하고 있습니다.

다음은 SQL Server 2005에서 잘 작동하며 두 번째 선택 결과에 추가 "foo"열이 표시됩니다.

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO
CREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT )
GO
select company, stepid, fieldid from #Results
GO
ALTER TABLE #Results ADD foo VARCHAR(50) NULL
GO
select company, stepid, fieldid, foo from #Results
GO
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO

---

설명은 순서대로 작성되어야합니다.

1. 테이블에 대한 문 변경
2. 가다
3. Select 문.

중간에 'GO'가 없으면 모든 것이 하나의 단일 스크립트로 간주되고 select 문이 열을 찾을 때 찾을 수 없습니다.

'GO'를 사용하면 'GO'까지의 스크립트 부분을 하나의 단일 배치로 간주하고 'GO'이후 쿼리에 들어가기 전에 실행됩니다.

---

dropping임시 테이블을 다시 생성하는 대신 truncate재사용 할 수 있습니다.

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    Truncate TABLE #Results
else
    CREATE TABLE #Results
    (
        Company             CHAR(3),
        StepId              TINYINT,
        FieldId             TINYINT,
    )

사용 중이 Sql Server 2016거나 Azure Sql Database아래 구문을 사용하여 임시 테이블을 삭제하고 다시 만듭니다. 여기에 더 많은 정보 MSDN

통사론

DROP TABLE [IF EXISTS] [database_name. [스키마 _ 이름]. | schema_name. ] table_name [, ... n]

질문:

DROP TABLE IF EXISTS tempdb.dbo.#Results
CREATE TABLE #Results
  (
   Company             CHAR(3),
   StepId              TINYINT,
   FieldId             TINYINT,
  )

---

문제는 실행을 배치로 분리하기 위해 중간에 GO 문을 추가해야한다는 것입니다. 두 번째 드롭 스크립트 즉 IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results, 단일 배치의 일부인 임시 테이블을 삭제하지 않았습니다. 아래 스크립트를 시도해 주시겠습니까?

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
)

GO

select company, stepid, fieldid from #Results

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE #Results

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
    NewColumn            NVARCHAR(50)
)

GO

select company, stepid, fieldid, NewColumn from #Results

---

This worked for me: social.msdn.microsoft.com/Forums/en/transactsql/thread/02c6da90-954d-487d-a823-e24b891ec1b0?prof=required

if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U') 

   and o.id = object_id(N'tempdb..#tempTable')
)
DROP TABLE #tempTable;

---

Just a little comment from my side since the OBJECT_ID doesn't work for me. It always returns that

`#tempTable doesn't exist

..even though it does exist. I just found it's stored with different name (postfixed by _ underscores) like so :

#tempTable________

This works well for me:

IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#tempTable%') BEGIN
   DROP TABLE #tempTable;
END;

---

This could be accomplished with a single line of code:

IF OBJECT_ID('tempdb..#tempTableName') IS NOT NULL DROP TABLE #tempTableName;   

---

pmac72 is using GO to break down the query into batches and using an ALTER.

You appear to be running the same batch but running it twice after changing it: DROP... CREATE... edit... DROP... CREATE..

Perhaps post your exact code so we can see what is going on.

---

I usually hit this error when I have already created the temp table; the code that checks the SQL statement for errors sees the "old" temp table in place and returns a miscount on the number of columns in later statements, as if the temp table was never dropped.

After changing the number of columns in a temp table after already creating a version with less columns, drop the table and THEN run your query.

---

Now you can use the below syntax if you are using one of the new versions of SQL Server (2016+).

DROP TABLE IF EXISTS schema.yourtable(even temporary tables #...)

---

I recently saw a DBA do something similar to this:

begin try
    drop table #temp
end try

begin catch 
    print 'table does not exist'
end catch 

create table #temp(a int, b int)

---

My code uses a Source table that changes, and a Destination table that must match those changes.

-- 
-- Sample SQL to update only rows in a "Destination" Table
--  based on only rows that have changed in a "Source" table
--


--
-- Drop and Create a Temp Table to use as the "Source" Table
--
IF OBJECT_ID('tempdb..#tSource') IS NOT NULL drop table #tSource
create table #tSource (Col1 int, Col2 int, Col3 int, Col4 int)

--
-- Insert some values into the source
--
Insert #tSource (Col1, Col2, Col3, Col4) Values(1,1,1,1)
Insert #tSource (Col1, Col2, Col3, Col4) Values(2,1,1,2)
Insert #tSource (Col1, Col2, Col3, Col4) Values(3,1,1,3)
Insert #tSource (Col1, Col2, Col3, Col4) Values(4,1,1,4)
Insert #tSource (Col1, Col2, Col3, Col4) Values(5,1,1,5)
Insert #tSource (Col1, Col2, Col3, Col4) Values(6,1,1,6)

--
-- Drop and Create a Temp Table to use as the "Destination" Table
--
IF OBJECT_ID('tempdb..#tDest') IS NOT NULL drop Table #tDest
create table #tDest (Col1 int, Col2 int, Col3 int, Col4 int)

--
-- Add all Rows from the Source to the Destination
--
Insert #tDest
Select Col1, Col2, Col3, Col4 from #tSource


--
-- Look at both tables to see that they are the same
--
select *
from #tSource
Select *
from #tDest

--
-- Make some changes to the Source
--
update #tSource
    Set Col3=19
    Where Col1=1
update #tSource
    Set Col3=29
    Where Col1=2
update #tSource
    Set Col2=38
    Where Col1=3
update #tSource
    Set Col2=48
    Where Col1=4

--
-- Look at the Differences
-- Note: Only 4 rows are different. 2 Rows have remained the same.
--
Select Col1, Col2, Col3, Col4
from #tSource
except
Select Col1, Col2, Col3, Col4
from #tDest

--
-- Update only the rows that have changed
-- Note: I am using Col1 like an ID column
--
Update #tDest
    Set Col2=S.Col2,
        Col3=S.Col3,
        Col4=S.Col4
From    (   Select Col1, Col2, Col3, Col4
            from #tSource
            except
            Select Col1, Col2, Col3, Col4
            from #tDest
        ) S
Where #tDest.Col1=S.Col1 

--
-- Look at the tables again to see that
--  the destination table has changed to match
--  the source table.

select *
from #tSource
Select *
from #tDest

--
-- Clean Up
--
drop table #tSource
drop table #tDest

---

Yes, "invalid column" this error raised from the line "select company, stepid, fieldid, NewColumn from #Results".

There are two phases of runing t-sql,

first, parsing, in this phase the sql server check the correction of you submited sql string, including column of table, and optimized your query for fastest retreival.

second, running, retreiving the datas.

If table #Results exists then parsing process will check the columns you specified are valid or not, else (table doesn't exist) parsing will be by passsed the checking columns as you specified.

참고URL : https://stackoverflow.com/questions/659051/check-if-a-temporary-table-exists-and-delete-if-it-exists-before-creating-a-temp