I have a table where records can refer to other records, e.g. a record could be (id, data, cross_id) where cross_id is the id of another record. these entries are always inserted as pairs.
right now I insert a new record (C# here) and return it's id with scope_identity. then I insert the second record of the pair with cross_id set to the id of the first record, and get the second record's id back. lastly I update the first record's cross_id, setting it to the id of the second. so I have to insert, insert, and update to add the pair of records.
this works, but it's slow when I have many pairs to insert. I'd prefer to do a bulk insert (BulkCopy in .net parlance), but the problem is I have to insert first to get the id, so it seems the only way to do this is individually. alternatively, I could manually create values for the id fields instead of using auto_increment, but this seems hacky...anyone have a better way to do this?
That's a pretty odd requirement, and makes me question your table design.
Many times, challenging SQL problems arise out of faulty schemas. I'd love to see you post the DDL for the table.
Regardless, if you are need to be able to insert records in pairs that reference eachother, you get more efficiency by doing this in a single stored procedure call rather than three trips to the database.
If it's not practically useful, then it's practically useless.