Tell the server that we want to add new rows (records) to a table

If we want to bulk insert data, we can reference it from another table

INSERT INTO "<schema_name>"."<table_name>" (<field_name1>, <field_name2>)
SELECT <value1>, <value2>
FROM <table> | <view>;

Product Offers

We have received a bulk of new planned offers in a CSV file

• CSV = Comma Separated Values
• It's a bit like a table where:
  • Columns are separated by commas
  • Rows are separated by new lines

Loading this into a staging table

• When we receive data from an external source like this it is a good idea not to put it into our production database immediately
• It is best to create a 'staging' table so we can triage it before we bulk insert
• Let's call our staging table stage_Product_Offers
• It will be the same as Product_Offers except:
  • We're including the word stage_ in the table name
  • We're not including the offer_id because this will be generated automatically

CREATE TABLE "sequel-mart-schema"."stage_Product_Offers" (
	offer_name VARCHAR(50) NOT NULL,
	product_id INT NOT NULL DEFAULT 0,
	offer_discount_percentage NUMERIC(4,2),
	offer_start_date DATE,
	offer_end_date DATE
);

Make sure the schema is included in the name

Import the CSV data to this table

Because we are using a Docker image we can't just pull the data straight from our hard-drive.

• Right-click on the stage_Product_Offers table (screenshot 3)

• Click 'Import/Export Data...' and fill the form out:

  Options Tab

  • Import/Export = Import
  • Filename
    • Click on the file icon on the right
    • Click the up-arrow icon on the left of the new pop-up until you get to the root folder ('/') (screenshot 5)
    • You should see a long list of folders
    • Select the 'tmp' file (this is the only one not locked)
    • Click the 'Upload file' icon (screenshot 6)
    • Locate the CSV file from (your project folder)\apprentice-boot-camp-databases\backend\imports\Product_Offers_Data.csv
    • Drag and drop the CSV file into the grey area below
    • Click the x on the top-right of the pop-up. The file should now be in the /tmp/ folder (screenshot 7)
    • Click 'Select' in the bottom-right and the file should appear as /tmp/Product_Offers_Data.xls
  • Format = csv
  • Encoding = SQL_ASCII
  • Header = Set the toggle on
  • Delimiter = , (comma)

  Columns tab

  • Columns to Import = Select all 5 columns in the order they are in the table from the dropdown (screenshot 9). They may already be pre-populated

• Click OK on the bottom-right of the import/export wizard to start the import

All being well the data should now be in the staging table

SELECT *
FROM "sequel-mart-schema"."stage_Product_Offers";

INSERT INTO Product_Offers

Now if we run the following:

INSERT INTO "sequel-mart-schema"."Product_Offers"
	(offer_name, product_id, offer_discount_percentage, offer_start_date, offer_end_date)
SELECT offer_name
	, product_id
	, offer_discount_percentage
	, offer_start_date
	, offer_end_date
FROM "sequel-mart-schema"."stage_Product_Offers";

we should find the 19 new offers have been inserted into the Product_Offers table

and if we query the table we can see the new offers:

SELECT *
FROM "sequel-mart-schema"."Product_Offers";