Insert data

In this article you are able to find out basic syntax for how to insert data to tables. This has been tested on Microsoft SQL Server so this might differ if you are using Oracles or MySQL database management systems (DBMS).

Syntax

To insert data to database, you need to first have a table where to insert data. After created table you can insert data, but you need to be specific with all the attributes you gave to tables properties. For example if you have CHAR(4) as a attribute property you need to have four it is then four character, not more not less. Then again if you have property values as a NOT NULL ,then there needs to be something to add for.

As an example for the previous article, this is an example of one possible data row. If you didn’t read it, here is the basic syntax for creating table.

CREATE TABLE PLAYER
(player_id         VARCHAR(8) PRIMARY KEY,
first_name         VARCHAR(20) NOT NULL,
last_name          VARCHAR(30) NOT NULL,
height             DECIMAL(6,2) NOT NULL,
weight             DECIMAL(6,2) NOT NULL,
racket_hand        CHAR(1),
team_id            VARCHAR(10) NOT NULL,
FOREIGN KEY (team_id) REFERENCES TEAM );

And here is one example data row, that could be done.

INSERT INTO PLAYER 
VALUES
('00000001', 'Mike', 'Anderson', 182.00, 80.25, 'L', 'TE012345678' );

Importance of documentation

I didn’t mention this in previous article, but it is also very important to create documentation about every table that you create, especially in corporation standards. This is because:

  1. You can avoid making mistakes, like forgetting to add right values.
  2. After you have created tables it can be only you who knows which value means something, like in PLAYER-table the value ‘L’ stand for Left racket hand
  3. If someone later updates your tables he/she can study your documentation.
  4. Some standards might even require now a days it.

This documentation is also one part of conceptual schema. After creating all the key areas of the conceptual modeling (draws, documentation, relationships (1:1, 1:*, *:*)  etc.) the next step is about creating Logical modeling and parts of it requires knowledge about conceptual schema’s. In logical modeling there actual table relationships shows more effective, for example in relation *:* create a new table which will have both relations primary keys as it primary keys. Those keys are also foreign keys. After solving are the relationships every table is then normalized at least in 3NF state. In last step in Physical modeling actual SQL-language is used to create tables.

– Tuomas Törmä

Sources

  • Introduction to Database Systems lectures At Haaga-Helia autumn 2013 teacher Tanja Bergius.

Leave a comment