SQL: Is there way to insert multiple records quickly?

[Cogman]

So here's the problem, I have an import program that is inserting data into a SQL server. My method of inserting is to build a giant SQL command that looks roughly like

Insert into table (x, y, z) Values (a, b, c);
Insert into table (x, y, z) Values (d, e, f);
Insert into table (x, y, z) Values (g, h, i);
Insert into table (x, y, z) Values (j, k, l);
...

Well, this takes quite a while to do. The application builds the commands much faster then the SQL server can execute them. Is there a way to increase the speed at which SQL executes these commands? (I would like to keep the SQL server independent.)

 

[GeekDrew]

Depends on which database engine you are using. Try:

Insert into table (x, y, z) Values (a, b, c), (d,e,f), (g,h,i), (j,k,l);

 

[Cogman]

I found something that does it pretty well.

Insert Into table (x, y, z)
Select 'a', 'b', c'
Union all
Select 'd', 'e', 'f'

This provided an order of magnitude increase of speed.

Next I would want to do the same thing for an Update and Update if exists/insert (Upsert?).

 

[MrChad]

What application/language? Bind variables might help here.

 

[Cogman]

Im using MSSQL 2005, but I really don't want to do anything that would require detection of SQL Server as this app has the potential to be used with all sorts of SQL Servers.

 

[Markbnj]

You can write the data to a file and do a bulk insert. It's typically a lot faster.

 

[Snapster]

Originally posted by: Markbnj
You can write the data to a file and do a bulk insert. It's typically a lot faster.

This

 

[Cogman]

Originally posted by: Markbnj
You can write the data to a file and do a bulk insert. It's typically a lot faster.

Not all sql servers support a bulk insert. I'm trying to stay compatible. Basically if the command/keyword isn't in here then I want to try and avoid it.

BTW, for a large update query I found that this works

update t1
set bob = t2.bob
from test t1 inner join
(Select 'w' bob, 9 frank
Union ALL
Select 'y', 7 ) t2
on t2.frank=t1.frank
select * from test;

 

[BoberFett]

You're talking about optimization. Optimization is commonly platform specific, so you're going to have a tough time speeding it up without using features of the environment you're working in. Straight ANSI SQL has limits.

If there are indexes on the table, try dropping the indexes, then inserting the data, then recreating the indexes.

Edit: Wait, are you doing updates or insert?

 

[Cogman]

Originally posted by: BoberFett
You're talking about optimization. Optimization is commonly platform specific, so you're going to have a tough time speeding it up without using features of the environment you're working in. Straight ANSI SQL has limits.

If there are indexes on the table, try dropping the indexes, then inserting the data, then recreating the indexes.

Edit: Wait, are you doing updates or insert?

Some optimizations are not platform specific. Like the union example I gave above, That will be faster on all systems because rather then grabbing a lock for each insert statement, the SQL server only grabs one lock. That saves a lot of time in any platform. At worst, that will be exactly the same speed of multiple inserts (The very unlikely case that the SQL server is not thread safe).

I need to do both, the solution I found above for inserts is what I needed, and the solution I posted above for updates is also what I needed. I now have a third case of doing an update if the record exists or insert if it doesn't. I don't think I will be able to do that one to be honest.

 

[Markbnj]

Originally posted by: Cogman

Originally posted by: Markbnj
You can write the data to a file and do a bulk insert. It's typically a lot faster.

Not all sql servers support a bulk insert. I'm trying to stay compatible. Basically if the command/keyword isn't in here then I want to try and avoid it.

BTW, for a large update query I found that this works

update t1
set bob = t2.bob
from test t1 inner join
(Select 'w' bob, 9 frank
Union ALL
Select 'y', 7 ) t2
on t2.frank=t1.frank
select * from test;

Yeah, I thought about the fact that there is no ANSI syntax for that (as far as I am aware). If broad compatibility is a requirement then you just have to pump rows as fast as you can... which is what your post was about .

 

[brandonbull]

Originally posted by: Cogman
So here's the problem, I have an import program that is inserting data into a SQL server. My method of inserting is to build a giant SQL command that looks roughly like

Insert into table (x, y, z) Values (a, b, c);
Insert into table (x, y, z) Values (d, e, f);
Insert into table (x, y, z) Values (g, h, i);
Insert into table (x, y, z) Values (j, k, l);
...

Well, this takes quite a while to do. The application builds the commands much faster then the SQL server can execute them. Is there a way to increase the speed at which SQL executes these commands? (I would like to keep the SQL server independent.)

Depends on your import program that your are using. Did you try the BCP?