-1

I have the following issue in SQL Server, I have some code that looks like this:

IF OBJECT_ID('tempdb..##TempLog') IS NULL
    CREATE TABLE ##TempLog(id INT, name NVARCHAR(max));

INSERT INTO ##TempLog (id, name) 
VALUES (1, 'Test')

When I do this I get an error

There is already an object named '##TempLog' in the database

in SQL Server 2014, but in SQL Server 2016, everything is ok.

Arash Kh
  • 39
  • 1
  • 5
  • 3
    The first time I run that I get _String or binary data would be truncated._. After I add a size to nvarchar to get around that, and drop the faulty table, I can run it over and over again without issue. Your mistake with nvarchar makes me suspect this isn't your real code. – Nick.Mc Nov 12 '17 at 08:19
  • @ Nick.McDermaid thanks – Arash Kh Nov 12 '17 at 08:32
  • Does your temp table need to be visible to other processes / users? If not, then you should only use one `#` instead of `##`. A single `#` means that each process can have its own temp table. If more than one user runs the same process, then each has their own temp table. `##` means the temp table is visible to all users. If you use `##`, then the first user who runs it will create the temp table. A second user will get an error. And if the second user drops the `##` temp table, the first user may be negatively impacted. `##` also means the temp table persists after the user process ends. – James L. Nov 12 '17 at 08:46

2 Answers2

2

This will work:

IF OBJECT_ID('tempdb..##TempLog') IS NOT NULL
drop table ##TempLog
CREATE TABLE ##TempLog(id INT, name NVARCHAR(10));

INSERT INTO ##TempLog (id, name) 
VALUES (1, 'Test')

as @Nick.McDermaid was saying you should also replace NVARCHAR

enter image description here

but it will work also over sql2012:

enter image description here

Radu
  • 995
  • 12
  • 23
1

try this

DROP TABLE IF EXISTS tempdb.dbo.##TempLog
CREATE TABLE ##TempLog
  (
   id INT, 
   name NVARCHAR(10)
  )

If you want to use existing table use

IF OBJECT_ID('tempdb..##TempLog') IS NOT NULL
    Truncate TABLE #TempLog
else
    CREATE TABLE ##TempLog
    (
        id INT, 
        name NVARCHAR(10)
    )
Newaz Sharif
  • 424
  • 4
  • 12