Inserting varchar to Decimal/Numeric

Hi,

Can we insert varchar values to Decimal(9,2)/numeric(9,2) fields?

I've a temp table with varchar values. I checked using isnumeric and all the values are numeric. But when I do the insert, it fails.

I was able to insert the following values:

000290165
000501075
000290165
000326314

But NOT the following:

010773474

All the values are 9 digits only.......

How can I convert this kind of numbers?

I need to convert all the 9 digit numbers to XXXXXXX.xx format.

(7digits.2 digits)

Thanks,

Siva.

I believe that in this case your expectations are incorrect. When I run:

Code Snippet

select inpt,
convert(numeric(9,2), inpt)
as convertedValue
from ( select '000290165' as inpt union all
select '000501075' union all
select '000290165' union all
select '000326314' union all
select '010773474'
) a

/*
inpt convertedValue
--
000290165 290165.00
000501075 501075.00
000290165 290165.00
000326314 326314.00

(5 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
*/

The output is as expected. But I don't think that is what you are expecting. To get this correct try:

Code Snippet

select inpt,
convert(numeric(9,2), left(inpt,7) + '.' + right(inpt,2))
as convertedValue
from ( select '000290165' as inpt union all
select '000501075' union all
select '000290165' union all
select '000326314' union all
select '010773474'
) a

/*
inpt convertedValue
--
000290165 2901.65
000501075 5010.75
000290165 2901.65
000326314 3263.14
010773474 107734.74
*/

|||

Wow!! Great!

It worked like a magic!!

Thanks much...

Siva.

inserting values for alias names within tables?

hi every1

I have a database table in which one of the fields is an alias for the identity field. That is the alias field self references the table and takes its value for the "id" field of tht table
as follows:
system_id int
system_name varchar(20)
sys_alias int
sys_pro varchar(80)
sys_values varchar(80)
so here the sys_alias takes wtever value the system assigns to the id field, system_id while inserting values into the table..
now my problem is i have to insert records into ths table frm aother tables using insert into...select from statements bt since the id values r enerated by the comp during value insertion i dunno how to give values fr the alias?

e.g insert into my_table(system_name,sys_alias,sys_pro,sys_values)
select x,(how to give ths field),y,z
from another_table
where...

neone who understood my problem and can help puhllezz post me a reply asap!
thnx:)

shuchiSounds like you'd have to do this in a trigger.|||what is the expression for the "alias" column? is it exactly the same value, or is it something like a character prefix plus the value?

perhaps you might consider using a view instead

rudy|||hi...
its exactly the same value..jus like a copy column fr the identity column..
i tried using max(syb_identity) and @.@.identity functions but they do not work specially since i put them in a sub query so theyre not calculated recursivelye but just once and insert the same identity value for all other inserted records!
can anyone help me what sortof view i shud create?
i am doing ths whole process thru a perl script so whatever sql tht needs to be done is done thru an sql file run from my perl script. mebbe asome sorta trigger will work tht automatically inserts the newest value of identityt generated into the alias field while insertion of records?
gosh i really need help here...so any ideas wud be gr8:)
thnx fr all the suggestions:)

-shuchi|||if this "alias" column is to have exactly the same values, then you don't really need it

just select it twice in any query -- select system_id
, system_name
, system_id as sys_alias
, sys_pro
, sys_values
from yourtable

rudy|||thanx a ton fr all the help...managed it:)

-shuchi

Inserting Unicode data

Hi.

We have a sql server db that we need to store Unicode text in. The fields are of the type nvarchar, ntext and nchar. Our solution uses both Oracle and SqlServer as a backing database. In Oracle there is a connection string switch "Unicode=True" that fixes the problem. Is there something similar in SqlServer? Since the db layer is generic we'ed like to avoid using a N' prefix on text strings in query statements.

Hi Kim,

As far as I can see, when the fields types are set to such as NVarChar and the update parameters have been set to the corresponding type, the data will be updated as Unicode in SQL Server. When updating the database, the N' prefix will be added automatically by ADO.NET.

That means you don't need to add anything additional to achieve this.

Are you getting some problem when updating the data in the way I mentioned? If so, please let me know the problem. Thanks!

|||

Hi Kevin,

Thank you for the response. The problem is that this is a system that's gone into production. The code is not written by me and I'm sorry to say it doesn't use command parameters for transfering variables. I guess the fix will have to wait until the next upgrade.

inserting to multiple fields using a button

I have multiple textboxes in a page. How do i make them insert their values to multiple fields on multiple tables using a button.

You can just execute some SqlCommands to insert data to different tables in the Click event of a button, for example:

protected void Button2_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(@."Data Source=labsh96223\iori2000;Integrated Security=SSPI;Database=tempdb"))
{
conn.Open();
string insertSql = @."Insert into myTbl_1 select @.id, @.name";
SqlCommand myCommand = new SqlCommand(insertSql, conn);
myCommand.Parameters.Add("@.id", SqlDbType.Int);
myCommand.Parameters.Add("@.name", SqlDbType.VarChar, 100);
myCommand.Parameters["@.id"].Value = Int32.Parse(TextBox1.Text);
myCommand.Parameters["@.name"].Value = TextBox2.Text;
int i = myCommand.ExecuteNonQuery();
myCommand.CommandText = @."Insert into myTbl_2 select @.id, @.Description";
myCommand.Parameters.Clear();
myCommand.Parameters.Add("@.id", SqlDbType.Int);
myCommand.Parameters.Add("@.Description", SqlDbType.VarChar, 1000);
myCommand.Parameters["@.id"].Value = Int32.Parse(TextBox3.Text);
myCommand.Parameters["@.name"].Value = TextBox4.Text;
i= myCommand.ExecuteNonQuery();
//execute other commands
}
}

Inserting Symbols into Fields (like ?)

I am attempting to insert a trademark symbol? into a database field but it is not working. Does anyone have any ideas on how to do this properly?

Any help would be very appreciated.

Since the Symbol ? is Ascii charater it should insert on Both Varchar & Nvarchar fields properly. The equalent to the symbol ? is char(153)

|||

insert mytable
(col1)
values
(char(153))

Inserting same identity value into two linked tables

Hi,
I have two tables which has 2 linked fields, as David Portas posted in
'Field value determines whether there is extra info that should be held
about that record':
CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type
INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many
different types you have */), UNIQUE (event_no, event_type) /* ...
other columns for events */) ;
CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY,
event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN
KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON
DELETE CASCADE /* ... other columns for event type 1 */) ;
I've set the event_no identity property in table 'events' to true. I have a
stored procedure to insert data to both tables at once (using 2 INSERT INTO
statements coming one after another).
The problem is that currently I'm using ADO to generate the new ID (By
selecting MAX of the event_no, adding 1 to the MAX value, and using the
result as the new event_no) which is sent as a parameter to the stored
procedure, which uses it in the 2 INSERT INTO statements.
This method is working fine by now, but could potentially cause problems.
How can generate a new value for that event_no identity column, then use it
in both the INSERT INTO statements?
I've read about @.@.IDENTITY, but I'm not sure about how to use it. I've also
read that there are better options, but these are not relevant since I'm
using SQL Server 7.
Kind Regards,
Amir.Amir wrote:
> Hi,
> I have two tables which has 2 linked fields, as David Portas posted in
> 'Field value determines whether there is extra info that should be held
> about that record':
> CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type
> INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many
> different types you have */), UNIQUE (event_no, event_type) /* ...
> other columns for events */) ;
> CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY,
> event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN
> KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON
> DELETE CASCADE /* ... other columns for event type 1 */) ;
> I've set the event_no identity property in table 'events' to true. I have
a
> stored procedure to insert data to both tables at once (using 2 INSERT INT
O
> statements coming one after another).
> The problem is that currently I'm using ADO to generate the new ID (By
> selecting MAX of the event_no, adding 1 to the MAX value, and using the
> result as the new event_no) which is sent as a parameter to the stored
> procedure, which uses it in the 2 INSERT INTO statements.
> This method is working fine by now, but could potentially cause problems.
> How can generate a new value for that event_no identity column, then use i
t
> in both the INSERT INTO statements?
> I've read about @.@.IDENTITY, but I'm not sure about how to use it. I've als
o
> read that there are better options, but these are not relevant since I'm
> using SQL Server 7.
> Kind Regards,
> Amir.
Use @.@.IDENTITY to retrieve the last inserted IDENTITY value. Like this
for example:
CREATE TABLE events (event_no INTEGER IDENTITY NOT NULL PRIMARY KEY,
event_name VARCHAR(10) NOT NULL UNIQUE, event_type INTEGER NOT NULL
CHECK (event_type BETWEEN 1 AND 10), UNIQUE (event_no, event_type) ) ;
INSERT INTO events (event_name, event_type)
VALUES ('foo',1);
SELECT @.@.IDENTITY AS last_identity_value ;
David Portas
SQL Server MVP
--|||Thanks, David!
Kind Regards,
Amir.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1137159522.649050.309090@.g47g2000cwa.googlegroups.com...
> Amir wrote:
> Use @.@.IDENTITY to retrieve the last inserted IDENTITY value. Like this
> for example:
> CREATE TABLE events (event_no INTEGER IDENTITY NOT NULL PRIMARY KEY,
> event_name VARCHAR(10) NOT NULL UNIQUE, event_type INTEGER NOT NULL
> CHECK (event_type BETWEEN 1 AND 10), UNIQUE (event_no, event_type) ) ;
> INSERT INTO events (event_name, event_type)
> VALUES ('foo',1);
> SELECT @.@.IDENTITY AS last_identity_value ;
> --
> David Portas
> SQL Server MVP
> --
>

Inserting Nulls with DTS

I am using a DTS package to copy data from a flat file to a table. I need for fields that are empty to be changed to nulls in the output. I wrote a VB Script to do this, but now it takes about 10 times longer that when I used the Copy transformation. Is there a faster way to do this?

I haven't tried the Trim transformation yet. (I'm waiting for my load to finish.) Will that place nulls in the output if a field is empty?Originally posted by jsneeringer
I am using a DTS package to copy data from a flat file to a table. I need for fields that are empty to be changed to nulls in the output. I wrote a VB Script to do this, but now it takes about 10 times longer that when I used the Copy transformation. Is there a faster way to do this?

I haven't tried the Trim transformation yet. (I'm waiting for my load to finish.) Will that place nulls in the output if a field is empty?

Hi,
1-in your SQL Server Table ( Destination DB) set a default value for the field(s), so when you attemp to insert Null value in that Field, the specified default vale will be insert. ( the way of inserting is not important . It can be DTS!!)

2- you can also write a Instead Of Trigger on your Table For Insert, so process the field value, if it's Null , write empty.

Hope that help you

Inserting NULL values on Date Fields trhough DAL

I am using a DAL and i want to insert a new row where one of the columns is DATE and itcanbe 'NULL'.

I am assigning SqlTypes.SqlDateTime.Null.

But when the date is saved in the database, i get the minvalue (1/01/1900) . Is there a way to put the NULL value in the database using DAL??

how can i put an empty date in the database?

THANK YOU!!!

check ifthishelps.|||

That does not work. Besides, in your example you are NOT using DAL. You are inserting directly to the DB, through a SQL statement.

Thannks,

JeffKish

|||

The code in the link below uses ADO.NET parameters. Hope this helps.

http://www.c-sharpcorner.com/Code/2003/Sept/EnterNullValuesForDateTime.asp