January 15, 2009 at 7:27 am
Hi there,
I need to make a row by row loop, in a big monster table... millions and millions of rows...
I dont want to make a cursor loop, which I heard is performance demanding.. So, I was thinking of doing a "while" row by row loop, where I delete each row i have worked with.. But, as far as i know, is DELETE FROM, also performance demanding... But do I have an alternative, (please notice that I do not have rowID column in any of the tables)...
Kind regards,
Anders
January 15, 2009 at 7:29 am
If you actually have to do row-by-row, use a cursor for it. It's what they're built for, and they're good at it. While loops for row-by-row aren't as good. (While loops are better at some things, but not this.)
If you describe what you're trying to accomplish, we can probably help you come up with something better than row-by-row. If not, go with a cursor.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 15, 2009 at 7:44 am
Hi,
What I need to do is:
Find out which specific rows and columns that contains ascii values below 32, and log it either in a txt file or another db.
So, basically I need to check each character in the entire database 😉 But I will only take a table at a time...
Kind regards,
Anders
January 15, 2009 at 7:56 am
If the table have keys that uniquely identify the rows then you do not need to use row by row logic.
Run a query that returns a list of rows that have that character then copy those rows.
oops, not enough coffee this morning.
You should still be able to do this without the proper keys
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
January 15, 2009 at 8:00 am
GSquared (1/15/2009)
If you actually have to do row-by-row, use a cursor for it. It's what they're built for, and they're good at it. While loops for row-by-row aren't as good. (While loops are better at some things, but not this.)If you describe what you're trying to accomplish, we can probably help you come up with something better than row-by-row. If not, go with a cursor.
If you add the caveat that if be a "firehose" (read only, static), I'd pretty much agree although there are certainly dynamic ways to avoid even those.
And, the myth that a while loop is any better than a "firehose" cursor just isn't true.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2009 at 8:02 am
agh100 (1/15/2009)
Hi,What I need to do is:
Find out which specific rows and columns that contains ascii values below 32, and log it either in a txt file or another db.
So, basically I need to check each character in the entire database 😉 But I will only take a table at a time...
Kind regards,
Anders
This can actually be done without either a cursor or a while loop in SQL Server 2005... how many tables and are they all in the same DB?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2009 at 8:05 am
Alternatively you can use SSIS for this, I did something similar recently and found SSIS worked perfectly.
January 15, 2009 at 8:07 am
Let me rephrase my question a bit... add on to it, really...
How are you going to identify the columns that you want to check or are you asking for code that checks every column, regardless of name.
At this point, it would be very helpful if you posted the CREATE TABLE statement for the table(s) in question.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2009 at 8:31 am
Just as a note as well.. if you are going to end up processing millions upon millions rows as indicated make sure you look at working with subsets to keep locks on the database down, otherwise you may bring other items to a halt while the cursor processes.
January 15, 2009 at 8:32 am
Without a cursor or While loop or whatever, you could get that in one pass with something like this:
create function Cols
(@Table_in nvarchar(128))
returns nvarchar(max)
as
begin
declare @Cols nvarchar(max)
select @Cols = coalesce(@cols + '+isnull([' + name + '], '''')', 'isnull([' + name + '], '''')')
from sys.columns
where object_id = object_id(@Table_in)
and system_type_id in (35,99,167,175,231,239)
return @Cols
end;
go
declare @Char0 char(1), @Char31 char(1), @SQL nvarchar(max);
select @Char0 = char(0), @Char31 = char(31);
select @SQL = coalesce(@SQL + ';
select * from dbo.' + name + ' where ' + dbo.Cols(name) + ' like ''%[' + @Char0 + '-' + @Char31 + ']%''',
'select * from dbo.' + name + ' where ' + dbo.Cols(name) + ' like ''%[' + @Char0 + '-' + @Char31 + ']%''')
from sys.tables
where object_id in
(select object_id
from sys.columns
where system_type_id in (35,99,167,175,231,239));
exec (@SQL);
Set the output to text file when you run the second part (after the "go"), and you'll have your text file. Modify the dynamic select if you need to. Or add "for XML raw, type" to the end of the select and insert it all into a table with an XML column.
This will definitely be better than a loop/cursor.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2009 at 1:05 am
Hi GSquared,
I have been sick for some days now, so I haven't been able to try your script until now.. And thanks very much, it works splendidly on SQL 2005 not on 2000 since "coalesce" doesn't exists. But never mind... I will just import a copy of my database to 2005, and run the script on it...
Thanks very very much....
Kind regards,
Anders
January 21, 2009 at 3:11 am
Hmm... After testing it again, I noticed that as far as i can see, it only returns char(31)... Not the ones below... Tested in one table...
January 21, 2009 at 6:45 am
I just tested it with:
create table dbo.Test1 (
ID int identity primary key,
Col1 varchar(100),
Col2 varchar(100),
Col3 varchar(100));
go
insert into dbo.test1 (col1, col2, col3)
select 'Char1'+char(1), 'NoChar', 'NoChar' union all
select 'NoChar', 'Char2'+char(2), 'NoChar' union all
select 'NoChar', 'NoChar', 'NoChar' union all
select 'Char2'+char(2), 'NoChar', 'NoChar' union all
select 'Char3'+char(3), 'NoChar', 'NoChar' union all
select 'Char4'+char(4), 'NoChar', 'NoChar' union all
select 'Char5'+char(5), 'NoChar', 'NoChar' union all
select 'Char6'+char(6), 'NoChar', 'NoChar' union all
select 'Char7'+char(7), 'NoChar', 'NoChar' union all
select 'Char8'+char(8), 'NoChar', 'NoChar' union all
select 'Char9'+char(9), 'NoChar', 'NoChar' union all
select 'Char10'+char(10), 'NoChar', 'NoChar' union all
select 'Char11'+char(11), 'NoChar', 'NoChar' union all
select 'Char12'+char(12), 'NoChar', 'NoChar' union all
select 'Char13'+char(13), 'NoChar', 'NoChar' union all
select 'Char14'+char(14), 'NoChar', 'NoChar' union all
select 'Char15'+char(15), 'NoChar', 'NoChar' union all
select 'Char16'+char(16), 'NoChar', 'NoChar' union all
select 'Char17'+char(17), 'NoChar', 'NoChar' union all
select 'Char18'+char(18), 'NoChar', 'NoChar' union all
select 'Char19'+char(19), 'NoChar', 'NoChar' union all
select 'Char20'+char(20), 'NoChar', 'NoChar' union all
select 'Char21'+char(21), 'NoChar', 'NoChar' union all
select 'Char22'+char(22), 'NoChar', 'NoChar' union all
select 'Char23'+char(23), 'NoChar', 'NoChar' union all
select 'Char24'+char(24), 'NoChar', 'NoChar' union all
select 'Char25'+char(25), 'NoChar', 'NoChar' union all
select 'Char26'+char(26), 'NoChar', 'NoChar' union all
select 'Char27'+char(27), 'NoChar', 'NoChar' union all
select 'Char28'+char(28), 'NoChar', 'NoChar' union all
select 'Char29'+char(29), 'NoChar', 'NoChar' union all
select 'Char30'+char(30), 'NoChar', 'NoChar' union all
select 'Char31'+char(31), 'NoChar', 'NoChar' union all
select 'Char32'+char(32), 'NoChar', 'NoChar'
It returned all the ones it was supposed to, and none of the ones it wasn't supposed to.
The reason it won't work as written in SQL 2000 isn't because of coalesce, which does exist in that, but because the system tables "sys.columns" and "sys.tables" are different in SQL 2000. You would need to replace them with "dbo.syscolumns" and "dbo.sysobjects".
I'm not sure why it would only return char(31) for you. Is it possible that there's a typo in your copy of the code?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2009 at 7:20 am
Hi There,
I modified your code a bit, so that I can run a single table at a time, and not every table.. Well at least that is what I think I have:
create function Cols
(@Table_in nvarchar(128))
returns nvarchar(max)
as
begin
declare @Cols nvarchar(max)
select @Cols = coalesce(@cols + '+isnull([' + name + '], '''')', 'isnull([' + name + '], '''')')
from sys.columns
where object_id = object_id(@Table_in)
and system_type_id in (35,99,167,175,231,239)
return @Cols
end;
go
Create Procedure sp_AsciiInTable2 @table nvarchar(max)
AS
declare @Char0 char(1), @Char31 char(1), @SQL nvarchar(max);
select @Char0 = char(0), @Char31 = char(31);
select @SQL = 'select * from dbo.' + @table + ' where ' + dbo.Cols(@table) + ' like ''%[' + @Char0 + '-' + @Char31 + ']%'''
from sys.tables
where object_id in
(select object_id
from sys.columns
where system_type_id in (35,99,167,175,231,239));
exec (@SQL);
And then I execute it:
create function Cols
(@Table_in nvarchar(128))
returns nvarchar(max)
as
begin
declare @Cols nvarchar(max)
select @Cols = coalesce(@cols + '+isnull([' + name + '], '''')', 'isnull([' + name + '], '''')')
from sys.columns
where object_id = object_id(@Table_in)
and system_type_id in (35,99,167,175,231,239)
return @Cols
end;
go
Create Procedure sp_AsciiInTable2 @table nvarchar(max)
AS
declare @Char0 char(1), @Char31 char(1), @SQL nvarchar(max);
select @Char0 = char(0), @Char31 = char(31);
select @SQL = 'select * from dbo.' + @table + ' where ' + dbo.Cols(@table) + ' like ''%[' + @Char0 + '-' + @Char31 + ']%'''
from sys.tables
where object_id in
(select object_id
from sys.columns
where system_type_id in (35,99,167,175,231,239));
exec (@SQL);
EXEC sp_AsciiInTable2 Test1
And the result is:
33;Char31;NoChar;NoChar
(one row)...
It returns the same for the table (Test1), if I run this:
declare @Char0 char(1), @Char31 char(1), @SQL nvarchar(max);
select @Char0 = char(0), @Char31 = char(31);
select @SQL = coalesce(@SQL + ';
select * from dbo.' + name + ' where ' + dbo.Cols(name) + ' like ''%[' + @Char0 + '-' + @Char31 + ']%''',
'select * from dbo.' + name + ' where ' + dbo.Cols(name) + ' like ''%[' + @Char0 + '-' + @Char31 + ']%''')
from sys.tables
where object_id in
(select object_id
from sys.columns
where system_type_id in (35,99,167,175,231,239));
exec (@SQL);
But it returns all tables with values 31...
January 21, 2009 at 10:08 am
If you are using SQL Server 2000, why did you post your question in a SQL Server 2005 forum? By doing that you are sure to get responses that are SQL Server 2005 specific.
Please post your questions in the appropriate forum. Most people that will help you will see your posts regardless of where you post them, but it is best to put them in the correct forum to help you get the best (most correct) responses to you problem.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply