SQL 14-edit data

Data editing in the database is the most frequent task we have encountered. In this article, I will summarize the most commonly used data editing.

　　

Select

We often encounter a situation where we want to create a new table. The data in the table comes from the original table. The original table, but the data in this table is already very messy, we want to back up the data, clear the data in the original table, and re-import the data. In these cases, we can use the select into statement.

If you have the permission to create a table, you can use the keyword "into" in the select statement to create a new table in the database. This statement only needs to add into new_table_name to the selected column name. Before the from keyword, of course, the data can come from one or more tables. It should be noted that the new table only contains the data queried in the select statement, without any keys, constraints, and relationships. The following example:

select top(10) *
into new_intervalwaitssample
from IntervalWaitsSample
select * from new_intervalwaitssample

This statement places the first 10 rows of data in the IntervalWaitsSample table in a newly created new_intervalwaitssample table.

Insert into select

Now we have this new table. If we want to select some data from the original table IntervalWaitsSample and insert it into this new table, we need to use the insert into select statement. The following example:

insert into new_intervalwaitssample
select * from IntervalWaitsSample where wait_type='ASSEMBLY_LOAD'

This statement inserts all the wait_type = 'assembly _ load' data in the original table IntervalWaitsSample into the new table.

　　

Insert into select union

What should we do if we want to write some known data into the target table at one time? In this case, the union statement is very useful. We can use the insert into select union statement, note that this statement is similar to the insert into select statement. The difference is that the select statement later uses union to connect multiple select statements.

insert into select value11 , value12 , value13 

union select value21 , value22 , value23 

union select  value31 , value32 , value33 

… …

The following is an example:

insert into new_intervalwaitssample
select 'ASYNC_NETWORK_IO','2011-05-09 16:50:00.973','0.00' union
select 'ASYNC_NETWORK_IO','2011-05-09 17:00:00.020','0.00' union
select 'ASYNC_NETWORK_IO','2011-05-09 17:15:00.123','0.00' union
select 'ASYNC_NETWORK_IO','2011-05-09 17:25:00.190','0.00'

　　

Use table value Constructors

We can also directly use the table value constructor. The Code is as follows:

insert into new_intervalwaitssample
values 
('ASYNC_NETWORK_IO','2011-05-09 16:50:00.973','0.00'),
('ASYNC_NETWORK_IO','2011-05-09 17:00:00.020','0.00'),
('ASYNC_NETWORK_IO','2011-05-09 17:15:00.123','0.00'),
('ASYNC_NETWORK_IO','2011-05-09 17:25:00.190','0.00')

　　

Cut data from the table

In another case, assume that we want to delete several pieces of data from a table, and then place the deleted data in another table. This action is like cutting and pasting. However, in this case, you must note that you can only cut data from the user table, but not from the view. Its syntax is as follows:

Delete sourcetable
output deleted.value1,deleted.value2,deleted.value3
into targettable
from targettable where … …

Below is the instance

delete WaitStats
output 
deleted.dt,
deleted.wait_type,
deleted.waiting_tasks_count,
deleted.wait_time_ms,
deleted.max_wait_time_ms,
deleted.signal_wait_time_ms
into new_waitestats
where WaitStats.wait_type='ABR'

Note that there is a rare keyword deleted, which is the alias of the table to be deleted. this is a bit like the this keyword in the class.

　　

Visible editing

If we are developing applications, I believe most people prefer to use the graphic interface in SQL Server Management Studio to operate data. After all, these powerful functions are provided. Why not use them. I have mentioned that Oracle can directly modify the data selected by SQL statements. At that time, I asked why Microsoft SQL Server did not provide such powerful functions. Haha, today I found that SQL Server still provides such a function. However, this modification can only be performed on a single table. If the query statement contains an inner join or other estimates, it will not work. Don't talk nonsense ,:

1. Right-click the table to be modified and select Edit top 200 rows.
2. When the editing interface is opened, the SQL server will have an additional tag, move the mouse to the tag, then slide to the Panel, then slide to the SQL, and click SQL, you can also right-click on the editing page. 1
   
   Figure 1
   In this case, you can write the following query statements for the data you want to modify in the text area:

   SELECT     TOP (1000) dt, wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
   FROM         new_waitestats
   WHERE     (wait_type = 'ABR')

3. Here we want to modify the wait_type = 'abc' data, click the query button, and all the qualified data can be seen in the following table. Then we can modify the data, note that after modification, press the Enter key to write it to the database. Isn't it very lazy to do this? You don't need to write an update statement and modify it directly.

　　

Copy and paste data using the editor

In another case, you can use the editor to replace the insert into select statement above. If the fields in the two tables are identical, We can query the data from one table and copy the data to another table.

1. First click the beginning of the Data row, select the entire row, and then click copy2
   
2. Right-click the second table that you want to insert data to, drag the scroll bar to the end, until all rows are empty, right-click paste, and press Enter, in this way, a piece of data is inserted. 3
   

These are all seen at work, and I find it interesting to record them here. These skills are not worth mentioning when the database technology experts come. I hope the experts will not laugh at me.