Mysql create table and insert at the same time

Question

                CREATE TABLE IF NOT EXISTS `$id` (
                    `id` int(11) NOT NULL AUTO_INCREMENT,
                  `start` varchar(10) NOT NULL,
                  `end` varchar(10) NOT NULL,
                  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                  PRIMARY KEY (`id`)
                ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;


                INSERT INTO `$id`(`start`, `end`) VALUES ('0','0')

I have been trying to figure out how I can bind these two Mysql(requests(?)) into one with no success. Basically I want it to work so when I create the table it should also add the values 0 and 0 to "start" and "end" rows. But I still want the "Create table if not exists" to be in effect for the INSERT INTO. So if the table exist don't INSERT either.

`VALUES ('0','0')` since they're VARCHAR - Plus, where is `$id` coming from? — Funk Forty Niner, Jul 03 '14 at 19:18
$id is from another part of the program, which will look something like this " 76561197961581096" — Hannes Brolin Lagerstedt, Jul 03 '14 at 19:20
OK. Well you'd still need to use `VALUES ('0','0')` since `start` and `end` are VARCHAR. — Funk Forty Niner, Jul 03 '14 at 19:22
Since your `date` is `NOT NULL`, have you tried adding a value to your query? — Funk Forty Niner, Jul 03 '14 at 19:25
@Fred-ii- There's a default value for this column. That should work fine. — VMai, Jul 03 '14 at 19:26
Only thing I can see is adding a semi-colon at the end `INSERT INTO... VALUES ('0','0');` see if that makes it kick in. — Funk Forty Niner, Jul 03 '14 at 19:29
Oh, i must have explained it wrong, I want so the "INSERT INTO" is a part of the "CREATE TABLE" so one single mysql request, possible? — Hannes Brolin Lagerstedt, Jul 03 '14 at 19:31
What you are trying to achieve that is not clear. this exact query works for me it creates the table if not exists and inserts data. — madteapot, Jul 03 '14 at 19:31
I doubt that's possible to do. If you want to do that, you will need to do it outside of PHPmyadmin or other program you're using, with PHP etc. — Funk Forty Niner, Jul 03 '14 at 19:32
Here is an example link http://codewalkers.com/c/a/Database-Code/MySQL-InstaTable/ and http://stackoverflow.com/q/21397704/ — Funk Forty Niner, Jul 03 '14 at 19:33

VMai · Answer 1 · 2014-07-03T20:01:49.577

You could do that with following single statement:

CREATE TABLE IF NOT EXISTS `$id` (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `start` VARCHAR(10) NOT NULL,
    `end` VARCHAR(10) NOT NULL,
    `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB 
SELECT '0' AS `start`, '0' AS `end`;

You use the CREATE TABLE ... SELECT syntax with selecting not from a table, but constant values and get the autoincrement value and the current_timestamp by default.

as of MySQL 5.5.6 or newer, see following excerpt from the manual, CREATE TABLE ... SELECT:

As of MySQL 5.5.6, handling of CREATE TABLE IF NOT EXISTS ... SELECT statements was changed for the case that the destination table already exists. This change also involves a change in MySQL 5.1 beginning with 5.1.51.

Previously, for CREATE TABLE IF NOT EXISTS ... SELECT, MySQL produced a warning that the table exists, but inserted the rows and wrote the statement to the binary log anyway. By contrast, CREATE TABLE ... SELECT (without IF NOT EXISTS) failed with an error, but MySQL inserted no rows and did not write the statement to the binary log.

MySQL now handles both statements the same way when the destination table exists, in that neither statement inserts rows or is written to the binary log. The difference between them is that MySQL produces a warning when IF NOT EXISTS is present and an error when it is not.

This change means that, for the preceding example, the CREATE TABLE IF NOT EXISTS ... SELECT statement inserts nothing into the destination table as of MySQL 5.5.6.

Mysql create table and insert at the same time

1 Answers1