Showing posts with label records. Show all posts
Showing posts with label records. Show all posts

Thursday, March 8, 2012

changing records in tables

Hi,

I was just wondering... Is there any built-in function in MS SQL, which will
allow me to do rows' "capitalisation"?
Lets say that in database.table.name I've got:
"FOO BAR LTD.", which I want to change to "Foo Bar Ltd."
"Foo bar LTD.", which I want to be "Foo Bar Ltd.".
Is there any way of doing this or do I have to read it from database, change
in some script, and then insert it back into the table?

Hope it's all clear ;-)

Thank you,
MartinMartin Lukasik (marcin@.milea.pl.i.hate.this.spam) writes:

Quote:

Originally Posted by

I was just wondering... Is there any built-in function in MS SQL, which
will allow me to do rows' "capitalisation"?
Lets say that in database.table.name I've got:
"FOO BAR LTD.", which I want to change to "Foo Bar Ltd."
"Foo bar LTD.", which I want to be "Foo Bar Ltd.".
Is there any way of doing this or do I have to read it from database,
change in some script, and then insert it back into the table?


No, there is no built in for this. You could write your own scalar
UDF for this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi There,

Search for "Camel Case" in Google

With Warm Regards
Jatinder Singh
http://jatindersingh.blogspot.com

Changing record number

Hi,
I have cancelled some records due to null fields,then the record number is not coming in order.how to code it to get it in orderwow, that's a short description.........
So I'm gonna have to guess the detail. You are probably, from the brief description, experiencing a problem because your report is having trouble with the nulls. More information, including database type, OS, CR version always helps us better assess the problem.
I think you need a formula to convert fields likely to be nulls, eg:
@.AccountBalance

if IsNull({tablename.AccountBalance}) then
formula = 0
else
formula = {tablename.AccountBalance}
end if

I've deliberatley kept this simple to demonstrate the principles.

Dave

Saturday, February 25, 2012

Changing ORDER BY

Hi All
win 2k pro sql 2k IIS 5 ASP-vbscript
I got a stored proc which basically returns a set of records depending
on a category ID (or all if passed ID =0) - see below
The FAQ Category below is retuned mostly order by Title but in some
other areas of my site I need to return it orded by CatID.
I was wondering whats the best way to do this as I cant seem to pass a
column name to the procedure and put "ORDER BY @.SortOrder"
These are the other ways I thought of.
1) create a new procedure the same with order by catID.
2) pass a number and use CASE - but I get error 1008
eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
I can see this becoming a common problem so wouldn't like to do #1
above as that would require alot of maintence to keep them the same if
I changed any of the table design.
Thanks for any help.
Al
CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
@.SelCatID TINYINT = 0
AS
SET NOCOUNT ON
-- Return all the FAQ Categories with Cat ID of the selected Category
SELECT CatID, ParentID, Title, FaqCount
FROM Help_FAQ_Categories
WHERE CatID= CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
ORDER BY Title
GOTry
ORDER BY CASE WHEN @.sort = 'col1' THEN col1 END ASC,
"Harag" <harag@.softhome.net> wrote in message
news:3v4mlvo18e9vbjisl08jqrs9m75pc240gd@.4ax.com...
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
>
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO
>|||Harag,
it is almost correct. However, you cannot mix data types without
explicit conversions. The typical solution to this type of problem is to
use an ORDER BY column for each sort order:
ORDER BY CASE WHEN @.Order=0 THEN Title END,
CASE WHEN @.Order=1 THEN CatID END
Gert-Jan
Harag wrote:
> Hi All
> win 2k pro sql 2k IIS 5 ASP-vbscript
> I got a stored proc which basically returns a set of records depending
> on a category ID (or all if passed ID =0) - see below
> The FAQ Category below is retuned mostly order by Title but in some
> other areas of my site I need to return it orded by CatID.
> I was wondering whats the best way to do this as I cant seem to pass a
> column name to the procedure and put "ORDER BY @.SortOrder"
> These are the other ways I thought of.
> 1) create a new procedure the same with order by catID.
> 2) pass a number and use CASE - but I get error 1008
> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
> I can see this becoming a common problem so wouldn't like to do #1
> above as that would require alot of maintence to keep them the same if
> I changed any of the table design.
> Thanks for any help.
> Al
> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
> @.SelCatID TINYINT = 0
> AS
> SET NOCOUNT ON
> -- Return all the FAQ Categories with Cat ID of the selected Category
> SELECT CatID, ParentID, Title, FaqCount
> FROM Help_FAQ_Categories
> WHERE CatID=> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
> ORDER BY Title
> GO|||Cool thanks guys this is what I wanted and will save alot of
maintence. :)
Thanks again
Al
On Sun, 07 Sep 2003 14:01:35 +0200, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>Harag,
>it is almost correct. However, you cannot mix data types without
>explicit conversions. The typical solution to this type of problem is to
>use an ORDER BY column for each sort order:
>ORDER BY CASE WHEN @.Order=0 THEN Title END,
> CASE WHEN @.Order=1 THEN CatID END
>Gert-Jan
>
>Harag wrote:
>> Hi All
>> win 2k pro sql 2k IIS 5 ASP-vbscript
>> I got a stored proc which basically returns a set of records depending
>> on a category ID (or all if passed ID =0) - see below
>> The FAQ Category below is retuned mostly order by Title but in some
>> other areas of my site I need to return it orded by CatID.
>> I was wondering whats the best way to do this as I cant seem to pass a
>> column name to the procedure and put "ORDER BY @.SortOrder"
>> These are the other ways I thought of.
>> 1) create a new procedure the same with order by catID.
>> 2) pass a number and use CASE - but I get error 1008
>> eg ORDER BY CASE WHEN @.Order=0 THEN Title ELSE CatID END
>> I can see this becoming a common problem so wouldn't like to do #1
>> above as that would require alot of maintence to keep them the same if
>> I changed any of the table design.
>> Thanks for any help.
>> Al
>> CREATE PROCEDURE dbo.usp_Help_FindFAQCategoryByCatID
>> @.SelCatID TINYINT = 0
>> AS
>> SET NOCOUNT ON
>> -- Return all the FAQ Categories with Cat ID of the selected Category
>> SELECT CatID, ParentID, Title, FaqCount
>> FROM Help_FAQ_Categories
>> WHERE CatID=>> CASE WHEN @.SelCatID=0 THEN CatID ELSE @.SelCatID END
>> ORDER BY Title
>> GO

Changing nVarChar lengths

I have a db with about 6.5 million records. A few
records have a set number of characters, however the
table design specifies more characters than is really
necessary. I'd like to reclaim as much space as
possible. Am I safe to change the field lengths for the
Char fields to the maximum number of characters I know
they will ever contain, without losing any data? Will I
have to change to nVarChar? If so, any data loss?If your sure the new size is larger than the largest piece of data for any
row you should not have a problem in changing it from a database standpoint.
But you may break existing code or apps if you do. That depends on how they
use it etc.
--
Andrew J. Kelly
SQL Server MVP
"Mike" <tatemike44@.hotmail.com> wrote in message
news:033201c36e6e$8574ad20$a401280a@.phx.gbl...
> I have a db with about 6.5 million records. A few
> records have a set number of characters, however the
> table design specifies more characters than is really
> necessary. I'd like to reclaim as much space as
> possible. Am I safe to change the field lengths for the
> Char fields to the maximum number of characters I know
> they will ever contain, without losing any data? Will I
> have to change to nVarChar? If so, any data loss?

Sunday, February 19, 2012

changing indexes

Hi, My application goes through a lengthy data processing phase before
it's ready to run. During the DP phase I'm mostly inserting records,
afterwards many of my tables will be read only. Is there anything
wrong with dropping indexes used for the DP and setting up clustered
indexes afterwards? Can I just declare my clustered indexes and go,
or do I have run some kind of statistics ...?Hi

There is nothing wrong in doing this, but it sounds like you are possibly
trying to reinvent what analysis services does!!!

If you are inserting a significant number of records your indexes may be
fragmented and therefore defragmenting them or re-creating them are
necessary activities. You may wish to change the fillfactor if your tables
are read only and have a lower fill factor whilst loading and 100% fill
factor when readonly.

You may also want to consider moving this data load offline into a separate
database, then you swap in the new data when it has been built and reduce
downtime. The whole database could be read only!

John

"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1128630847.225685.79080@.g43g2000cwa.googlegro ups.com...
> Hi, My application goes through a lengthy data processing phase before
> it's ready to run. During the DP phase I'm mostly inserting records,
> afterwards many of my tables will be read only. Is there anything
> wrong with dropping indexes used for the DP and setting up clustered
> indexes afterwards? Can I just declare my clustered indexes and go,
> or do I have run some kind of statistics ...?

Changing identity column value

Hi. Is there a way to remove a gap between identity column values? Some records were deleted and now we have identity column values that jump all over the place.

Normally, this is not a good idea. There are typically a number of problems that you add to your list of problems whenever you try to "solve this kind of problem." Yes, this can be done, however, some problems that you will have to address include:

Taking the table "offline"

Resolving referential integrity problems.

Dropping and recreating the table

verifying that the change is "correct"

|||Programatically you can do that using :


SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

(see Books Online)

if you have the order of identity:
1,2,5,6

with command above you can insert the records 3 and 4

Thursday, February 16, 2012

Changing field props in SQL Management Studio

Hi,
I have average database with something like 15M records. I have to change
some fields property of tables with average 4M records. On smaller tables I
can change one field at time and save design would be successful but on
larger tables I got timeout something like 25 seconds after pressing OK.
Where I can change timeout period or disable it?
Tried to put 0 on Connection window, changed to 0 remote query timeout, but
it does not change anything.
Thanks
RobertI strongly encourage you to do these changes using ALTER TABLE instead of the GUI since the GUI can
be quite clumsy regarding the SQL it generates.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Piasecznik" <WYpiasecznikWAL@.op.pl> wrote in message news:frdfec$1gmi$1@.news2.ipartners.pl...
> Hi,
> I have average database with something like 15M records. I have to change some fields property of
> tables with average 4M records. On smaller tables I can change one field at time and save design
> would be successful but on larger tables I got timeout something like 25 seconds after pressing
> OK.
> Where I can change timeout period or disable it?
> Tried to put 0 on Connection window, changed to 0 remote query timeout, but it does not change
> anything.
> Thanks
> Robert
>