Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use the SQL transaction manager to create a new table", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "how to use the SQL transaction manager to create a new table" bar!

Create a new table using the SQL transaction manager

You can create a new table using the methods described in the previous sections. However, it is easier to create a new table using the transaction manager. This section describes how to use this program to create a new table.

Select SQL Enterprise Manager from the SQL Sever program group on the taskbar, start the program, and you will see the window shown in figure 10.4. Browse the tree structure in the Service Manager window and select the folder named Database. After opening the folder Database, select what you created in Chapter 3.

Note:

If you haven't created your own database yet, go back to Chapter 3 to create it. You should never add data to master,tempdb or any other system database.

After selecting the database, you will see a folder named Group/users and a folder named objects. Open the folder objects and you will see many folders, one of which is called Tables. Right-click the folder Tables and select New table, and the window shown in figure 10.5 will appear.

You can use the Manager Tables window to create a new table. The Manager Tables window has seven columns: Key,Column,Name,Datatype,Size,Nulls and Default. Each row in the Manager Tables window indicates the information of a field in the table.

Figure 10.4

10.5

To create a new table, you need to enter at least one row of information. Type mycolumn under the column named Column Name. Next, select the Datatype column and select CHAR from the drop-down list. When you enter information in these two columns, the window will look like figure 10.6.

Figure 10.6

You have created a simple table with only one field. Click the Save button to save the new table. When asked to enter the name of the new table, type mytable and click OK. Now this table has been saved to your database.

If you open the folder Tables in the service manager window, you will see that the new table you created is listed. You can double-click the chart of the table to edit it, and the Manager Tables window will reappear, and you can add new fields and save again.

You can do all the work you can do with the SQL transaction manager with SQL statements. However, the transaction manager makes the table creation process easier.

Add data to the table

The next chapter discusses how to insert data into a table using SQL. However, if you need to add many records to a table, it is inconvenient to enter data using SQL statements. Fortunately, Microsoft SQL Sever comes with a client application called Microsoft Query, which makes it easy to add data to the table.

Start the Microsoft Query program located in the SQL Sever program group of the taskbar. Select File | New Query from the menu at the top of the window. A Select Data Source dialog box appears (see figure 10.7). Select your data source name and click Use.

Figure 10. seven

After entering your login account and password, the program requires you to select a table and a database. Select the mytable you created in the previous section, click the button Add, and then click the button Close to close the dialog box.

A dialog box appears in the upper-left corner of the window with a column of field names taken from the table mytable. You can double-click any field to add it to the main window. If you double-click the asterisk (*) character, all fields will be added to the main window.

If you have records in your table, they now appear under the field title in the main window. However, because you have just created this table, the table is still empty. To add a new record, select Records | Allow Editing, and a new record will appear in the main window. Enter a row of data to complete the record, and a new record is added to the table.

Figure 10. eight

When you go to the next new record, the value you entered into the previous record is automatically saved. If you want, you can use Microsoft Query to enter hundreds of records into the table.

Delete and modify tables

You should design tables carefully before creating them, because you will be very limited when you change an existing table. For example, once you have created a table, you cannot delete the fields in the table or change the data type of the fields. All you can do in this case is to delete the table and start all over again (see the section "creating records and tables using SQL" in Chapter 11, Intermediate SQL).

To delete a table, you can use the SQL statement DROP TABLE. For example, to completely delete the table mytable from the database, you would use the following statement:

DROP TABLE mytable

Warning:

Be careful when using the DROP TABLE command. Once a table is deleted, you will not be able to restore it.

When you build a site, you probably need to enter test data into the database. And when you are ready to provide your site to the world, you will want to clear the test information from the table. If you want to clear all the data in the table without deleting the table, you can use the TRUNCATE TABLE statement. For example, the following SQL statement removes all data from the table mytable:

TRUNCATE TABLE mytable

Although you cannot delete or modify existing fields, you can add new fields. The easiest way to do this is to use the Manager Tables window in the SQL transaction manager. You can also use the SQL statement ALTER TABLE. Here is an example of how to use such a statement:

ALTER TABLE mytable ADD mynewcolumn INT NULL

This statement adds a new field, mynewcolumn, to the table mytable. When you add a new field, you must allow it to accept null values, because there may already be many records in the table.

Thank you for reading, the above is the content of "how to use the SQL transaction manager to create a new table". After the study of this article, I believe you have a deeper understanding of how to use the SQL transaction manager to create a new table. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.