July 2, 2014 at 3:25 pm
Hi All
Could anyone help me on getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.
Declare @Companyname Nvarchar (400)
declare @STR nvarchar(MAX)
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
DECLARE GLEntry_cursor CURSOR FOR
SELECT REPLACE Name FROM Company where Name <> 'AAAAA'
OPEN GlEntry_cursor
FETCH NEXT FROM GLEntry_cursor INTO @Companyname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STR = ('SELECT '''+@CompanyName+''' as Company,[Entry No_] ,[G_L Account No_] ,[Posting Date] ,[Document Type] FROM dbo.['+@CompanyName+'$G_L Entry]')
--PRINT (@STR)
Insert into STAGING.dbo.[IT_G_L Entry]
EXEC (@STR)
FETCH NEXT FROM GLEntry_cursor INTO @Companyname
END
CLOSE GLEntry_cursor
DEALLOCATE GLEntry_cursor
Regards
SR
July 2, 2014 at 3:44 pm
ksrikanth77 (7/2/2014)
Hi AllCould anyone help me on getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.
Declare @Companyname Nvarchar (400)
declare @STR nvarchar(MAX)
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
DECLARE GLEntry_cursor CURSOR FOR
SELECT REPLACE Name FROM Company where Name <> 'AAAAA'
OPEN GlEntry_cursor
FETCH NEXT FROM GLEntry_cursor INTO @Companyname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STR = ('SELECT '''+@CompanyName+''' as Company,[Entry No_] ,[G_L Account No_] ,[Posting Date] ,[Document Type] FROM dbo.['+@CompanyName+'$G_L Entry]')
--PRINT (@STR)
Insert into STAGING.dbo.[IT_G_L Entry]
EXEC (@STR)
FETCH NEXT FROM GLEntry_cursor INTO @Companyname
END
CLOSE GLEntry_cursor
DEALLOCATE GLEntry_cursor
Regards
SR
Why do you want to replace one type of loop with another? Honestly, a well written cursor is likely to perform better than a recursive cte or a while loop.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2014 at 3:59 pm
First, you might ask if you really need to replace the cursor. Are there so many company records that it makes a significant difference in comparison to the time it takes to insert the records? Is it worth compromising working code?
But if you must... 🙂
I have two methods for your consideration. Both concatenate all the insert statements into a single string using a different string concatenation method.
1. Uses the FOR XML PATH('') trick. Downside is that you should be sure that the Company.Name doe not have any escaped XML characters. (&,",', etc.) If these are strictly alphanumeric fields, you should be good.
DECLARE @STR nvarchar(MAX);
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry];
SET @STR =
(SELECT 'INSERT INTO STAGING.dbo.[IT_G_L Entry] SELECT ''' + CompanyName
+ ''', [Entry No_], [G_L Account No_], [Posting Date], [Document Type] FROM dbo.[' + CompanyName + '$G_L Entry];'
FROM Company
WHERE CompanyName <> 'AAAAA'
FOR XML PATH(''));
EXEC @STR;
Or...
2. Uses the string variable as an accumulation point.
DECLARE @STR nvarchar(MAX);
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry];
SELECT @STR = COALESCE(@str,'') + 'INSERT INTO STAGING.dbo.[IT_G_L Entry] SELECT ''' + Name
+ ''', [Entry No_], [G_L Account No_], [Posting Date], [Document Type] FROM dbo.[' + Name + '$G_L Entry];'
FROM Company
WHERE Name <> 'AAAAA';
EXEC @STR;
The only other issue that I'm aware of is if there is any possibility of SQL injection via the Company.Name field. I'm not sure how to secure these against it. Perhaps the wiser denizens of the forum might be able to help.
I am only an egg.
July 2, 2014 at 5:53 pm
Hi James
I am not getting any result or any error when I execute the second query
July 3, 2014 at 7:10 am
James Hammond (7/2/2014)
The only other issue that I'm aware of is if there is any possibility of SQL injection via the Company.Name field. I'm not sure how to secure these against it. Perhaps the wiser denizens of the forum might be able to help.
Since @CompanyName is also part of the table name it would be easy to leverage sys.objects to see if that table exists.
if EXISTS(
select *
from sys.objects
where name = '[' + @CompanyName + '$G_L Entry]'
)
--Put your dynamic code here
You could even add an else to raise an exception if the value passed in doesn't exist (that is probably something I would do).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 3, 2014 at 8:27 am
Since @CompanyName is also part of the table name it would be easy to leverage sys.objects to see if that table exists.
...
or use OBJECTPROPERTY function.
No cursor or loop requried to build SQL, however the performance of this query will be defined not by loop slowness, but the fact that it still needs to be a seria of separate insert queries as you need to insert from different tables:
DECLARE @sql NVARCHAR(MAX) = ''
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
SELECT @sql = @sql + 'Insert into STAGING.dbo.[IT_G_L Entry] SELECT '''+C.Name+''',[Entry No_] ,[G_L Account No_] ,[Posting Date] ,[Document Type] FROM dbo.['+@CompanyName+'$G_L Entry];
'
FROM Company C
WHERE C.Name != 'AAAAA'
AND OBJECTPROPERTY(OBJECT_ID( 'dbo.['+C.Name+'$G_L Entry]'),'IsUserTable') = 1
EXEC (@sql)
July 3, 2014 at 11:04 am
ksrikanth77 (7/2/2014)
Hi JamesI am not getting any result or any error when I execute the second query
It produces what I expect on my database. Try replacing the EXEC with PRINT and see what string is being created. Post that result here, if you can't find anything wrong with it.
I agree with everyone else posting here that you won't see any performance improvement by making this change. The only benefit I can see is if you have a company mandate against cursors.
July 3, 2014 at 11:07 am
Eugene Elutin (7/3/2014)
Since @CompanyName is also part of the table name it would be easy to leverage sys.objects to see if that table exists.
...
or use OBJECTPROPERTY function.
...
Sean and Eugene - Good idea. I like that!
July 3, 2014 at 11:22 am
Looking at the code you provided I am wondering why you want to eliminate the cursor. You are looping through a set of tables to import the data from each source table in a set based manner.
What problem are you experiencing.
Yes, you could write the code so that it creates a single dynamic sql based code set that would import the data from the tables all at once but if you aren't having any serious issues I'm not sure if I would take the time as much as I don't really like cursors.
They do have a use, just make sure you are using them correctly.
July 3, 2014 at 11:26 am
Yes, you could write the code so that it creates a single dynamic sql based code set that would import the data from the tables all at once...
Yep, building @SQL with UNION ALL bit between SELECT, instead of INSERT.
Would it make any faster? I doubt it...
July 3, 2014 at 11:30 am
Eugene Elutin (7/3/2014)
Yes, you could write the code so that it creates a single dynamic sql based code set that would import the data from the tables all at once...
Yep, building @SQL with UNION ALL bit between SELECT, instead of INSERT.
Would it make any faster? I doubt it...
Plus, the way you have it right now each table is imported in its own transaction not as one massive transaction which could actually be slower and would be an all or nothing import if the insert failed for any reason.
July 3, 2014 at 12:06 pm
Thanks all you for your responses, however When I print or Exec the result is Command Completed Successfully.
My request is
I have a company table with the Name Column having Company Names
I want to loop through all the GLtables which are separated by companyname prefix and to insert data into a staging table getting data from all the GLtables from different companies.
The solution provided do not have a @CompanyName varaible Declared and gives me an error for creating the variable.And If I declare and set the company name it displays me the insert into ... select * from! query prints correctly.
I am looking for a solution which exactly replace the cursors which I posted earlier.
Thanks Again
SR
July 3, 2014 at 12:10 pm
ksrikanth77 (7/3/2014)
Thanks all you for your responses, however When I print or Exec the result is Command Completed Successfully.My request is
I have a company table with the Name Column having Company Names
I want to loop through all the GLtables which are separated by companyname prefix and to insert data into a staging table getting data from all the GLtables from different companies.
The solution provided do not have a @CompanyName varaible Declared and gives me an error for creating the variable.And If I declare and set the company name it displays me the insert into ... select * from! query prints correctly.
I am looking for a solution which exactly replace the cursors which I posted earlier.
Thanks Again
SR
Why are you wanting to replace the cursor? You still haven't answered that question. Is there a specific problem or issue you are trying to solve?
July 3, 2014 at 12:12 pm
Lynn Pettis (7/3/2014)
Eugene Elutin (7/3/2014)
Yes, you could write the code so that it creates a single dynamic sql based code set that would import the data from the tables all at once...
Yep, building @SQL with UNION ALL bit between SELECT, instead of INSERT.
Would it make any faster? I doubt it...
Plus, the way you have it right now each table is imported in its own transaction not as one massive transaction which could actually be slower and would be an all or nothing import if the insert failed for any reason.
Again, why replace the cursor? Is it not functioning correctly? In your original post you said you wanted to use a recursive cte or a while loop. Both of which are very likely to be slower to produce the same results. A cursor is really just a while loop.
--edit--
got bitten by the quote bug. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 3, 2014 at 12:29 pm
And if you are so serious about eliminating the cursor, here is some code. Try it by itself in an empty test database. I have the insert part commented so it won't run.
SET NOCOUNT ON;
create table dbo.Company(Name varchar(10));
insert into dbo.Company values ('ABC'),('BCD');
declare @SQLCmd nvarchar(max);
select @SQLCmd = stuff((select N'union all' + nchar(13) + nchar(10) + N'select ''' + c.Name + N''' as Company, [Entry No_], [G_L Account No_], [Posting Date], [Document Type] FROM dbo.[' +
c.Name + N'$G_L Entry]' + nchar(13) + nchar(10)
from dbo.Company c
where c.Name <> 'AAAAA'
order by c.Name
for xml path(''),TYPE).value('.','nvarchar(max)'),1,11,'');
print @SQLCmd;
--truncate table STAGING.dbo.[IT_G_L Entry];
--insert into STAGING.dbo.[IT_G_L Entry]
--exec (@SQLCmd);
drop table dbo.Company;
SET NOCOUNT OFF:
EDIT: Added the truncate table to the code, also as a comment.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply