There is an XML file with 1000 lines, where there is user data and new activation key values ​​for them, how to add this data to the MS SQL where these users already exist, and you just need to replace the old key values ​​with new ones

  • What version of SQL Server do you have and what tools is it permissible to use, is there, for example, the possibility (and necessity) to write a utility for this? - Serafim Prozorov
  • ms sql server 2008 r2, you can write a utility, I would use bulkinsert, but it massively loads only new values, how to make it add values, according to the user, I did not find - Aizen Souske

1 answer 1

And what is the usual UPDATE from XML variable for you? 1000 lines is not such a figure to think about some tricky optimization ..

For example, I put a simple script:

 SET STATISTICS TIME OFF IF OBJECT_ID('tempdb..#Users', 'U')IS NOT NULL DROP TABLE #Users;

- we create a tablet with half-ezdatel

 CREATE TABLE #Users( Id INT IDENTITY(1, 1), Value UNIQUEIDENTIFIER )

--Add 100,000 users

 ;WITH CTE100000 AS(SELECT 1 N UNION ALL SELECT N+1 FROM CTE100000 WHERE N<100000) INSERT #Users SELECT NEWID() FROM CTE100000 OPTION(MAXRECURSION 0) DECLARE @xml XML

- generate xml with 1000 lines

 ;WITH CTE1000 AS(SELECT 1 N, 100 Id UNION ALL SELECT N+1, Id + 100 FROM CTE1000 WHERE N<1000) SELECT @xml = ( SELECT N [@Id], NEWID()[@Value] FROM CTE1000 FOR XML PATH('row'), ROOT('root') ) OPTION(MAXRECURSION 1000) SET STATISTICS TIME ON

- we update data from xml

 UPDATE U SET Value = T.Value FROM( SELECT Tcvalue('@Id', 'int') as Id, Tcvalue('@Value', 'UNIQUEIDENTIFIER') as Value FROM @xml.nodes('/root/row') T(c) )T INNER JOIN #Users U ON U.Id = T.Id OPTION(FORCE ORDER)

Result:

 /* Время синтаксического анализа и компиляции SQL Server: время ЦП = 63 мс, истекшее время = 126 мс. Время работы SQL Server: Время ЦП = 15 мс, затраченное время = 16 мс. (строк обработано: 1000) */

As you can see the update of 1000 lines is less than 200ms. Where are you faster?

The only thing I would like to mention is the mandatory index by # Users.Id and the guaranteed user connection order in the UPDATE , specified by the FORCE ORDER option, in order to use this index correctly. (although the server here itself would be handled)

UPD: or do you have a problem in order to read the xml from the file and transfer it to the server? Parameter stored procedure, for example.

UPD: if the xml file contains data about users that are not in the user table, you can add them with an INSERT , like this:

 INSERT #Users(Id, Value) SELECT T.Id, T.Value FROM( SELECT Tcvalue('@Id', 'int') as Id, Tcvalue('@Value', 'UNIQUEIDENTIFIER') as Value FROM @xml.nodes('/root/row') T(c) )T LEFT JOIN #Users U ON U.Id = T.Id WHERE U.Id IS NULL OPTION(FORCE ORDER)

To update old and insert new ones with one query, you can use the MERGE operator. Study at least for the sake of academic interest, you may like it.

  • The fact is that there are about 100 thousand records in the table itself, the xml file has only full name and others in it, and according to this data it is necessary to check where to insert the code, and I don’t understand how to do it ... - Aizen Souske
  • @AizenSouske, so what? Add an index by full name and others. The speed of the number of rows in the table will not change. It’s all a matter of the number of lines in the xml file, for each line in the xml file the server, by index in the user table, searches for the line to be updated. I changed the number of users per 100,000 in my script. The analysis of the plan became a little longer fulfilled, but the query execution time remained less than 20ms. I repeat the question. Where are you less? :) - pegoopik
  • Thank you, I will try now, the processing time of requests did not play a role, it is in understanding how to do, in general, thanks again =) - Aizen Souske
  • @PavelMayorov, where did you get that request for synchronization with xml one? What makes you think that this index is not yet? What makes you think that this index is not useful for other user search tasks? It is a natural index. - pegoopik
  • @pegoopik chot I could not figure out this code, I have an xml file on my computer separately, the base is on the server, there is access to the server. Why generate another new xml during the creation of the table, and if there is no way without it, then how to insert the data from my xml there so that it updates them ... - Aizen Souske