I have SQL Server 2005 DB with a number of tables of the form
Table (id <Primary Key, Identity column>, name <VarChar (255)>)
I have a Java Servlet that needs to be able to bulk a开发者_如何学Godd information to those tables. The SQL Server DB is running on a different machine than the Servlet, and I do not have access to the file system of that machine.
My understanding is that I can't use Bulk Insert because it requires the data file to be on the server's file system (which I can't do), it requires you to fill in all fields (can't do, one field is an identity field that is filled in by the DB) or to have the unfilled field be the last one (I put the identity / id field first, and have no desire to change that), and requires the format file to be on the Server's file system.
In MySQL, I'd simply use Load Data Local InFile. SQL Server 2005 appears to lack such a capability. Am I just missing something? Is there any way for me to add more than one record at a time to a SQL Server table w/o using bcp or Bulk Insert?
TIA,
Greg
INSERT INTO Table (column_name, ...)
Values
(column_values,...),(column_values,...),(column_values, ...), ...
Note this is possible with SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms174335%28v=SQL.100%29.aspx
For SQL Server 2005 and Under you would need to do something like this
INSERT INTO table (column_name, column_name, ...)
SELECT column_value , column_value, ...
UNION ALL
SELECT column_value , column_value, ...
UNION ALL
SELECT column_value , column_value, ...
UNION ALL
SELECT column_value , column_value, ...
UNION ALL
SELECT column_value , column_value, ...
So you have neither access to the filesystem on the servlet machine nor the SQL Server? (I'm not sure how you would do this in MySQL either, since LOAD DATA LOCAL INFILE needs a file).
ADO.NET supports a SqlBulkCopy API: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
精彩评论