SQL Insert Using Records From Another Insert

Question

I have 3 tables, as follows:

tblAgentVisit (VisitID auto-increments)
VisitID (PK) | StatusID | A bunch of other columns
--------------------------------------------------
1            | 1        | etc.
2            | 1        | etc.

tblAgentVisitAgents
VisitID | AgtID | Prime
-----------------------
1       | 8507  | 1
2       | 56    | 1

tblAgentVisitLoad
AgtID
-----
2077
3068
432

Every quarter, we take our top 20% of agents, and load their IDs into tblAgentVisitLoad.

I need to create a new, unique visit for each agent in the table. I need to INSERT my StatusID and other columns to tblAgentVisit, grab the auto-incremented VisitID (using OUTPUT?), then INSERT VisitID, the AgtID, and Prime = 1 into tblAgentVisitAgents. Again, each visit has to be unique for each agent.

Ideally, this is how my tables would look when I'm done:

tblAgentVisit (VisitID auto-increments)
VisitID (PK) | StatusID | A bunch of other columns
--------------------------------------------------
1            | 1        | etc.
2            | 1        | etc.
3            | 1        | etc.
4            | 1        | etc.
5            | 1        | etc.

tblAgentVisitAgents
VisitID | AgtID | Prime
-----------------------
1       | 8507  | 1
2       | 56    | 1
3       | 2077  | 1
4       | 3068  | 1
5       | 432   | 1

Anyone have any suggestions for doing this within MS SQL Server 2005? I'm totally stumped.

If you could add a DateCreated field to tblAgentVisit, you could just grab the records for the current quarter based on that field and insert those records into tblAgentVisitAgents. — Jim, Jan 22 '15 at 00:05
The problem is that this may or may not be run on the first of the quarter, and other singular records are going to be added in at random. I think. We may change how it works, so I'll keep that in mind. Thanks! — PhoenixUNI, Jan 22 '15 at 00:21
You're on the right track. Use the [`OUTPUT`](https://msdn.microsoft.com/en-us/library/ms177564%28v=sql.90%29.aspx) clause on the `INSERT` to get the newly assigned identity values into a temporary table and then move on to the next task. — HABO, Jan 22 '15 at 03:21
I guess I just don't understand how to iterate each record in tblAgentVisitLoad as its own entry. Since each agent would need a unique VisitID, they'd have to be run in the same block, then move on to the next record, right? — PhoenixUNI, Jan 22 '15 at 04:44

score 0 · Accepted Answer · edited May 23 '17 at 11:49

I have two potential solutions for you. The first trades some space for a slightly simpler approach, the second is more complex but probably better overall.

Using OUTPUT would also be an alternative, but it would require writing the assignments into a temporary table (since the receiving table can't have constraints) and then write from there into tblAgentVisitAgents. Here's another SO question with details on how you could do that:

How can I INSERT data into two tables simultaneously in SQL Server?

Solution 1: Use a temporary field for the first agent assignment.

First, add a column (say, FirstAgentID) to tblAgentVisit to temporarily store the assigned agent while creating each visit:

ALTER TABLE tblAgentVisit ADD FirstAgentID int NULL;

If you were using MSSQL 2008 or above, you could make this column SPARSE so any NULL records take up no space, but this isn't available in 2005. Still, hopefully the extra NULL int column won't be an issue for you.

Next, create the new visit records, storing temporarily the agent you intend to assign to each visit:

INSERT INTO tblAgentVisit(Status, ..., FirstAgentID)
SELECT ..., AgtId FROM tblAgentVisitLoad;

Now, use the new visit records (with their temporary FirstAgentID) to create the subsidiary record assigning the appropriate agent to each new visit:

INSERT INTO tblAgentVisitAgents(VisitID, AgtID, Prime)
SELECT VisitID, FirstAgentID, 1
FROM tblAgentVisit
WHERE FirstAgentID IS NOT NULL;

Finally, null out the temporary field so it doesn't create any potential confusion or duplication of data:

UPDATE tblAgentVisit
SET FirstAgentID=NULL
WHERE FirstAgentID IS NOT NULL;

Solution #2: Use a CTE and ROW_NUMBER() to assign new visits to agents by ordinal position.

I believe you could also do this in two statements using a CTE and ROW_NUMBER() without needing the temporary field described above:

INSERT INTO tblAgentVisit(Status, ...) blah;

WITH a AS (
   SELECT AgtId, ROW_NUMBER() OVER(ORDER BY AgtId ASC) AS row
   FROM tblAgentVisitLoad
), b AS (
   SELECT VisitID, ROW_NUMBER() OVER(ORDER BY VisitID ASC) AS row
   FROM tblAgentVisit
   WHERE NOT EXISTS(SELECT NULL FROM tblAgentVisitAgents c WHERE
      c.VisitID = tblAgentVisit.VisitID
)
INSERT INTO tblAgentVisitAgents(VisitID, AgtID, Prime)
SELECT b.VisitID, a.AgtID, 1
FROM a, b
WHERE a.row = b.row;

This would take advantage of the fact that the only visits after the INSERT that have no agents assigned are the new visits. If there are other situations where visits may not have any corresponding tblAgentVisitAgents records, you'd need to filter the CTE b somehow to only include the newly-created visits.

These are both great, viable solutions. Thank you! – PhoenixUNI Jan 26 '15 at 14:22 — PhoenixUNI, Jan 26 '15 at 14:22

SQL Insert Using Records From Another Insert

1 Answers1

Solution 1: Use a temporary field for the first agent assignment.

Solution #2: Use a CTE and ROW_NUMBER() to assign new visits to agents by ordinal position.