SQL Server通過BCP進行大批量資料匯入匯出

標籤：伺服器   tostring   sys   存在   div   使用者   ring   user   bsp   

預置條件：

使用sa帳號登入SQL Server Management Studio，右鍵點擊安全性-登入名稱-資料庫使用者名稱屬性，設定伺服器角色為sysadmin。

刪除已存在的預存程序

String JOB_BCP_DELETE_SQL = “IF EXISTS (SELECT * FROM sys.objects WHERE name = ‘PROCEDURE_BCP_OUT‘ AND type in (N‘P‘, N‘PC‘))\n DROP PROCEDURE PROCEDURE_BCP_OUT \n”；pre = conn.prepareStatement(JOB_BCP_DELETE_SQL);pre.execute();

建立預存程序

String sql = "set @sql = N‘BCP ‘ + @tableName + ‘ out ‘ + @filePath + ‘ -c -t -U \"‘ + @userName + ‘\" -P \"‘ + @password + ‘\" -S \"‘ + @dbIp + ‘\"‘\n";StringBuffer createSqlBuf = new StringBuffer();createSqlBuf.append("CREATE PROCEDURE PROCEDURE_BCP_OUT \n")    .append("@dbIp nvarchar(256),\n")    .append("@userName nvarchar(256),\n")    .append("@password nvarchar(256),\n")    .append("@tableName nvarchar(256),\n")    .append("@filePath nvarchar(256)\n")    .append("AS\n")    .append("BEGIN\n")    .append("EXEC master.sys.sp_configure ‘show advanced options‘, 1\n")    .append("RECONFIGURE\n")    .append("EXEC master.sys.sp_configure ‘xp_cmdshell‘, 1\n")    .append("RECONFIGURE\n")    .append("declare @sql nvarchar(256)\n")    .append(sql)    .append("EXEC master..xp_cmdshell @sql\n")    .append("EXEC master.sys.sp_configure ‘show advanced options‘, 1\n")    .append("RECONFIGURE\n")    .append("EXEC master.sys.sp_configure ‘xp_cmdshell‘, 0\n")    .append("RECONFIGURE\n")    .append("END");String createSql = createSqlBuf.toString();pre = conn.prepareStatement(createSql);pre.execute();

執行預存程序

StringBuffer execSqlBuf = new StringBuffer();execSqlBuf.append("EXECUTE PROCEDURE_BCP_OUT \n")    .append("@dbIp=?, ").append("@userName=?, ").append("@password=?, ")    .append("@tableName=?, ").append("@filePath=?");String execSql = execSqlBuf.toString(); pre = conn.prepareStatement(execSql);pre.setString(1, dbIp);pre.setString(2, dbUserName);pre.setString(3, dbPassword);pre.setString(4, tableName);pre.setString(5, file);pre.execute();

 

SQL Server通過BCP進行大批量資料匯入匯出