StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Inserting data from staging table to two other tables
text
Body
copied!<p>I have a situation in which a CSV is uploaded to my application and each line essentially needs to be put into the database. I read each line and build a data table and then SqlBulkCopy that table up to a staging table.</p> <p>The staging table looks like this:</p> <pre><code>UserID, GroupID, FirstName, LastName, EmailAddress </code></pre> <p>I have three other relevant tables in the database. A contacts table, a groups table and a contacts to groups mapping table.</p> <pre><code>Contacts: ID, UserID, FirstName, LastName, EmailAddress Groups ID, UserID, Name, Description ContactGroupMapping ID, ContactID, GroupID </code></pre> <p>The ContactGroupMapping table simply maps contacts to groups. Hopefully the staging table now makes sense, it holds the details of each imported contact plus the group they should also be mapped to.</p> <p>My plan was to run a query against the database after the SqlBulkCopy to move the data from the staging table to the Contacts and ContactGroupMapping tables. Currently, I have a query looking something like this:</p> <pre><code>INSERT INTO Contacts (UserID, FirstName, LastName, EmailAddress) SELECT DISTINCT [t1].UserID, [t1].EmailAddress, [t1].FirstName, [t1].LastName FROM ContactImportStaging as [t1] WHERE NOT EXISTS ( SELECT UserID, EmailAddress, FirstName, LastName FROM Contacts WHERE UserID = [t1].UserID AND EmailAddress = [t1].EmailAddress AND FirstName = [t1].FirstName AND LastName = [t1].LastName ) </code></pre> <p>So, my problem is that while this inserts all the distinct contacts into my contacts table, I then have no way to add the associated row to the mapping table for each newly inserted contact. </p> <p>The only solution (probably because I suck at SQL) I can come up with is to have an extra nullable field in the contacts table identifying the group that the contact is to be associated with and insert this too. Then I could run a second query to select all contacts with a value in this column and insert into the mappings table.</p> <p>Any thoughts on how this sort of thing should be achieved most efficiently?</p> <p>Thanks.</p> <p><strong>Edit:</strong> To elaborate on the object model: There are any number of Contacts and any number of Groups. A contact can be in a group by way of an entry in the ContactGroupMapping table. One contact can be in any number of groups. At the database level, this model is the concern of the three tables <strong>Contacts</strong>, <strong>Groups</strong> and <strong>ContactGroupMapping</strong>. I'm needing to move one row from the staging table and create two rows; one in the Contacts table and one in the ContactGroupMapping table.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload