How can I pass the retrieved results to a temporary SQL table?

Http://topic.csdn.net/t/20050403/20/3905378.html
How can I pass the retrieved results to a temporary SQL table? Jerry_liu (formulas) Question in. Net/VB. NET at 20:40:32

I modified N rows of records in the DataGrid in VB.net. After selecting the modified records, I want to update the table in the SQL statement. Someone suggested that I first exist in the temporary table and then store it in another way.
In a real table, if insert is used to store one row of records into a temporary table, if there are ten rows, isn't it necessary to execute ten stored procedures? Is there a simpler and more effective way?
Then, can the data in the temporary table be updated using an update statement? Or do I have to confirm the ID in one row before updating?
Number of questions: 20. replies: 7top

Harryho (kiluya) on the first floor)The score is returned at 22:36:45 ,. 7

If you are using SQL Server, I suggest using trigger. if you use trigger, there will be a sweet table named inserted to store records for modifying and inserting tables. you can use the records of this table to update your table.

Create trigger trigger_name on table_name
For update, insert
Update table set field name = from inserted where ....


Top

Hamadou on the second floor (zhufeng-Striving for the ideal)The score is returned at 08:28:22 ,. 7

Stored in a temporary table. If you use a stored procedure, you need to record how many times it is called cyclically. However, you can use an SQL statement in the latest table, such as insert into table_name select # temp_table.name, # temp_table.age, and so on.

Terryshi (terryshi) on the third floor)The score is 0 at 08:34:31.

Select * into # temp_table form AAAA
The definition of a temporary table is the same as that of a normal table. The difference is that the temporary table is automatically deleted after the access is completed.
# XX indicates a local temporary table, which is deleted after one query.
# XX indicates a global temporary table, which is deleted after a connection, almost sqlconnection. Close (). Top

4 floor jerry_liu (formulas)The score is 0 at 09:36:48.

To: Hamadou (zhufeng)
Because several of my records may be changed to the original data, and several are newly added, When I update the data from a temporary table to the database, I cannot insert it one by one. (My current idea is to use datarow. rowstate in VB.net to determine)

When saving records to a temporary table. use sqlcommand to execute the stored procedure. One procedure is used to create a temporary table, and the other is used to add a record. If there are 10 records, I have to execute sqlcommand. executenonquery () ten times?
I am also worried that I have created a local temporary table. Will this table no longer exist when I execute the second sqlcommand?

To: harryho (kiluya)
The trigger has never been used. I'm learning. Thank you for replying to top.

Hamadou on the fifth floor (zhufeng-Striving for the ideal)The score is 0 at 09:43:50.

You can determine the row status, or use dataview (to obtain the added row). Generally, if you want to insert a row into the temporary table, you have to do one row. So how many calls are required, and 10 calls are required for 10 calls. For usage of temporary tables, see
Terryshi (terryshi) said. Top

Foxbuilder on the 6th floor)The score is returned at 10:04:30 ,. 6

Generally, you can use temporary tables or triggers.
I personally think that the trigger is easier to use, but it is too complicated for copying and updating (if each record needs to process different logic code, I usually use a temporary table.
For your question

Q:
Because several of my records may be changed to the original data, and several are newly added, When I update the data from a temporary table to the database, I cannot insert it one by one. (My current idea is to use datarow. rowstate in VB.net to determine)


A: update includes (ADD, modify, delete), datarow. rowstate
Records can indicate that the row status is (added, modified, or deleted ),
Temporary table structure. Besides the structure of the source table, I add an nrowstate (smallint) field to save
Rowstate.

Note that when you traverse each datarow, you will find that the rows whose datastate is deleted cannot be directly accessed by you. In this case, the method I used is


If dr. rowstate = datarowstate. Deleted then
Isdelete = true
Dr. rejectchanges ()
End if
Add this row to the temporary table
If isdelete then
Dr. Delete ()
End if


Q:
When saving records to a temporary table. use sqlcommand to execute the stored procedure. One procedure is used to create a temporary table, and the other is used to add a record. If there are 10 records, I have to execute sqlcommand. executenonquery () ten times?
I am also worried that I have created a local temporary table. Will this table no longer exist when I execute the second sqlcommand?

A:
Dim conn sqlconnection = getconn ()
...
Sqlcommand. executenonquery (...) 'create a temporary table
Dim Dr as datarow
For each DR in DT. Rows
Sqlcommand. executenonquery (...) 'insert each row into the temporary table ....
End

Ensure that each sqlcommand uses the same connection. The temporary table will be valid.

As for the call efficiency, I don't think you need to worry about it. A few hundred records at a time should not feel too much.










Dim conn as sqlconnection = getconn

Top

Jerry_liu (formulas) on the 7th floor)The score is 0 at 14:36:21.

If, according to foxbuilder, a row is deleted, the row is first deleted from the temporary table, and then imported to the original table from the temporary table. Then, all records are read into the temporary table first, delete the record to be deleted. After the operation, overwrite all records of the original table.

It would be okay if there are not many records in the table, but if there are tens of thousands of records.

If I only extract the record IDs that are added, updated, and Dele and store them directly to the original table, isn't it missing an intermediate link? Why use a temporary table for data transfer?