INSERT SQL

3 Key excerpts on "INSERT SQL"

• 

  eBook - ePub

  CompTIA DataSys+ Study Guide

  Exam DS0-001

  • Mike Chapple, Sharif Nijim(Authors)
  • 2023(Publication Date)
  • Sybex

    (Publisher)

  Data Manipulation Language (DML) is the flavor of SQL that lets you manipulate data within a database. While DDL creates database objects, DML modifies the data those objects contain. You can think of DML as the tools used to move things around inside a building once the building is complete. Just as a person uses tools to move furniture or hang artwork on a wall, you use DML to create, modify, or delete data within the tables defined by DDL.

  Create, read, update, and delete are the primary DML operations. As a result, you may see DML referred to as CRUD operations: Create, Read, Update, and Delete. Table 3.2 illustrates each operation and its corresponding SQL verb. INSERT, UPDATE, and DELETE all change data at rest in a table. SELECT is a read-only operation that retrieves data from a table.

  TABLE 3.2

  Data Manipulation Language Operations

  DML Operation	SQL Verb
  Create	INSERT
  Read	SELECT
  Update	UPDATE
  Delete	DELETE

  INSERT

  The INSERT command creates new rows in a table. INSERT requires that you specify a target table and the values you want to place into each column. Optionally, you can specify the columns within the table for each value you want to insert. Figure 3.3 shows the syntax of the INSERT statement.

  FIGURE 3.3

  SQL INSERT syntax

  The following INSERT statement adds Connor Sampson's information from Table 2.1 into the Customer table from Figure 3.1 .

  INSERT INTO customer (customer_id, title, first_name, middle_name, last_name, date_of_birth) VALUES (1, 'Mr', 'Connor', 'Andrew', 'Sampson', '2001-03-02');

  You do not need to specify the column parameters when inserting a row that contains data for every column. However, identifying columns is a good practice. When inserting data into a subset of columns, it is easiest to identify the target columns. Suppose you have a customer named Anitej Gupta with no middle name. The following INSERT statement adds Anitej to the Customer table from Figure 3.1

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Modeling Step by Step

  • Gavin Powell(Author)
  • 2020(Publication Date)
  • Auerbach Publications

    (Publisher)

  This chapter shows how the relational database model is used from an application perspective. There is little point in understanding something such as relational database modeling without seeing it applied in some way, and so this chapter looks at how a database model is accessed by applications. A relational database model contains tables, where rows in tables are accessed using a computer programming language called Structured Query Language (SQL). SQL is a declarative programming language that is used to read data from tables and update data in those tables.

  A declarative programming language describes what you are coding without describing how the problem is solved, which can be difficult to understand, because declarative code requires all instructions in a single complex instruction. Other computer programming languages such as C and FORTRAN are procedural or modular and break software down into separate parts called modules; and then there is Java, which is object-oriented.

  When a database model is well designed, the creation of SQL code can be a simpler process, which also can imply that difficulties encountered in coding of SQL queries can often indicate database model design flaws, or just too much granularity in a data model.

  This chapter covers:

  •  What SQL is •  The origins of SQL •  Different types of queries •  Changing data in tables •  Changing table structure

  4.1    What is SQL?

  SQL is a declarative computer programming language used for accessing row and column values in relational database tables:

  •  SQL is structured in the sense that it is used to access structured data from tables in a relational database. •  Use of the word “language” implies a computer programming language that has specific syntax for performing specific tasks.

  •  SQL is a declarative language consisting of single commands where the database itself does a lot of the work in deciding how to get that information. In other words, SQL tells the database what it wants, and the database does some of the logical assessment, because logic is built into a relational database using keys and relationships between tables. A procedural language, on the other hand, contains blocks of commands, where those blocks of commands are sequences of distinct steps, typically where each successive step is dependent on the result of the previous step (command) in the sequence.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning ASP.NET 4.5.1: in C# and VB

  • Imar Spaanjaars(Author)
  • 2014(Publication Date)
  • Wrox

    (Publisher)

  Figure 12-15 .

  In addition to selecting data, you also need to be able to insert data into the database. You do this with the INSERT statement.

  Creating Data

  To insert new rows in a SQL Server table, you use the INSERT statement. It comes in a few different flavors, but in its simplest form it looks like this:

  INSERT INTO TableName (Column1 [, Column2]) VALUES (Value1 [, Value2])

  Just as with the WHERE clause, you need to enclose string and date values in single quotes, but you can enter numbers and boolean values directly in your SQL statement. The following snippet shows how to insert a new row in the Genre table:

  INSERT INTO Genre (Name, SortOrder) VALUES ('Tribal House', 20)

  The Id column of the Genre table is set up to generate a value automatically when you insert a new row (you see more of this concept, called identity columns, later in this chapter). Because it’s generated by SQL Server, it’s not part of this query. After you have created some data, you may want to edit it again. You do this with the UPDATE statement.

  Updating Data

  To update data in a table, you use the UPDATE statement:

  UPDATE TableName SET Column1 = NewValue1 [, Column2 = NewValue2] WHERE Column3 = Value3

  With the UPDATE statement, you use Column = Value constructs to indicate the new value of the specified column. You can have as many of these constructs as you want, with a maximum of one per column in the table. To limit the number of items that get updated, you use the WHERE clause, just as with selecting data as you saw earlier. Without a WHERE clause, all rows will be affected which is usually not what you want.

  The following example updates the row that was inserted with the INSERT statement you saw earlier. It sets the Name to Trance and updates the SortOrder to 5 to move the item up a little in sorted lists. It also uses the unique ID of the new row (13 in this example) in the WHERE clause to limit the number of rows that get affected with the UPDATE

  Sign up to read

  Learn more about book