Clever use of SQL Server to edit the first 200 lines of functionality

In SQL Server 2005/2008/2008 R2, we can use SQL Server's own pre-editing 200-line feature, which is intended to simplify the use of update, allowing developers, DBAs and some people who do not understand T-SQL are able to directly modify data in the database through a visual interface.

In the use of the first 200 lines of editing functions, editing the first 200 rows of data, simple is simple, but it is easier to directly in the visualization of the page directly edit the data you want to edit, rather than open the editing interface automatically read out the 200, automatically read out the 200 order of clutter, Originally is a very useful powerful function, looks a little chicken.

By chance, it is much easier to modify the data by showing a small trick such as displaying the Criteria pane, displaying the SQL pane, reading the data you want to read, or sorting the data that is being read.

Talk less, give a real case.

There is a menu data table, which stores the system menu information, the table structure is as follows:

SELECT TOP [Functioncode]-- menu number, primary key ,[functionname]-- name ,[Functionurl]-- links ,[TYPE]-- type ,[DESCRIPTION]-- Description ,[OrderNo]-- Sort from [dbo] . [MENU]

After you open the database, use the edit first 200 lines function directly, as shown in:

650) this.width=650; "src=" Http://images.http//www.51cto.com/userupload/2010/9/201009012256299651.png "width=" 337 "height=" 194 "/>

Figure Edit Top 200 lines

Open later to read the data in the menu, as shown in:

650) this.width=650; "src=" Http://images.http//www.51cto.com/userupload/2010/9/2010090122564596692.png "width=" 645 "height=" 532 "/>

Figure Edit Top 200 lines

As shown, when you open the data in the menu, because the menu is not added to the data table at one time, it is not sorted according to the primary key column, which makes it inconvenient to modify the menu data directly using database data, not to put together the data order of a menu item. It was later discovered that you could use the Display Criteria pane to modify the order of the first 200 rows of data that were read by default, and open the Display Criteria pane in SSMs, as shown in:

650) this.width=650; "src=" Http://images.http//www.51cto.com/userupload/2010/9/2010090122570194563.png "width=" 339 "height="/>

Figure using the Display Criteria pane

Click "Show Criteria Pane" to open the interface as shown:

650) this.width=650; "src=" Http://images.http//www.51cto.com/userupload/2010/9/2010090122584693624.png "width=" 608 "height=" 489 "/>

Figure shows the Criteria pane

In the Display Criteria pane, we first sort by functioncode, and then sort by orderno, so that you can put all the data of a menu together, after the setup is complete, click Execute SQL, as shown in:

650) this.width=650; "src=" Http://images.http//www.51cto.com/userupload/2010/9/2010090122591091455.png "width=" 745 "height=" 233 "/>

Figure Execute SQL

After executing the SQL, the result is as follows:

650) this.width=650; "src=" Http://images.http//www.51cto.com/userupload/2010/9/2010090122595395326.png "/>

Chart menu data in sequential order

This allows us to modify the data in the way we want, and the data of a menu is put together to facilitate the modification of the menu data primary key and sorting.

In addition to using the Display Criteria pane, you can also use the Display SQL pane and click the Show SQL pane, as shown below:

650) this.width=650; "src=" Http://images.http//www.51cto.com/userupload/2010/9/2010090123020398497.png "/>

Figure shows the SQL pane

In the Display SQL pane, we can use T-SQL statements familiar to the developer and DBA to read the data, the read-out data can be modified directly in the results list, and can read more than 200 of the data, you can use the conditional statement and the order BY statement, in order to use the condition pane corresponding to the We use the following SQL statement to read the menu data.

SELECT TOP (functioncode), functionname, Functionurl, TYPE, DESCRIPTION, OrderNo From MENU ORDER by Functioncode ASC, OrderNo ASC

Here you can read up to 1000 menu data, and the result is exactly the same as the data you read using the Criteria pane, as shown in the following interface:

650) this.width=650; "src=" Http://images.http//www.51cto.com/userupload/2010/9/2010090123022299788.png "width=" 645 "height=" 507 "/>

Figure using the Show SQL pane

In addition to using the Display Criteria pane, displaying the SQL window, you can also use the Display view pane, which is a visual interface for what is not familiar with T-SQL, and it is also quite comprehensive to facilitate access to SQL statements.

Summary

on the database product, most people always preferORACLE,MYSQL, preferenceORACLEis becauseOracelthe stability and functionality of the powerful, is to usePl-sqlThere are a lot of easy tools to use;MYSQLbecause this guy is half free, small and cost-effective. SQL ServerBut very few praise, although the function is also very strong, but the voice of criticism constantly. But, through the introduction of this little trick, gradually understandSQL Serverfunction is not as weak as we think, but there are many good things, tricks we did not find and master. Compared toSQL Serverto theSQL Server 2005,5~6only one version of the year,SQL Server 2005to theSQL Serverthis time has been shortened to3years, this is usedSQL ServerThe Gospel of the user. The shortening of the product upgrade time also brings some problems: such as compatibility, upgrade cost, new features and so on, these are the factors that need to be considered, this article just stand in the pure user angle to look at this problem.

This article is from the "Saab √ Martini" blog, please be sure to keep this source http://hfcombo.blog.51cto.com/9089438/1606221

Clever use of SQL Server to edit the first 200 lines of functionality