Inserting records to an interface

views:

answers:

Inserting records to an interface

I have set up a stored procedure for inserting a single record into a table. The reason for this is to create a general interface for people to use so I can change the table structure that it points to without anyone noticing or having to change code on their end in the future.

The problem occurs when the user has to insert many records. Normally they would be able to do an INSERT statement which inserts many records at once but with this interface they are forced to loop through each record, inserting it individually. Speed is not the biggest factor here, accessibility is.

I have considered using a view but I'm not sure how well this would work. I'm simply not familiar enough with them to know for sure. Also, queries from other servers may be accessing the interface and I don't think views allow you to insert from another server.

What would you suggest I do?

+1 A:

You can have 2 stored procedures. One uses SQL Server's BULK INSERT and the other one is your standard one for inserting one record (you already have this). Your users can call either one depending on their needs.

For more info on BULK INSERT http://msdn.microsoft.com/en-us/library/ms188365.aspx

del.ave 2010-08-09 15:39:31

I don't understand how the bulk insert solves the problem. How do you "pass in" the records?

Joe Philllips 2010-08-09 15:43:12

bulk insert needs a file. Here is an example:http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

del.ave 2010-08-09 15:50:36

Using a file isn't going to solve the problem. The problem isn't that I have a huge amount of records, it's that I don't want the users to have to rewrite their programs to use a loop instead of an INSERT statement while accessing the interface

Joe Philllips 2010-08-09 15:53:49

I don't think that you can insert multiple data records with one SQL Server "INSERT" statement. SQL Server 2008 might have something for that, but not 2005 and 2000.Here is a trick that you can try. I don't know how well it will perform:http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

del.ave 2010-08-09 16:01:21

When I say "insert multiple records at once" I mean like this: INSERT INTO table1 (column1, column2, ...)SELECT column3, column4, ...FROM table2

Joe Philllips 2010-08-09 16:03:56

So the records are coming from a different table (table2). That simplifies the problem. You can use a "SELECT INTO" statement: http://www.w3schools.com/Sql/sql_select_into.asp

del.ave 2010-08-09 16:06:29

I don't want to give the users access to the table. The whole point is to use an interface so I can modify the back end when needed.

Joe Philllips 2010-08-09 16:22:26

I understand that. What are the parameters to your interface method(s)?If you control the concrete implemenation, then why can't you change it to use BULK INSERT?

del.ave 2010-08-09 17:11:12

Take a look at the SQLBulkCopy class. This seems to do what you are looking for.

Abe Miessler 2010-08-09 15:49:19

I thought about that one too, this assumes he is using a .NET language.

del.ave 2010-08-09 15:53:17

The users can use whatever language they want. They do not have access to the table itself (the point of the interface...)

Joe Philllips 2010-08-09 15:59:08

When you say users are you referring to other programmers? Can you expand on what you mean by "interface"?

Abe Miessler 2010-08-09 16:03:27

I want other programmers to program to an interface. http://www.artima.com/lejava/articles/designprinciples.html

Joe Philllips 2010-08-09 16:23:10

ansaurus

tags:

views:

answers:

Inserting records to an interface

related questions