如何使用db = SQL Server 2008 Express在java（使用JDBC）中执行批量插入语句 [英] how to execute bulk insert statement in java (using JDBC) with db=SQL Server 2008 Express

问题描述

我正在尝试在SQL Server 2008 Express上执行BULK INSERT语句。
（它基本上占用指定文件中的所有字段，并将这些字段插入表中的相应列中。）

I am trying to execute a BULK INSERT statement on SQL Server 2008 Express. (It basically takes all fields in a specified file and inserts these fields into appropriate columns in a table.)

下面给出了批量插入的示例声明 -

Given below is an example of the bulk insert statement--

BULK INSERT SalesHistory FROM 'c:\SalesHistoryText.txt' WITH (FIELDTERMINATOR = ',')

以下是我试图使用的Java代码（但它无法正常工作）...有人可以告诉我在这里做错了什么或指向我使用Bulk Insert语句的java代码示例/教程？ -

Given below is the Java code I am trying to use (but its not working)...Can someone tell me what I am doing wrong here or point me to a java code sample/tutorial that uses the Bulk Insert statement? --

public void insertdata(String filename)
{
    String path = System.getProperty("user.dir");
    String createString = "BULK INSERT Assignors FROM  " + path + "\\" +filename+ ".txt WITH (FIELDTERMINATOR = ',')";   
    try  
       { 
            // Load the SQLServerDriver class, build the 
            // connection string, and get a connection 
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
            String connectionUrl = "jdbc:sqlserver://arvind-pc\\sqlexpress;" + 
                                    "database=test01;" + 
                                    "user=sa;" + 
                                    "password=password1983"; 
            Connection con = DriverManager.getConnection(connectionUrl); 
            System.out.println("Connected."); 

            // Create and execute an SQL statement that returns some data.  
            String SQL = "BULK INSERT dbo.Assignor FROM  " + path + "\\" +filename+ ".txt WITH (FIELDTERMINATOR = ',')";  
            Statement stmt = con.createStatement();  
            ResultSet rs = stmt.executeQuery(SQL);

            // Iterate through the data in the result set and display it.  
            while (rs.next())  
            {  
               //System.out.println(rs.getString(1) + " " + rs.getString(2));
                System.out.println(" Going through data");
            }

       }  
       catch(Exception e)  
       { 
            System.out.println(e.getMessage()); 
            System.exit(0);  
       } 
}

推荐答案

我我猜你的SQL字符串缺少文件名周围的单引号。请尝试以下方法：

I'd guess that your SQL string is missing the single quotes around the filename. Try the following:

        String SQL = "BULK INSERT dbo.Assignor FROM '" + path + "\\" +filename+ ".txt' WITH (FIELDTERMINATOR = ',')";  

编辑以回应您的评论：我不希望有在批量插入之后是ResultSet中的任何内容，这与我在普通 INSERT 语句后面的ResultSet中不期望任何内容的方式非常相似。这些语句只是将它们提供的数据插入表中，它们也不会返回它。

EDIT in response to your comment: I wouldn't expect there to be anything in the ResultSet following a bulk insert, in much the same way that I wouldn't expect anything in a ResultSet following an ordinary INSERT statement. These statements just insert the data they are given into a table, they don't return it as well.

如果你没有收到任何错误信息，那么它看起来像你的批量插入工作。如果您在SQLCMD或SQL Server Management Studio中查询表，是否看到了数据？

If you're not getting any error message, then it looks like your bulk insert is working. If you query the table in SQLCMD or SQL Server Management Studio, do you see the data?

INSERT ， UPDATE ， DELETE 和 BULK INSERT 语句不是查询，所以你不应该使用 executeQuery（）方法。 executeQuery（）仅用于运行 SELECT 查询。我建议使用 executeUpdate（String） 方法。此方法返回 int ，这通常是插入/更新/删除的行数。

INSERT, UPDATE, DELETE and BULK INSERT statements are not queries, so you shouldn't be using them with the executeQuery() method. executeQuery() is only intended for running SELECT queries. I recommend using the executeUpdate(String) method instead. This method returns an int, which is normally the number of rows inserted/updated/deleted.

这篇关于如何使用db = SQL Server 2008 Express在java（使用JDBC）中执行批量插入语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！