Creating a new table in the local MS SQL Server database located in the “* .mdf” file

Creating a new table in the local MS SQL Server database located in the “*.mdf” file

This topic provides an example of creating a new table in the local database, which is implemented in the “*.mdf” file. The database is designed to work in the management system of relational databases (RDBMS) Microsoft SQL Server.


Contents


Search other websites:

 




Task

The database “MyDataBase.mdf” is given. Create in this database a table with the name Source, which has the following structure.

Field name Type Explanation
ID_Source int Unique identifier
Name char[50] Source Name
Address char[100] Address

 


Instructions

1. Run MS Visual Studio. Preparing the database

Run MS Visual Studio.

Using the Server Explorer utility, connect the “MyDataBase.mdf” database to the MS Visual Studio system. Download the archive of the finished (previously created) database here.

The process of connecting the database to future projects in MS Visual Studio is described in more detail in the article:

 

2. Creating the ‘Source’ table

So far, the database does not contain any elements: tables, views, diagrams, etc. (figure 1).

Visual Studio database initial state

Figure 1. Database “MyDataBase.mdf” – initial state

To create a table, use the command “Add New Table” from the context menu, which is called by clicking the right mouse button on the “Tables” element of the database “MyDataBase.mdf” (Figure 2).

SQL Server command add new tableFigure 2. The command to add a new table “Add New Table”

This will open the window for creating table fields (Figure 3).

SQL Server database table fieldFigure 3. Window for creating table fields

The purpose of the fields in the window is as follows:

  • field Column Name – contains the names of the fields of the table that is created. In our case, this field can contain the names “ID_Source”, “Name” and “Address”;
  • “Data type” – contains the data type, which is specified for any field;
  • “Allow Nulls” specifies whether cells can contain NULL values. The NULL value for the cell means that the data was not entered (data not available).

After filling in the names of the fields, the types of the table entry window will have the view, as shown in Figure 4.

SQL Server table type nameFigure 4. Setting table names and their types

Field names and their types are specified in accordance with the condition of the task.

The ID_Column field does not contain the “Allow Nulls” option, since this field can be a unique counter in the future. In this regard, the type of the field ID_Column is selected as an integer (int).

After clicking on the close button of the table input window (or saving), you must specify the Source name of the newly created table (Figure 5).

SQL Server database table nameFigure 5. Entering the table name

After the performed actions, a table will be created with the name Source (Figure 6).

SQL Server database table figureFigure 6. The newly created ‘Source’ table

 

3. The work with the ‘Source’ table

After creating the table, the system offers several commands for operating the table data and editing the table structure.



3.1. The command to edit the structure of the table “Open Table Definition”

If you need to rebuild the table structure (number of fields, field names, field types, etc.), you need to do the following:

  • open the ‘Tables’ tab in the ‘MyDataBase.mdf’ database (Figure 6);
  • in the ‘Tables’ tab, right-click with the mouse, and from the context menu call the “Open Table Definition” command (Figure 7). This opens the field definition table (Figure 8).

Visual Studio table definition commandFigure 7. The command “Open Table Definition”

SQL Server table structure figure

Figure 8. The ‘Source’ table in the structure editing mode

After editing the structure of the table, it needs to be closed and saved.

 

3.2. The command to input data into a table

The data in the table can be entered manually or programmatically.

If you need to enter data, you should do the following:

  • call the context menu by right-clicking on the ‘Source’ table (Figure 9). As a result, a window opens for entering data into the table (Figure 10);
  • use the keyboard to enter data (records) in the ‘Source’ table (Figure 10).

After you finish entering data, the table can be closed. The data will be automatically saved.

If one of the fields allows the input of NULL values (the “Allow Nulls” option was enabled in the table structure), you can avoid enter values to such cells. Otherwise, the system will issue warning messages.

SQL Server database data tableFigure 9. The “Show Table Data” command

SQL Server input data tableFigure 10. Input data in the ‘Source’ table

 


Related topics