creating SQL script

Question

I'm trying to create an SQL script to automatize inserting of values into a table.

I have a table table1 with 2 columns: key, value.

I want to insert a few rows:

INSERT INTO table1 (key, value) VALUES ("tomato1","random_value_1")
INSERT INTO table1 (key, value) VALUES ("tomato2","random_value_2")
INSERT INTO table1 (key, value) VALUES ("tomato3","random_value_3")
INSERT INTO table1 (key, value) VALUES ("tomato4","random_value_4")

How can I put this into a shell script that I can execute from command line.

Thanks

Are you asking how to create a script of 1000 inserts or how to run such a script ? — A.B.Cade
– A.B.Cade, Commented Jun 19, 2012 at 19:38
a script with 5 entries will do; I can adapt it. The OS is unix, hence it's called a shell script ;) — Adrian
– Adrian, Commented Jun 19, 2012 at 19:54

Randy · Accepted Answer · 2012-06-19 18:49:02Z

2

save as a file with .sql extension.

then run from the command line with a sql connection tool like SQLPLus (you don't indicate which database you are on)

answered Jun 19, 2012 at 18:49

Randy

16.7k2 gold badges40 silver badges56 bronze badges

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

1 Comment

Adrian Over a year ago

adding this comment for answer completion (by A. B. Cade): put the insert statements in a file (filename.sql) and then run it from your script like this: sqlplus <user>/<password>@<db> @filename.sql

bnvdarklord · Accepted Answer · 2012-06-19 18:51:05Z

2

You should also combine inserts to the same table into one, as it is much faster, like so:

INSERT INTO table1 
(key, value) VALUES 
("tomato1","$1"),
("tomato2","$2"),
("tomato3","$3"),
("tomato4","$4")

answered Jun 19, 2012 at 18:51

bnvdarklord

3092 silver badges6 bronze badges

1 Comment

bnvdarklord Over a year ago

The difference in execution time is pretty big. You can easily fix them using find/replace techniques.(replace insert into ... with nothing and just manually add the first one in the beginning, and replace new lines with ,\n so everything is seperated with a comma.)

Collectives™ on Stack Overflow

creating SQL script

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related