April 17, 2009 at 6:48 am
The answer you've already got should work, but I would do it slightly differently.
I like to use REPLACE functions to do multiple substitutions in a large string template, rather than a lot of concatenation. I think this is more readable.
I also like to use SET QUOTED_IDENTIFIER OFF so I can use double quotes around a string containing quotes, rather than a lot of paired single quotes.
Now that the important issues of style have been addressed, there are minor issues like performance. Looking at your query, I wonder if the WHERE NOT EXISTS clause is running as a correlated subquery rather than a left anti join. I've rewritten it as WHERE NOT IN so you can see if that is faster. Also, you have B.ACCTTYPE in the field list but not in the GROUP BY, so I don't see how you get this to run in the first place. If you're putting every field in the GROUP BY with no aggregate functions, you might as well just use SELECT DISTINCT.
I wrote two versions. The first one creates and executes one huge command for all companies. This should work, but with 500 companies it might be too much. The second version does it one company at a time. I'm assuming that the execution time is spent doing the inserts, and that querying the SY01500 table repeatedly to read one company name at a time is not a problem. If that is a bad assumption I'd read the names once from SY01500 into a table variable, and query the table variable inside the loop.
---------------------------------------------------------------------------------------
-- Create one huge command
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE ce_sp_RefreshTables
AS
DECLARE @sql VARCHAR(MAX)
SET @sql = ''
SELECT @sql = @sql + REPLACE(REPLACE(
"USE CE_CUSTOM;
INSERT INTO GL_Transactions
(SOURCEINTERID, SOURCETBL, DEX_ROW_ID, SOURCDOC, OPENYEAR,
OPENMTH, OPENDAY, ACTINDX, ACCTTYPE,
ACTNUMBR_1, ACTNUMBR_2, ACTNUMBR_3, ACTNUMBR_4, ACTNUMBR_5)
SELECT DISTINCT '', '', A.DEX_ROW_ID, A.SOURCDOC, DATEPART(yyyy, A.TRXDATE),
DATEPART(mm, A.TRXDATE), DATEPART(dd, A.TRXDATE), B.ACTINDX, B.ACCTTYPE,
B.ACTNUMBR_1, B.ACTNUMBR_2, B.ACTNUMBR_3, B.ACTNUMBR_4, B.ACTNUMBR_5
FROM .. A
JOIN ..GL00100 B ON A.ACTINDX = B.ACTINDX
WHERE A.TRXDATE > = 'Oct 1, 2004'
AND A.DEX_ROW_ID NOT IN (
SELECT DEX_ROW_ID FROM CE_CUSTOM..GL_Transactions
WHERE DEX_ROW_ID IS NOT NULL AND SOURCEINTERID = '' AND SOURCETBL = ''); ",
'', RTRIM(interid)),
'', CurrentTable)
FROM DYNAMICS..SY01500
CROSS JOIN (
SELECT 'GL20000' AS CurrentTable
UNION ALL
SELECT 'GL30000' ) x
WHERE UDCOSTR2 = 'Active'
AND interid 'Audit'
ORDER BY interid, CurrentTable
EXEC ( @sql )
GO
---------------------------------------------------------------------------------------
-- Use one database at a time, but without a cursor
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE ce_sp_RefreshTables
AS
DECLARE @sql VARCHAR(MAX), @CompanyName NVARCHAR(255)
SET @CompanyName = ''
WHILE 1=1 BEGIN
SELECT TOP 1 @CompanyName = RTRIM(interid)
FROM DYNAMICS..SY01500
WHERE UDCOSTR2 = 'Active' AND interid 'Audit' AND interid > @CompanyName
ORDER BY interid
IF @@ROWCOUNT = 0
BREAK
SELECT @sql = REPLACE(REPLACE(
"USE CE_CUSTOM;
INSERT INTO GL_Transactions
(SOURCEINTERID, SOURCETBL, DEX_ROW_ID, SOURCDOC, OPENYEAR,
OPENMTH, OPENDAY, ACTINDX, ACCTTYPE,
ACTNUMBR_1, ACTNUMBR_2, ACTNUMBR_3, ACTNUMBR_4, ACTNUMBR_5)
SELECT DISTINCT '', '', A.DEX_ROW_ID, A.SOURCDOC, DATEPART(yyyy, A.TRXDATE),
DATEPART(mm, A.TRXDATE), DATEPART(dd, A.TRXDATE), B.ACTINDX, B.ACCTTYPE,
B.ACTNUMBR_1, B.ACTNUMBR_2, B.ACTNUMBR_3, B.ACTNUMBR_4, B.ACTNUMBR_5
FROM .. A
JOIN ..GL00100 B ON A.ACTINDX = B.ACTINDX
WHERE A.TRXDATE > = 'Oct 1, 2004'
AND A.DEX_ROW_ID NOT IN (
SELECT DEX_ROW_ID FROM CE_CUSTOM..GL_Transactions
WHERE DEX_ROW_ID IS NOT NULL AND SOURCEINTERID = '' AND SOURCETBL = ''); ",
'', @CompanyName),
'', CurrentTable)
FROM (
SELECT 'GL20000' AS CurrentTable
UNION ALL
SELECT 'GL30000' ) x
EXEC ( @sql )
END
April 17, 2009 at 11:10 am
This looks interesting.
Thank you.
But I don't understand where you are getting 'comp' and 'table'. Are this variables?
Tony
Things will work out. Get back up, change some parameters and recode.
April 17, 2009 at 1:39 pm
I'm using '{COMP}' and '{TBL}' as replacement tags in the template string (I changed the angle brackets to curly braces so this would post correctly). They're just text strings, not variables. You could use 'Bonnie' and 'Clyde' if you liked, as long as the text you use doesn't occur naturally in the template.
REPLACE('Have you read the manual', 'Have you', 'No I have not') replaces instances of the 2nd parameter with the 3rd parameter in the text of the 1st parameter. I used two nested REPLACE functions to make two replacements.
The inner REPLACE changes all occurrences of {COMP} to the value of RTRIM(interid), the outer one changes all occurrences of {TBL} with CurrentTable (either GL20000 or GL30000).
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply