How to verify if a table exist, create it&it's relasions before INSERT 1

[royc75]

Hello,

I have a standard INSER statement.
I would like to extend it to first check if the table exist.
In case yes, the INSERT will occur.
In case now, the table will be created AND it's Primary key and relsionships will be created as well before the INSERT. Then, the original INSERT will occur.
How can do this?

 

[bborissov]

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.Tables
                        WHERE TableName = 'MyTable)
   BEGIN
--- TABLE DOES NOT EXISTS
       CREATE TABLE MyTable .......
-- add indexes
-- add constrains
-- etc.
   END
   

INSERT ......

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[royc75]

Hi Borislav,

But if I'll do that, the SP will not compile at the
-- add indexes
-- add constrains
and at the INSERT statement since when it compiles it searches for the table I am inserting into and if it does not physically exist I can't write these lines...
Perhaps I'm wrong, in that case, I will be happy to see a code example that is actually working.

 

[bborissov]

This code compiles good for me:

CREATE PROCEDURE MyInsert
AS
BEGIN
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.Tables
                        WHERE Table_Name = 'BugTest')
   BEGIN
       CREATE TABLE BugTest (Fld1 int)
   END
INSERT INTO BugTest VALUES (1)
END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[royc75]

The CREATE will work, try adding
-- add indexes
-- add constrains
And tell me if it is working to you.
If yes, I will happy to see code example.

 

[bborissov]

This also compiles goog for me:

CREATE PROCEDURE MyInsert
AS
BEGIN
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.Tables
                        WHERE Table_Name = 'BugTest')
   BEGIN
       CREATE TABLE BugTest (Fld1 int)
       CREATE INDEX MyIndex ON  BugTest(Fld1)
       ALTER TABLE BugTest WITH NOCHECK
             ADD CONSTRAINT MyConstr CHECK (Fld1 > 0)
   END
INSERT INTO BugTest VALUES (1)
END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[royc75]

Really...?
Well, I will check it out and let you know!

 

[bborissov]

Yep,
BTW I have only SQL Management Studio here.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.