In this example I will show you how to create a simple table named my_quotes and insert new records with an SQL query using Microsoft SQL Server Studio Express Manager.
| quote_id | first_name | last_name | quote | subject |
| 1 | Anne | Sullivan | I am beginning to suspect all elaborate and special systems of education. They seem to me to be built up on the supposition that every child is a kind of idiot who must be taught to think. | education |
| 2 | Arthur | Koestler | Creativity is a type of learning process where the teacher and pupil are located in the same individual. | education |
| 3 | Edward | Everett | Education is a better safeguard of liberty than a standing army. | education |
| 4 | Albert | Einstein | Education is what remains after one has forgotten everything he learned in school. | education |
- Open Microsoft SQL Server Studio Express and connect with your SQL server
- Select the database on the server
- Click on the button New Query (View > Toolbars > Standard)
- Type the SQL query, as shown in the example below:
or copy and paste the following code:
quote_id int IDENTITY (1,1),
first_name varchar (90),
last_name varchar (90),
quote text,
subject varchar (70)
)
and click on the button Execute
Explanation
quote_id int IDENTITY (1,1)
quote_id is the name for the new column.
int(eger) is a data type. It specifies how long a number can be; other possibilites in SQL are bigint, smallint and tinyint (0 - 255).
IDENTITY means that this integer is a unique identifier for the record.
(1,1) The first number (1) before the comma is called the seed. This value is used for the first row inserted into the table. The second number (1) after the comma is the incremental value (+1) added to the next record inserted in the table. 1 is a standard value, for both the seed and the increment. It applies to the records even if it is not specified.
first_name varchar (90)
varchar (character varying) specifies the maximum number of characters (90) we want to use for this field. The maximum number of characters allowed for this data type is 8,000.
quote text
quote is the name for this new column
text is another data type. The maximum number of characters allowed is 2,147,483,647.
Insert new records in the table my_quotes
- Click on the button New Query
- Type the SQL query to insert your records in existing table called my_quotes
INSERT my_quotes (first_name, last_name, quote, subject)
VALUES ('Anne', 'Sullivan', 'I am beginning to suspect all elaborate and special systems of education. They seem to me to be built up on the supposition that every child is a kind of idiot who must be taught to think.', 'education')
INSERT my_quotes (first_name, last_name, quote, subject)
VALUES ('Arthur', 'Koestler', 'Creativity is a type of learning process where the teacher and pupil are located in the same individual.', 'education')
INSERT my_quotes (first_name, last_name, quote, subject)
VALUES ('Edward', 'Everett', 'Education is a better safeguard of liberty than a standing army.', 'education')
INSERT my_quotes (first_name, last_name, quote, subject)
VALUES ('Albert', 'Einstein', 'Education is what remains after one has forgotten everything he learned in school.', 'education')
The difference between Microsoft SQL and Oracle MySQL
If you know PHP and MySQL you will probably notice the difference in the example above when you insert multiple records. There are a few workarounds to make the code a bit shorter or look like MySQL, but it is not possible to insert multiple records with just one insert command, like you do with MySQL.
This is how a table, with same properties, is created in MySQL:
`quote_id` int auto_increment,
`first_name` varchar(90),
`last_name` varchar(90),
`quote` text,
`subject` varchar(70),
PRIMARY KEY (`quote_id`)
)
And this is how you can insert records with PHP into the MySQL table:
INSERT INTO `my_quotes` (`quote_id`, `first_name`, `last_name`, `quote`, `subject`)
VALUES
('', 'Anne', 'Sullivan', 'I am beginning to suspect all elaborate and special systems of education. They seem to me to be built up on the supposition that every child is a kind of idiot who must be taught to think.', 'education'),
('', 'Arthur', 'Koestler', 'Creativity is a type of learning process where the teacher and pupil are located in the same individual.', 'education'),
('', 'Edward', 'Everett', 'Education is a better safeguard of liberty than a standing army.', 'education'),
('', 'Albert', 'Einstein', 'Education is what remains after one has forgotten everything he learned in school.', 'education');