CODEWITHSUNDEEP
mysql
Matt Clark
Matt Clark

Reputation: 28609

mysql insert into table if exists

In my project I have two code paths that interact with MySQL during first time setup. The first step is the database structure creation, in here, a user has the ability to pick and choose the features they want - and some tables may not end up being created in the database depending on what the user selects.

In the second part, I need to preload the tables that did get created with some basic data - how could I go about inserting rows into these tables, only if the table exists?

I know of IF NOT EXISTS but as far as I know, that only works with creating tables, I am trying to do something like this

INSERT INTO table_a ( `key`, `value` ) VALUES ( "", "" ) IF EXISTS table_a;

This is loaded through a file that contains a lot of entries, so letting it throw an error when the table does not exist is not an option.

Upvotes: 3

Views: 11297

Answers (2)

PaulF
PaulF

Reputation: 6773

If you know that a particular table does exist with at least 1 record (or you can create a dummy table with just a single record) then you can do a conditional insert this way without a stored procedure.

INSERT INTO table_a (`key`, `value`)
SELECT "", "" FROM known_table
WHERE EXISTS (SELECT *
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = 'your_db_name') AND (TABLE_NAME = 'table_a')) LIMIT 1;

Upvotes: 1

Akshey Bhat
Akshey Bhat

Reputation: 8545

IF (SELECT count(*)FROM information_schema.tables WHERE table_schema ='databasename'AND table_name ='tablename') > 0
THEN
INSERT statement 
END IF

Use information schema to check existence if table

Upvotes: 7

Related Questions