Clever Geek Handbook

MySQL inserts data only if the table does not exist

Using strictly SQL (no PHP or whatever), is it possible to create a table and insert default data into that table only if that table doesn't exist?

0


source to share


5 answers


Use the CREATE TABLE ... SELECT format:



create table if not exists tablename as
select * from defaultdata;
+2


source


Here's one way to do it:

CREATE TABLE IF NOT EXISTS T (
  ID int(10) unsigned NOT NULL primary key,
  NAME varchar(255) NOT NULL
);

REPLACE INTO T SELECT 1, 'John Doe';
REPLACE INTO T SELECT 2, 'Jane Doe';


REPLACE is a MySQL extension to the SQL standard that either inserts or deletes and inserts.

0


source


You can make a selection in one of the metadata tables

if(not exists select * from whatever_meta where table_name = "whatever)
begin
   ...
end

You would need to do some research to figure out exactly how ...

0


source


Is it possible to store the status of a table as a variable and then use that variable to determine whether to insert data? Example:

@status = SHOW TABLES LIKE 'my_table';
INSERT INTO my_table VALUES (1,'hello'),(2,'world') WHERE @status <> false;

The problem with Paul Morgan is that he expects the data to already exist in another table. Jonas' answer would be extremely resource intensive, especially if there are a lot of REPLACEMENT in there (which are not needed if the table exists).

0


source


Maybe I'm missing a point, but why the default data can't be a set of insert statements ... and what just needs to be done is create a table if it doesn't exist, followed by insert statements ... that's the data by by default should not exist in another table.

0


source






More articles:


All Articles