1

I am using SQL Server 2012 and I have the following T-SQL query:

USE MyDatabase

INSERT INTO [Table1]
    SELECT *
    FROM [xxx.xx.x.xx].[xxx].[dbo].[Table1]

I would like to modify this query so that it copies Table1 into MyDatabase only if that table does not already exist in MyDatabase.

I've had a look here but I can't figure out how to migrate the solutions into my problem: Check if table exists in SQL Server

How can I achieve this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3115933
  • 4,303
  • 15
  • 54
  • 94

1 Answers1

-1

This should do it:

    IF (NOT EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'Table1'))
    BEGIN
        INSERT INTO [Table1]
        SELECT *
        FROM [xxx.xx.x.xx].[xxx].[dbo].[Table1]
    END

More details and approaches you can read here: Check if table exists in SQL Server

Pixel Hunter
  • 92
  • 10
  • Isn't that doing the contrary of what I want to achieve? My aim is that if Table1 exists, then it should not start the insert operation. I did not want to run that on my live database so that I do not mess things up. Just trying to confirm if your solution is doing just that. – user3115933 Jul 08 '18 at 10:11
  • 1
    Yes you're right, my bad. Just insert a `NOT` before the `EXISTS` that would be a `( NOT EXISTS`, I will edit my post... – Pixel Hunter Jul 08 '18 at 10:28
  • Thanks. Works beautifully. – user3115933 Jul 08 '18 at 10:31
  • 2
    I'm surprised that works, in fact I'm confident it wouldn't as there would need to be a `CREATE` script first for the `INSERT INTO` to work. Seem like what you'd really want is a `SELECT...INTO...`. – Thom A Jul 08 '18 at 11:06