| Author |
 Topic  |
|
|
cirugio
Yak Posting Veteran
88 Posts |
 Posted - 03/20/2012 : 15:19:33
|
I currently have a sql table which we receive from the vendor that houses over 800 fields. The vendor provides us with the maximum size of each field, even though our company may not utilize the entire length for that specific field. For Example:
dbo.CustData
Instrument_ID (nvarchar(40),null)
Term_maturity (numeric(8,0),null)
Payment_amt (numeric(18,3),null)
.
.
.
I would like to rewrite this table so that each field is smaller. I was utilizing the ALTER TABLE ALTER COLUMN commands and it works fine when I increase/decrease the size of my numeric fields. I just get an error when I try to reduce the size of my
nvarchar field. The error basically states "String or binary data would be truncated. The statement has been terminated". Is there a way around this. Below is the query I created:
ALTER TABLE CustData ALTER COLUMN Instrument_ID nvarchar(20),null;
ALTER TABLE CustData ALTER COLUMN Term_maturity numeric(10,0), null;
ALTER TABLE CustData ALTER COLUMN Payment_amt numeric(15,5),null;
Any suggestions or better way of doing this would be greatly appreciated it.
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/20/2012 : 15:22:07
|
that means you already have some data in field which is having length above the one you're trying to set it
so first check that and make sure you set it having length enough to hold current data
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
cirugio
Yak Posting Veteran
88 Posts |
Posted - 03/20/2012 : 15:36:17
|
Thanks Visakh16,
So what you are telling me, is that basically it will never make the field smaller if it truncates values. Is there a way around this, or I have no choice?
Also, can you tell me, is there a way to have one Alter Table statement when modifying mutliple columns? If yes, would you mind providing me an example? thanks again |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 03/20/2012 : 15:41:57
|
quote:
Originally posted by cirugio
Thanks Visakh16,
So what you are telling me, is that basically it will never make the field smaller if it truncates values. Is there a way around this, or I have no choice?
Also, can you tell me, is there a way to have one Alter Table statement when modifying mutliple columns? If yes, would you mind providing me an example? thanks again
If you want to reduce the size of column that already has data larger than you want to make it you need to update the column first with the "truncated" value; then alter the column. |
Edited by - Lamprey on 03/20/2012 15:42:30 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/20/2012 : 15:42:33
|
it it already have values it wont truncate automatically. you've to first apply update logic to truncate it and then do ALTER ....COLUMN
also ALTER TABLE.... ALTER COLUMN ... has to be repeated for each column
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
cirugio
Yak Posting Veteran
88 Posts |
Posted - 03/20/2012 : 15:43:43
|
How about if I just want to truncate whats already in there?
|
|
|
|
cirugio
Yak Posting Veteran
88 Posts |
Posted - 03/20/2012 : 15:52:03
|
Visakh16, can you provide me with an example of how I would update it with the truncated value. Sorry, I am a bit unclear.
For instance:
. if the field I want to truncate is: Instrument_id nvarchar(40) and contains the value of 'John Smith 445 N. Denvor Street, Colardo'
. and I want to it to truncate to 30 characters so that the field would contain 'John Smith 445 N. Denvor Stree' without specifically typing in the values in the update statement.
how would I use the update function ? Would I have to use a substring in the update? Sorry, I am fairly new to sql, so have a little learning curve. Thanks again. |
Edited by - cirugio on 03/20/2012 15:54:48 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/20/2012 : 15:58:48
|
quote:
Originally posted by cirugio
Visakh16, can you provide me with an example of how I would update it with the truncated value. Sorry, I am a bit unclear.
For instance:
. if the field I want to truncate is: Instrument_id nvarchar(40) and contains the value of 'John Smith 445 N. Denvor Street, Colardo'
. and I want to it to truncate to 30 characters so that the field would contain 'John Smith 445 N. Denvor Stree' without specifically typing in the values in the update statement.
how would I use the update function ? Would I have to use a substring in the update? Sorry, I am fairly new to sql, so have a little learning curve. Thanks again.
you would do it like
UPDATE table
SET Column=LEFT(Column,30)
but why should you do it that way
are you aware that this will cause of loss of information. I wont do that unless I'm sure that whatever data it had was junk or had no relevance
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
cirugio
Yak Posting Veteran
88 Posts |
Posted - 03/20/2012 : 16:03:28
|
| Perfect! Thank you Visakh16. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/20/2012 : 16:06:12
|
quote:
Originally posted by cirugio
Perfect! Thank you Visakh16.
you didnt answer my question
are you aware of consequences??
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
cirugio
Yak Posting Veteran
88 Posts |
Posted - 03/20/2012 : 16:46:46
|
| Yes, I am aware of the consequences. The data that is in the tables is basically garbage test data that was not cleared out from when the applications was in UAT. The fields which I would be working with are just user fields and do not relate to production data. My changes will not impact anything. Thank you for validating. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/20/2012 : 16:59:10
|
ok...then fine with that
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
reducing the size of a column in sql table
