Tag Info

New answers tagged

1

You already inserted 'DAMASCUS' and 'Alepo' values and they are unique. SQL server is rejecting data according to your rules. Try: SELECT * FROM governorate WHERE name = 'DAMASCUS'; You will found record is already there.


0

You should insert hash as a parameter following this example: http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html Something like this insert_stmt=( "insert into meanu (username,pwdhash1)" " values('BV2',%s)" ) data=(hash) cursor.execute(insert_stmt, data)


1

Last Sentence of MySQL Documentation on Traditional InnoDB Auto-Increment Locking says You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter. Therefore, if the second INSERT had failed and rolled back, it makes the gap. You may have to set ...


0

I failed to understand the extent of the transactions. Before I do the INSERT, I do a write-locking select on the external index value in both transactions. I would have expected T2 to block T1 at that point, but that is not the case, both transactions successfully acquire a lock on the gap. Neither transaction can promote its lock to insert intention; T1 ...


0

Please look carefully at the query and their locks Both INSERT queries are trying to INSERT a new row at space id 18 page no 1103450 n bits 320 Since both queries are doing INSERTs each must have an auto_increment ID created each ID must be attached to a new BTREE entry being written into the index In light of this, there has to be some lock engaged ...


2

Simple , just try to adapt the following code insert into prices (group, id, price) select 7, articleId, 1.50 from article where name like 'ABC%';


3

If the (subquery) has SELECT (whatever expression) AS col FROM ..., then you can do: INSERT INTO mytable (col1, col2, col3, col4) SELECT val1, s.col, val2, val3 FROM (subquery) AS s ; or: WITH s (col) AS (subquery) INSERT INTO mytable (col1, col2, col3, col4) SELECT val1, s.col, val2, val3 FROM s ;


1

After thinking about this, I decided to answer instead of comment to provide more detailed information. Yes, an INSERT statement can use an OUTPUT clause. It can be specified before the VALUES clause. See the SQL Server Books Online (http://technet.microsoft.com/en-us/library/ms174335.aspx) for the authoritative T-SQL reference I suggest you avoid ...


0

MERGE has more flexible OUTPUT. OUTPUT can refer to the merge source which is handy if you want the client to be able to match what it sent to what was actually inserted (e.g. IDENTITY values). INSERT can't do that (for no fundamental reasons; seems to be not implemented). I can't think of any performance difference. The plans certainly look so similar that ...


0

Can't comment yet, so posting as answer - you state in one comment that "not sure if there are ever duplicates within a given document anyway" - in that case (and in most other cases too) you should use INSERT ... ON DUPLICATE KEY UPDATE as it does not need to delete the existing row, so update overhead is only seen when the target row actually exists. ...


2

Your best bet will be to use SSIS or BULK INSERT. There are various performance improvements that you can do when using them and they are very well documented in The Data Loading Performance Guide. At SSIS level, you can look into below things to speed up data read and data load : Fast Parse Option along with its limitations. Use the SQL Server Native ...


0

You may use REPLACE INTO. The disadvantage of it is that it creats high IO, as each existing record will be deleted and then inserted (as opposed to being updated). Try loading the new rows' IDs into a separate table on the destination server, then run a delete on the destination joining this new table with the existing table using the ID. After that you ...


0

Linked servers aren't usually a good idea from a security perspective. See distribution statistics section here I think SSIS would provide a better way of doing this.


1

Parse the JSON when the record in inserted, not later. (OK, maybe you need to collect the JSON in a "staging" table between arrival and processing.) When you parse the JSON, extract only the fields that you need to manipulate/search/etc in MySQL. Be conservative. Don't extract many fields. Leave the JSON as is in case you need the details later. It ...



Top 50 recent answers are included