Adding hundreds of new rows to a table quickly-sql plus

quinnd6

Hi Ive got loads of new entries that need to be entered into a table if they're not in there already.

Is there a quick way of doing this rather than having to add the entries manually using some kind of tool rather than using manual sql statements(would take a very long time since theres 800 new entries).?

Tom Dunne

What format are they in and what tools have you available to you (e.g. sql developer, Toad, etc.)?

quinnd6

Ive none of thos tools Im afraid just oracle sql plus.
I have excel aswell.

No other database tools

Tom Dunne

What format are the entries in? Are they in a file of some sort, or do you have to manually enter them?

quinnd6

They're a load of strings that need to be inserted into a database.

so I would have to execute hundreds of statements like this

insert into tablename(columnname) values('denidn.rjvj')

and the values would be different for each string I need to insert.

Tom Dunne

I don't think you are understanding me.

Where are these strings? Are they stored in a file? If so, what format is that file (e.g. xml, csv, etc.). Are they coming from a program?

Or are they on a sheet of paper and you have to manaully type them in?

quinnd6

Well I have them in an excel file.

I also copied and pasted them into a .txt file.

nice1franko

type really fast


- edit make a little java class to do it. or do u know any programming languages?

koloughlin

A quick and dirty way to do this is to use Excel to build your insert statements. Copy your list of values into column A of a spreadsheet. Then in column B put something like

="insert into tablename(columnname) values('" & a1 & "');"

Then you can simply use fill down to create all your insert statements. You can then run the inserts in SQL Plus.

If you want to do this in a more sophisticated way for something with larger volumes have a look at Oracle's SQL Loader.

Tom Dunne

koloughlin wrote:

A quick and dirty way to do this is to use Excel to build your insert statements. Copy your list of values into column A of a spreadsheet. Then in column B put something like

="insert into tablename(columnname) values('" & a1 & "');"

Then you can simply use fill down to create all your insert statements. You can then run the inserts in SQL Plus.

Oooh, I like quick and dirty.

Ahem, sorry. I actually have used this method in the past and it works a treat.

koloughlin wrote:

If you want to do this in a more sophisticated way for something with larger volumes have a look at Oracle's SQL Loader.

SQL Loader is the better option. I was actually just checking Oracle's own SQL Developer to see if it had any bulk load options but it doesn't. (SQL Developer is free to download from Oracle, btw).

quinnd6

kloughlin that was brilliant they're all in there now thanks a million you're a lifesaver.

Ill check out that oracle sql loader aswell.

Thanks a lot guys brilliant help

Justice

koloughlin's tip is excellant,

i dont know much about orcle SQL engine,
but this also works for Access Sql and a txt file C:\data\stuff.txt

insert into tablename (fieldname1) select f1 from [Text;DATABASE=C:\data\;HDR=No].[stuff.txt]