Tech Tip: Learn How to Use the PSQL v9 Bulk Data Utility

Introduced as a new feature in Pervasive PSQL v9, the Bulk Data Utility (BDU) is a command-line utility that allows you to load data from a delimited text file into an existing PSQL database table. In this example, we will use PSQL v9.5 to create a new test table called "BDU_Test" in the DEMODATA database, and then we will insert records into the table using the BDU.

1. Launch the Pervasive Control Center (PCC)

2. In the right window frame, under "Common Tasks," select "Execute SQL Commands"

3. Expand the list of available databases, select "DEMODATA," and then click "OK."

4. In the 'SqlDoc' window, type the following to create a new table that will be part of the DEMODATA database:

CREATE TABLE BDU_Test
(
Name CHAR(20) NOT NULL CASE,
Number INTEGER,Company CHAR(25) NOT NULL CASE,
RoomNo UINT NOT NULL,
HeadOfDept UBIGINT NOT NULL
)

5. Now hit the F9 key to execute the SQL statement you just created

6. Copy and Paste the following text into a new ASCII file named "BDU_IN.txt":

"pervasiveBDUsample_1",12345,"pervasive",101,18446744073709551615
"pervasiveBDUsample_2",12346,"pervasive",102,18446744073709551614
"pervasiveBDUsample_3",12347,"pervasive",103,18446744073709551613
"pervasiveBDUsample_4",12348,"pervasive",104,18446744073709551612
"pervasiveBDUsample_5",12349,"pervasive",105,18446744073709551611

NOTE: In this example, we are saving the BDU_IN.txt file in a directory called "C:\temp\BDU". The "-t ," option is used to designate a comma delimiter between fields. The "-n \r\n" option is used to designate a carriage return line feed for the end of each record.

7. From a Command Prompt window, enter the following command:
c:\pvsw\bin>bdu DEMODATA BDU_Test c:\temp\BDU\BDU_In.txt -t , -n \r\n >>c:\temp\BDU\BDU_Out.txt

8. You can now view the results by looking at the c:\temp\BDU\BDU_Out.txt file.

Additional information on using the BDU is available via the online help and documentation under “BDU”.