开发者

How do I bulk insert data into SQL Server without using bcp?

开发者 https://www.devze.com 2023-02-22 08:31 出处:网络
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 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

0

精彩评论

暂无评论...
验证码 换一张
取 消