Possible to check if table not exist, select top(0) * into newtable else do nothing

• KoldCoffee

  SSCoach

  Points: 18971

  More actions

  January 4, 2015 at 5:37 pm

  #293442

  I need, as part of a sql agent job, to create a check for the existence of a table. If it doesn't exist I need it created using the schema of existing table

  select top(0) * into <newtable> from <originaltable>

  If it already exists I want the agent job to do essentially nothing at that step.

  I've tried

  if not exists (select * from sysobjects where name='TESTTABLE' and xtype='U')

  SELECT TOP(0) * INTO dbo.TESTTABLE FROM dbo.ORIGINALTABLE

  ELSE

  SELECT 1 FROM dbo.TESTTABLE

  is it possible to do without getting error? shouldn't this work without error:

  Msg 2714, Level 16, State 6, Line 3

  There is already an object named ...

  how to do?

• KoldCoffee

  SSCoach

  Points: 18971

  More actions

  January 4, 2015 at 5:51 pm

  #1767645

  sorry, got it

  IF object_id('dbo.TESTTABLE') is null

  begin

  SELECT TOP(0) * INTO dbo.TESTTABLE FROM dbo.ORIGINALTABLE

  END;