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

quinnd6 · 13-07-2007 02:12PM #1

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 · 13-07-2007 02:17PM

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

quinnd6 · 13-07-2007 02:29PM

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

No other database tools

Tom Dunne · 13-07-2007 02:42PM

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

quinnd6 · 13-07-2007 02:54PM

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 · 13-07-2007 03:03PM

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 · 13-07-2007 03:06PM

Well I have them in an excel file.

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

nice1franko · 13-07-2007 03:07PM

type really fast

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

koloughlin · 13-07-2007 03:08PM

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 · 13-07-2007 03:15PM

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 · 13-07-2007 03:40PM

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 · 13-07-2007 09:07PM

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]

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

Comments