Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Insert Multiple Rows Without a Cursor

Author  Topic 

sfortner
Yak Posting Veteran

63 Posts
Posted - 2011-06-09 : 10:34:12
I'm working with a Sage Timesheet database over which I can't change. We have time entries in a tsp_TimeEntries table and we have to insert corresponding rows into a tsp_AppTrack table to be able to change time status. I'd like to get rid of the cursor, but not sure it's possible. The tsp_TimeEntries table's ID won't have a corresponding Emp_ID in the tsp_AppTrack before changing time status. Can someone help make this more efficient? Here's what we have now:

-- create submit record for manager approved step
declare @SubmitID int, @tspID int, @date1 datetime, @date2 datetime
select @date1 = '02/20/2011', @date2 = '03/05/2011'  -- two week pay period
select @SubmitID = ID from TSP_APPROVALS where NAME = 'Manager Approved'

DECLARE ffcursor CURSOR FAST_FORWARD FOR
select te.ID from tsp_TimeEntries
inner join tsp_Components_0 c0 on (te.IDS_0 = c0.ID )
inner join tsp_EmployeeData e on e.id = c0.ID
left outer join rjc_PayCodes pc on (te.IDS_0 = pc.IDS_0 and te.IDS_1 = pc.IDS_1 and te.IDS_4 = pc.IDS_4 
and te.IDS_5 = pc.IDS_5 and te.IDS_9 = pc.IDS_9)
where te.IDS_1 = @Nids1 and rtrim(ltrim(e.field_3)) = @Division and te.status_10 = 3 and te.entry_date between @Date1 and @date2 and te.ID not in
  (select ID from tsp_AppTrack)

OPEN ffcursor

FETCH NEXT FROM ffcursor
INTO @tspID

WHILE @@FETCH_STATUS = 0
BEGIN
	insert into tsp_apptrack (ID, EMP_ID, START_DATE, END_DATE, STEP_ID, APPROVAL_TYPE, AUDIT_EMP, ITEM_GUID)
	select max(id) + 1, @tspID, @Date1, @Date2, @SubmitID, 1, 0, 
	Cast(@tspID as varchar(10)) + '-T-' + Convert(varchar(10),cast(@startDate as datetime),112))
	from tsp_AppTrack with (HOLDLOCK, UPDLOCK)

	FETCH NEXT FROM ffcursor
	INTO @tspID
END

CLOSE ffcursor
DEALLOCATE ffcursor


--Steve

sfortner
Yak Posting Veteran

63 Posts
Posted - 2011-06-13 : 08:57:00
I don't think a derived query will work because of the max(id) + 1 requirement, or can it? Anyone have a sol'n?

--Steve
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2011-06-13 : 09:19:44
Max + 1? Is this in MS SQL Server? Since you say you can't change it, i feel for you.

I am sure it could be done with a loop, an OUTPUT clause, and a temp table or CTE. Not sure if it would be more efficient.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

sfortner
Yak Posting Veteran

63 Posts
Posted - 2011-06-13 : 09:50:39
Yes, SQL Server 2005. The cursor is a RBAR (row by agonizing row) loop already, but it would be great if this could be a set-based operation. I'm using an output clause for the insert into the tsp_TimeEntries so I know which ID was just inserted, but not sure how I'd use that to handle what to insert. Temp table or CTE might be an option though, but the show stopper IMO is the max(id) + 1 that's not associated with anything in the tsp_TimeEntries so I can't join against that. Any ideas are much appreciated...

--Steve
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2011-06-13 : 13:23:53
max + 1 seems to lock you into a loop, unless a recursive cte could be cludged to simulate it and join on that. I have no clue how to make that work.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2011-06-13 : 13:54:16
This should work, no recursion necessary:
;WITH params(SubmitID, Date1, Date2) AS (
SELECT ID SubmitID, CAST('02/20/2011' AS DATETIME) Date1, CAST('03/05/2011' AS DATETIME) Date2 
FROM TSP_APPROVALS WHERE NAME = 'Manager Approved'),
IDs(tspID, row_num) AS (SELECT te.ID, ROW_NUMBER() OVER (ORDER BY te.ID) FROM tsp_TimeEntries
	INNER JOIN tsp_Components_0 c0 ON te.IDS_0 = c0.ID
	INNER JOIN tsp_EmployeeData e ON e.id = c0.ID
	LEFT OUTER JOIN rjc_PayCodes pc ON te.IDS_0 = pc.IDS_0 AND te.IDS_1 = pc.IDS_1 AND te.IDS_4 = pc.IDS_4 
	AND te.IDS_5 = pc.IDS_5 AND te.IDS_9 = pc.IDS_9
	CROSS JOIN params
	WHERE te.IDS_1 = @Nids1 AND RTRIM(LTRIM(e.field_3)) = @Division AND te.status_10 = 3
		AND te.entry_date BETWEEN Date1 AND date2 AND te.ID NOT IN (SELECT ID FROM tsp_AppTrack)),
maxID(maxID) AS (SELECT MAX(id) tsp_apptrack)
INSERT INTO tsp_apptrack (ID, EMP_ID, START_DATE, END_DATE, STEP_ID, APPROVAL_TYPE, AUDIT_EMP, ITEM_GUID)
SELECT maxID+row_num, tspID, Date1, Date2, SubmitID, 1, 0, 
CAST(tspID AS VARCHAR(10)) + '-T-' + CONVERT(VARCHAR(10),CAST(@startDate AS DATETIME),112))
FROM IDs CROSS JOIN params CROSS JOIN maxID
I'm getting some weird syntax errors with @Nids1 and @Division, I didn't see them declared in your code so I'm assuming you posted a snippet.
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts
Posted - 2011-06-14 : 13:27:33
What, you couldn't cram a few more cross joins in there?

Where is doctor cross join when you need him!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2011-06-14 : 13:38:58
Hey, params and maxID are both single rows. Stop picking on me!
Go to Top of Page

sfortner
Yak Posting Veteran

63 Posts
Posted - 2011-07-13 : 11:49:32
Rob, this looks fantastic, but I'm concerned about one thing. It looks like this grabs the ID from the tsp_AppTrack table but does no updlock or holdlock to guarantee that if Timesheet inserts a record into the tsp_AppTrack during this operation that it doesn't duplicate IDs. This table is updated by both Timesheet users as well as users of our custom app. I corrected a few minor syntax errors (sorry, there's no way for you to test it really), and changed the check for existing records to look at the emp_id field for the given date range we're specifying. The query now looks like the below:


		;WITH params(SubmitID, Date1, Date2) AS (
		SELECT ID as SubmitID, @date1 as Date1, @date2 as Date2
		FROM TSP_APPROVALS WHERE NAME = 'Submitted'),
		IDs(tspID, row_num) AS (SELECT te.ID, ROW_NUMBER() OVER (ORDER BY te.ID)
		FROM tsp_TimeEntries te
		INNER JOIN tsp_Components_0 c0 ON te.IDS_0 = c0.ID
		INNER JOIN tsp_EmployeeData e ON e.id = c0.ID
		LEFT OUTER JOIN rjc_PayCodes pc ON te.IDS_0 = pc.IDS_0 AND te.IDS_1 = pc.IDS_1 AND te.IDS_4 = pc.IDS_4 
		AND te.IDS_5 = pc.IDS_5 AND te.IDS_9 = pc.IDS_9
		CROSS JOIN params
		WHERE te.IDS_1 = @Nids1 AND RTRIM(LTRIM(e.field_3)) = @Division AND te.status_10 = 3
		AND te.entry_date BETWEEN Date1 AND date2 AND te.IDS_0 NOT IN 
			(SELECT EMP_ID FROM tsp_AppTrack where START_DATE = @Date1 and END_DATE = @Date2)),
		maxID(maxID) AS (SELECT MAX(id) from tsp_apptrack)
		INSERT INTO tsp_apptrack (ID, EMP_ID, START_DATE, END_DATE, STEP_ID, APPROVAL_TYPE, AUDIT_EMP, ITEM_GUID)
		SELECT maxID+row_num, tspID, Date1, Date2, SubmitID, 1, 0, 
		CAST(tspID AS VARCHAR(10)) + '-T-' + CONVERT(VARCHAR(10),CAST(@Date1 AS DATETIME),112)
		FROM IDs CROSS JOIN params CROSS JOIN maxID


PS - I'm really going to have to break down and learn how CTEs and cross joins work. I've only used both a couple of times, but don't fully understand them yet. I only understand at a high level how your query works.

--Steve
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2011-07-13 : 12:14:22
quote:
updlock or holdlock to guarantee that if Timesheet inserts a record into the tsp_AppTrack during this operation that it doesn't duplicate IDs
Locking hints won't guarantee that anyway, only a primary key or unique constraint can prevent duplicates. If this is the only statement you need to run then you shouldn't need a lock hint, or if you want to be safe just put an explicit BEGIN TRANSACTION...COMMIT around it, and optionally SET TRANSACTION ISOLATION LEVEL SERIALIZABLE too.
Go to Top of Page

sfortner
Yak Posting Veteran

63 Posts
Posted - 2011-07-13 : 12:27:10
Okay, thanks for the advice. I confirmed that the table currently has no PK and only a single non-clustered index on the ID and Start_Date fields, and no unique constraint. We are currently using the updlock and holdlock hints to insert time into our tsp_TimeEntries, but we are having lots of deadlocks. I've remedied that with a retry per http://msdn.microsoft.com/en-us/library/ms179296(v=SQL.90).aspx and http://msdn.microsoft.com/en-us/library/aa175791(v=sql.80).aspx.

I think probably because Sage is querying tables in a different order than we are and it's getting deadlocked per Microsoft's guidelines at http://msdn.microsoft.com/en-us/library/ms177433(v=SQL.90).aspx. But the updlock and holdlock hints have eliminated the duplicate IDs, but the big difference with the tsp_TimeEntries is that it has a unique constraint on the ID field. That said, do we need to add a unique constraint on ID and Start_date of the tsp_AppTrack? Or will we be safe with just the transaction wrapper and SERIALIZABLE isolation level?

Update: this link http://msdn.microsoft.com/en-us/library/aa259216(v=sql.80).aspx shows that SERIALIZIBLE puts a holdlock on all select statements for all tables in the transaction. This sounds perfect in that it will lock it all down while it's updating.

--Steve
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2011-07-13 : 13:19:02
Don't know about Start_Date, but any "ID" column that is supposed to uniquely identify a row should have a unique or primary key constraint on it. If you can't add it, then SERIALIZABLE will prevent INSERTs and UPDATEs that could affect the MAX(ID). It's at least equivalent to the locking hints you're using.

Is the ID column using IDENTITY?
Go to Top of Page

sfortner
Yak Posting Veteran

63 Posts
Posted - 2011-07-13 : 13:43:28
No, the ID field has no identity, but it appears that ID and Start_Date and maybe End_Date make a unique row. This table stores a status for each user (@tspid) for a given pay period (Start_Date and End_Date). Basically, my understanding is that when time is entered for a user in the tsp_TimeEntries, that user will eventually have an entry for the pay period in the tsp_AppTrack table that tracks the status of their time. It's customizable via the tsp_Approvals table, and that contains entries like Entered (for us, a 3), Submitted (a 7), Manager Approved (oddly, a 4), then it goes to Final Approved (an 8) where their time is approved by Payroll.

This database is not set up very well, but from what I understand has been around a long time, and may be just a carryover from the original. I think most all the constraints and anything special they're doing to ensure integrity is done in their apps.

I think that SERIALIZIBLE isolation level looks perfect. We also have to update the status_10 field from a 3 to a 7 in the tsp_TimeEntries in this sproc, so everything is already wrapped in a transaction. I think I can just plug in your CTE insert and set the SERIALIZIBLE isolation level and we're good to go. Thanks for the excellent help!

--Steve
Go to Top of Page

sfortner
Yak Posting Veteran

63 Posts
Posted - 2011-07-13 : 16:12:07
I'm having trouble with the CTE. If I query it after creation with the following

;WITH params(SubmitID, Date1, Date2) AS (
SELECT ID as SubmitID, @date1 as Date1, @date2 as Date2
FROM TSP_APPROVALS WHERE NAME = 'Submitted'),
IDs(tspID, row_num) AS 
(SELECT te.ID, ROW_NUMBER() OVER (ORDER BY te.ID)
FROM tsp_TimeEntries te
INNER JOIN tsp_Components_0 c0 ON te.IDS_0 = c0.ID
INNER JOIN tsp_EmployeeData e ON e.id = c0.ID
LEFT OUTER JOIN rjc_PayCodes pc ON te.IDS_0 = pc.IDS_0 AND te.IDS_1 = pc.IDS_1 AND te.IDS_4 = pc.IDS_4 
AND te.IDS_5 = pc.IDS_5 AND te.IDS_9 = pc.IDS_9
CROSS JOIN params
WHERE te.IDS_1 = @Nids1 AND RTRIM(LTRIM(e.field_3)) = @Division AND te.status_10 = 3
AND te.entry_date BETWEEN Date1 AND date2 AND te.IDS_0 NOT IN 
	(SELECT EMP_ID FROM tsp_AppTrack where EMP_ID = te.IDS_0 and START_DATE = @Date1 and END_DATE = @Date2)),
maxID(maxID) AS (SELECT MAX(id) from tsp_apptrack)

select * from params


I get the result set

SubmitID Date1 Date2
674500000 2011-02-27 00:00:00.000 2011-03-12 00:00:00.000

To insert this into the tsp_AppTrack table, it needs to include the tspID (te.IDS_0) in the results. How can that be done?

--Steve
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2011-07-13 : 16:19:20
You're only SELECTing from the params CTE, you have to cross join it with the IDs CTE, like I originally posted. You have to include the INSERT...SELECT directly under the CTE block or it won't work.

If you need IDS_0 insted of ID, just change the column in the SELECT that defines the IDs CTE:
;WITH ...
IDs(tspID, row_num) AS 
(SELECT te.IDS_0, ROW_NUMBER() OVER (ORDER BY te.ID)
FROM tsp_TimeEntries te ...
Go to Top of Page

sfortner
Yak Posting Veteran

63 Posts
Posted - 2011-07-13 : 17:20:16
Yeah, sorry, I had misunderstood how this worked initially, so I've introduced some bugs. Here's an updated version of your CTE, but the issue is that it now returns 94 total rows instead of the correct 10 distinct ids_0 rows for each of the employees. I believe it is because there's multiple records in the tsp_TimeEntries. How do I return the distinct ids_0 rows in the CTE? Here's the updated version:

;WITH params(SubmitID, Date1, Date2) AS (
SELECT ID as SubmitID, @date1 as Date1, @date2 as Date2
FROM TSP_APPROVALS WHERE NAME = 'Submitted'),
IDs(tspID, row_num) AS 
(SELECT te.IDS_0, ROW_NUMBER() OVER (ORDER BY te.IDS_0)
FROM tsp_TimeEntries te
INNER JOIN tsp_Components_0 c0 ON te.IDS_0 = c0.ID
INNER JOIN tsp_EmployeeData e ON e.id = c0.ID
LEFT OUTER JOIN rjc_PayCodes pc ON te.IDS_0 = pc.IDS_0 AND te.IDS_1 = pc.IDS_1 AND te.IDS_4 = pc.IDS_4 
AND te.IDS_5 = pc.IDS_5 AND te.IDS_9 = pc.IDS_9
CROSS JOIN params
WHERE te.IDS_1 = @Nids1 AND RTRIM(LTRIM(e.field_3)) = @Division --AND te.status_10 = 3
AND te.entry_date BETWEEN Date1 AND date2 AND te.IDS_0 NOT IN 
	(SELECT EMP_ID FROM tsp_AppTrack where EMP_ID = te.IDS_0 and START_DATE = @Date1 and END_DATE = @Date2)),
maxID(maxID) AS (SELECT MAX(id) from tsp_apptrack)
--INSERT INTO tsp_apptrack (ID, EMP_ID, START_DATE, END_DATE, STEP_ID, APPROVAL_TYPE, AUDIT_EMP, ITEM_GUID)
SELECT maxID+row_num, tspID, Date1, Date2, SubmitID, 1, 0, 
CAST(tspID AS VARCHAR(10)) + '-T-' + CONVERT(VARCHAR(10),CAST(@Date1 AS DATETIME),112)
FROM IDs CROSS JOIN params CROSS JOIN maxID


--Steve
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2011-07-13 : 17:29:19
Why that would be...ANOTHER CTE!
;WITH params(SubmitID, Date1, Date2) AS (
SELECT ID as SubmitID, @date1 as Date1, @date2 as Date2
FROM TSP_APPROVALS WHERE NAME = 'Submitted'),
ID(tspID) AS 
(SELECT DISTINCT te.IDS_0
FROM tsp_TimeEntries te
INNER JOIN tsp_Components_0 c0 ON te.IDS_0 = c0.ID
INNER JOIN tsp_EmployeeData e ON e.id = c0.ID
LEFT OUTER JOIN rjc_PayCodes pc ON te.IDS_0 = pc.IDS_0 AND te.IDS_1 = pc.IDS_1 AND te.IDS_4 = pc.IDS_4 
AND te.IDS_5 = pc.IDS_5 AND te.IDS_9 = pc.IDS_9
CROSS JOIN params
WHERE te.IDS_1 = @Nids1 AND RTRIM(LTRIM(e.field_3)) = @Division --AND te.status_10 = 3
AND te.entry_date BETWEEN Date1 AND date2 AND te.IDS_0 NOT IN 
	(SELECT EMP_ID FROM tsp_AppTrack where EMP_ID = te.IDS_0 and START_DATE = @Date1 and END_DATE = @Date2)),
IDs(tspID, row_num) AS (SELECT tspID, ROW_NUMBER() OVER (ORDER BY tspID) FROM ID),
maxID(maxID) AS (SELECT MAX(id) from tsp_apptrack)
--INSERT INTO tsp_apptrack (ID, EMP_ID, START_DATE, END_DATE, STEP_ID, APPROVAL_TYPE, AUDIT_EMP, ITEM_GUID)
SELECT maxID+row_num, tspID, Date1, Date2, SubmitID, 1, 0, 
CAST(tspID AS VARCHAR(10)) + '-T-' + CONVERT(VARCHAR(10),CAST(@Date1 AS DATETIME),112)
FROM IDs CROSS JOIN params CROSS JOIN maxID
You can stack these things like pancakes.
Go to Top of Page

sfortner
Yak Posting Veteran

63 Posts
Posted - 2011-07-14 : 08:21:44
Cool - I had read about the stacking of CTEs a little from yesterday but had no idea that's how to eliminate duplicates. That worked great - thanks for all the really excellent help. The SERIALIZABLE transaction isolation level is also key to our success - I would not have implemented that without your guidance. I also tested and confirmed that this query doesn't post duplicates in the tsp_AppTrack table, so that's perfect. I hope you know you've made me feel like a rank amateur. I don't know how I ever wrote anything complex at all. Oh yeah, with cursors. Wasn't that my original question?

--Steve
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2011-07-14 : 11:45:34
quote:
I hope you know you've made me feel like a rank amateur


Not my intention, just wanted to help. Besides, I was a rank amateur too...really, I didn't manage my personal hygiene that well back then.

BTW, I've only learned how to use CTEs in the past year or so, and they've been available for 6.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site