SQL Check if table exists then Create Table & Insert into same query

Question

Tried This Code But it gives "ER_PARSE_ERROR"

Err Description: "sqlMessage": "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Insert values ('Bitcoin')' at line 1"

var query = "CREATE TABLE IF NOT EXISTS projects (project_name VARCHAR(10)) Insert values ('" + projectName + "')";

I want to Insert a new Project in the Projects Table, create the table if it does not exists and insert the record, I can write multiple queries for this work and check response if success then proceed with the next query but I am looking for a query which does all the work concatenating multiple queries in itself.

Note: I am using mysql module in Nodejs

The process should work synchronously one after the other, when a user sends a post request to the route '/add_project' with data in body "project_name":"project1" then the server inserts the record in the mysql table projects but it generates err "No such Table " if that was the first record. I want it to work synchronously first by creating the table if not exists and then inserting the record.

Edit: Added screenshot for @Nick suggestion

Do you have a multithreaded setup, i.e. would you expect it to be possible for more than one thread in your application to execute the CREATE TABLE and INSERT statements? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 15, 2018 at 3:17
No, I only want the procedure to work synchronously one after the other, when a user sends a post request to the route '/add_project' with data in body "project_name":"project1" then the server inserts the record in the mysql table projects but it generates err "No such Table " if that was the first record. I want it to work synchronously first by creating the table if not exists and then inserting the record — nilansh bansal
– nilansh bansal, Commented Oct 15, 2018 at 3:29

Nick · Accepted Answer · 2018-10-15 04:02:15Z

1

You could use CREATE TABLE ... SELECT syntax:

var query = "CREATE TABLE IF NOT EXISTS projects (project_name VARCHAR(10)) SELECT '" + projectName + "' AS project_name";

Demo on rextester

answered Oct 15, 2018 at 4:02

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nilansh bansal Over a year ago

I want to insert a record into the table after creating the table select works when there are already some records in the table.

Nick Over a year ago

This query is not selecting records from a table, it is just selecting the string projectName. Have a look at my demo and you will see it starts from a non-existent table (because the first thing I do is DROP the table.

Nick Over a year ago

@nilanshbansal Sorry messed up my demo. It's working again now.

nilansh bansal Over a year ago

Thanks for the answer.

nilansh bansal · Accepted Answer · 2018-10-15 03:35:33Z

Working Code But I need to get less complicated code without much error handling:

app.post('/add_project_to_database',function(req,res){
var projectName = req.body.project_name;

//CHECK IF PROJECT EXISTS
var query = "Select project_name from projects where project_name = '" + projectName + "'";
return res.send('Project Already Exists!');

//INSERTING PROJECT
// var query = "CREATE TABLE IF NOT EXISTS projects (project_name VARCHAR(10)) Insert values ('" + projectName + "')";
var query = "Insert into projects(project_name) values ('" + projectName + "')";

con.query(query, function(err, result){
    if(err){
        var query_create = "CREATE TABLE IF NOT EXISTS projects (project_name VARCHAR(10))";
        con.query(query_create,function(error,res_create){
            if(error){
                console.log(error);
                res.send(error);
            }
            else{
                console.log("TABlE CREATED");
                con.query(query,function(error_insert,response){
                    if(error_insert){
                        console.log(error_insert);
                        res.send(error_insert);
                    }
                    else{
                        console.log("RECORD INSERTED SUCCESSFULLY !");
                    }
                });
            }
        });
    }else{
        res.send("Successfully Inserted Project");
    }
});
});

Collectives™ on Stack Overflow

SQL Check if table exists then Create Table & Insert into same query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related