The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL Server Newbie: how to bulk INSERT?

I'm just teaching myself how to do stored procedures in SQL Server, and how to interact between Delphi 6 Enterprise and SQL Server 2005.  I have a lot of dBASE-type DB experience including StreamlineSQL with the Advantage Database, but I have never written a stored procedure in TransactSQL until today.

I have created a client dataset in Delphi and users use that to choose one or more records.  When the users click "finish" a series of records need to be created in the SQL Server database on the server, each with the ID of a chosen record from the client dataset.  Example:

X Swimming Pool
  Copies
X Sauna

The user chose to go to the swimming pool and sauna, so two "chosen activities" records need to be created in my database.  Remember, the "source" table above only exists in memory on the client computer so I can't use it as part of an INSERT statement in a stored procedure on the server.

Here is my question: what is the best way to pass this series of IDs to the stored procedure so they can be added as separate records in a table?

I could create a pseudo-array structure using a string parameter to my stored procedure, but that's a lot of work.
Is there a simpler way?
Karl Perry Send private email
Saturday, June 24, 2006
 
 
Never mind.  I did a Google search and discovered the use of XML.  Wow.  How cool.

I built an XML structure from the data, then passed it to my stored procedure as a varchar parameter, then used the sp_xml_preparedocument and OpenXML features of SQL Server to insert it into my tables.
Karl Perry Send private email
Saturday, June 24, 2006
 
 
Oh no! You must be joking...
Not Steve Mcconnell
Sunday, June 25, 2006
 
 
If Delphi supports .NET, perhaps you're looking for the System.Data.SqlBulkCopy class:

* Load your data into a DataTable instance e.g. by using the BeginLoadData and LoadDataRow methods
* Copy the data to the SQL server using the SqlBulkCopy class
Christopher Wells Send private email
Sunday, June 25, 2006
 
 
Not Steve:

"Oh no! You must be joking..."

As a SQL Server newbie I can't recognize what you are disparaging.  Would you please elaborate?  If I'm doing something stupid I'd like to know now.

Christopher:

I'm using Delphi 6, which was the last version prior to their .Net-compatible versions.  So with D6 I can't use that function.  However, last night after the 'never mind' post I spent time to develop XML strings to feed into my stored procedures, and that works fantastic.
Karl Perry Send private email
Sunday, June 25, 2006
 
 
I guess I spoke too soon.

I thought there was an easier way to do this,
but I can't remember... (I was thinking about something along the lines of Bulk Copy)

To save my skin I send you this link:
http://www.codeproject.com/database/Pass_Array_To_SP.asp

But that's not very pretty, and more along the lines of your initial array idea.


So your solution is fine with me :)

By the way:
You are never doing anything stupid.
A (working) solution to a problem can never be stupid:
It's just that some solutions can be less efficient than others ;)
Not Steve Mcconnell
Monday, June 26, 2006
 
 
Can't you also just concatenate multiple sql statements together with a semicolon betewen them?

i.e.

insert into1 (fields) values (values);insert into1 (fields) values (values);insert into1 (fields) values (values);insert into1 (fields) values (values);insert into1 (fields) values (values);insert into1 (fields) values (values);

and execute it?
Dan Hirsch Send private email
Wednesday, June 28, 2006
 
 
Bulk Copy is good idea.  Not a hugh MS-SQL expert but in other RDBMS you can use prepared statements with a bunch of data you want INSERTED.

You prepare the statement, then upload the data, and let all the processing take place server side.

Oracle, Progress, DB2, and PostgreSQL all do this.  I wonder if this is of any help?
~Eric
Thursday, June 29, 2006
 
 
If you go the multiple SQL queries seperated by a semi-colon route then take a look at stuffing them in a transaction.
Cymen
Sunday, July 02, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz