We do a lot of inserts into a table that has a primary key on an identity
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Friday, March 9, 2012
Insert-Retrive-Update in Text datatype.
Can anyone guide how to retrieve, update and insert data in column having 'Text' datatype? I am a newbie and i havent' done this yet.
Thanks in advance.
If you are using SQL Server 2005 consider using a VARCHAR(MAX) or NVARCHAR(MAX) instead of a text datatype. Look up the VARCHAR(MAX) and TEXT datatypes in books online.|||No. i am using sql 2000 and i wanted to know the method of retriving and updating data usign text datatypes.
thanksk,
|||Use it in the same manner that you would use a normal varchar column. The only difference is that when using it in a where condition you can't use the '=' operator. You have to use 'like'. Other than that inserting, updating and retrieving is exactly the same.Best regards,
Sami Samir|||
Please see the links below for some samples. For text/ntext/image value manipulation, you can use UPDATETEXT/WRITETEXT/READTEXT or SELECT/INSERT/UPDATE statements depending on your needs. There are functions like DATALENGTH and PATINDEX that will work with these data types too.
http://www.umachandar.com/technical/SQL6x70Scripts/Main53.htm
http://www.umachandar.com/technical/SQL6x70Scripts/Main63.htm
http://www.umachandar.com/technical/SQL6x70Scripts/Main12.htm
Insertion data via Stored Procedure [URGENT]!
Hi all;
Question:
=======
Q1) How can I insert a record into a table "Parent Table" and get its ID (its PK) (which is an Identity "Auto count" column) via one Stored Procedure??
Q2) How can I insert a record into a table "Child Table" whose (FK) is the (PK) of the "Parent Table"!! via another one Stored Procedure??
Example:
----
I have two tables "Customer" and "CustomerDetails"..
SP1: should insert all "Customer" data and return the value of an Identity column (I will use it later in SP2).
SP2: should insert all "CustomerDetials" data in the record whose ID (the returned value from SP1) is same as ID of the "Customer" table.
FYI:
--
MS SQL Server 2000
VS.NET EA 2003
Win XP SP1a
VB.NET/ASP.NET :)
Thanks in advanced!There are a couple of ways to get the last inserted IDENT, but I prefer to use IDENT_CURRENT('table_name') to get the ID from the last inserted record of thespecified table.
|||Ok, what about if I want to do these process in two SPs??
INSERT INTO Customer (val1,val2,etc) VALUES (@.Val1,@.val2,etc)INSERT INTO CustomerDetails (ID,Val1,Val2, etc) VALUES ((IDENT_CURRENT('Customer'),@.val1,@.val2,etc)
I want to take the IDDENTITY value from the fitrst "INSERT INTO" statment, because I need this value in my source code as well as other Stored Procedure(s).
Thanks in advanced!|||Insert Blah...;
Return SCOPE_IDENTITY()|||Thanks gays.
I think my problem is how to get the returned value (IDDENTITY value) from the VB.NET code (in other words, how to extract it from VB.NET/ADO.NET code)?
Note:
--
VB.NET/ADO.NET
or
C#.NET/ADO.NET
Are are ok, if you would like to demonstrate your replay. ( I want the answer!).
Thanks again.|||YES!!
The problem was from my ADO.NET part.
Thanks for your help gays.
Wednesday, March 7, 2012
Inserting without using INSERT INTO
Hi,
How do i insert a value into varbinary column using ASP
Here i dont want to use "INSERT INTO"
Tx.
DNKHi
if your concern is to hide sql then alternative is to use StoredProcedure
with encryption.
Regards
R.D
"DNKMCA" wrote:
> Hi,
> How do i insert a value into varbinary column using ASP
> Here i dont want to use "INSERT INTO"
> Tx.
> DNK
>
>
How do i insert a value into varbinary column using ASP
Here i dont want to use "INSERT INTO"
Tx.
DNKHi
if your concern is to hide sql then alternative is to use StoredProcedure
with encryption.
Regards
R.D
"DNKMCA" wrote:
> Hi,
> How do i insert a value into varbinary column using ASP
> Here i dont want to use "INSERT INTO"
> Tx.
> DNK
>
>
Inserting without using INSERT INTO
Hi,
How do i insert a value into varbinary column using ASP
Here i dont want to use "INSERT INTO"
Tx.
DNKSorry, "INSERT INTO" is the answer if you want to insert. Maybe you
could you explain why you don't want to if that's not the answer you
are looking for.
Note however that you insert into *tables* not a column. Perhaps you
just mean you want to update a column in which case use UPDATE.
David Portas
SQL Server MVP
--|||I want to know is there a way i can put a value into VARBINARY Column thru
ASP Code.
-DNK
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1126860791.506472.201910@.f14g2000cwb.googlegroups.com...
> Sorry, "INSERT INTO" is the answer if you want to insert. Maybe you
> could you explain why you don't want to if that's not the answer you
> are looking for.
> Note however that you insert into *tables* not a column. Perhaps you
> just mean you want to update a column in which case use UPDATE.
> --
> David Portas
> SQL Server MVP
> --
>|||All updates should normally be done through stored procedures so the
answer is to call a proc using the ADO command object. The proc can
then perform the INSERT.
David Portas
SQL Server MVP
--|||DNKMCA (dnk@.msn.com) writes:
> I want to know is there a way i can put a value into VARBINARY Column thru
> ASP Code.
> -DNK
If you want a sample about ASP, you may want to ask in an ASP forum.
From the SQL side of things, varbinary is not that much different than
any other data value.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
How do i insert a value into varbinary column using ASP
Here i dont want to use "INSERT INTO"
Tx.
DNKSorry, "INSERT INTO" is the answer if you want to insert. Maybe you
could you explain why you don't want to if that's not the answer you
are looking for.
Note however that you insert into *tables* not a column. Perhaps you
just mean you want to update a column in which case use UPDATE.
David Portas
SQL Server MVP
--|||I want to know is there a way i can put a value into VARBINARY Column thru
ASP Code.
-DNK
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1126860791.506472.201910@.f14g2000cwb.googlegroups.com...
> Sorry, "INSERT INTO" is the answer if you want to insert. Maybe you
> could you explain why you don't want to if that's not the answer you
> are looking for.
> Note however that you insert into *tables* not a column. Perhaps you
> just mean you want to update a column in which case use UPDATE.
> --
> David Portas
> SQL Server MVP
> --
>|||All updates should normally be done through stored procedures so the
answer is to call a proc using the ADO command object. The proc can
then perform the INSERT.
David Portas
SQL Server MVP
--|||DNKMCA (dnk@.msn.com) writes:
> I want to know is there a way i can put a value into VARBINARY Column thru
> ASP Code.
> -DNK
If you want a sample about ASP, you may want to ask in an ASP forum.
From the SQL side of things, varbinary is not that much different than
any other data value.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Inserting Values to Identity column.
Hi all,
I have a Table Which only have one column, and that column is also an Identity column. Does anybody know how to insert values to this table. I know that if I change the table and add another column I can solve this problem. But because I can't change the of the Database design provided to me by my architect, I have to find a solution for this.
Please help me......
Regards,
Sandarenu
you could use the statement "SET IDENTITY_INSERT ON" before the insert statement.
you can read about the usage from books online
Inserting values into a column by selecting value from different table
Hi, I have a question regarding how to insert one column values into a table by selecting from different table..Here is the syntax..
----
insert into propertytable values (select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE', 13926, 0, 4, 1, 451, 1, 8, 1)
the first column in the propertytable will be... select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE'
How do I do that..Help PLZ..There are probably a number of ways to accomplish this...my preference would be to use a local variable to hold the value.
declare @.seq varchar(20); --[whatever...could be int]
select @.seq = lastvalue+incrementby
from agilesequences
where name='SEQPROPERTYTABLE';
insert into propertytable values (@.seq, 13926, 0, 4, 1, 451, 1, 8, 1);
--------
This should work unless I fat-fingered something.
There may be a way to do this in one pop...
--------|||INSERT INTO propertytable(ColName)
SELECT lastvalue+incrementby
FROM agilesequences
WHERE name='SEQPROPERTYTABLE'
What's this?
13926, 0, 4, 1, 451, 1, 8, 1
And You should supply a collist for the inserted tables...
Damn another fluff reply...|||There are probably a number of ways to accomplish this...my preference would be to use a local variable to hold the value.
declare @.seq varchar(20); --[whatever...could be int]
select @.seq = lastvalue+incrementby
from agilesequences
where name='SEQPROPERTYTABLE';
insert into propertytable values (@.seq, 13926, 0, 4, 1, 451, 1, 8, 1);
--------
This should work unless I fat-fingered something.
There may be a way to do this in one pop...
--------
Thanks a lot guys..
This worked for me.
----
insert into propertytable values (select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE', 13926, 0, 4, 1, 451, 1, 8, 1)
the first column in the propertytable will be... select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE'
How do I do that..Help PLZ..There are probably a number of ways to accomplish this...my preference would be to use a local variable to hold the value.
declare @.seq varchar(20); --[whatever...could be int]
select @.seq = lastvalue+incrementby
from agilesequences
where name='SEQPROPERTYTABLE';
insert into propertytable values (@.seq, 13926, 0, 4, 1, 451, 1, 8, 1);
--------
This should work unless I fat-fingered something.
There may be a way to do this in one pop...
--------|||INSERT INTO propertytable(ColName)
SELECT lastvalue+incrementby
FROM agilesequences
WHERE name='SEQPROPERTYTABLE'
What's this?
13926, 0, 4, 1, 451, 1, 8, 1
And You should supply a collist for the inserted tables...
Damn another fluff reply...|||There are probably a number of ways to accomplish this...my preference would be to use a local variable to hold the value.
declare @.seq varchar(20); --[whatever...could be int]
select @.seq = lastvalue+incrementby
from agilesequences
where name='SEQPROPERTYTABLE';
insert into propertytable values (@.seq, 13926, 0, 4, 1, 451, 1, 8, 1);
--------
This should work unless I fat-fingered something.
There may be a way to do this in one pop...
--------
Thanks a lot guys..
This worked for me.
Inserting values in a datetime field
Hi All,
I have a datetime column in a table on the SQL database. I need to insert
values into the datetime column from vb.net code. Here is my code:
dim nameval, str, qry as string
nameval = "abc"
str = "2005/03/16 14:20"
qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," & "'"
str & "')"
...
..
ocmd.ExecuteNonQuery()
...
...
The error message that I get is as follows:
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated. .Net
SqlClient Data Provider"
The problem I think is due to passing a string for a datetime field. My
question is, if I convert the string to datetype using CDate(str), then I
would have to again convert the date to string in order to form the insert
statement. So, the ultimate result will be again passing a string for the
datetime field!
I know that this is a simple syntax problem, which I don't seem to get right
!
Would anybody be able to give me insert statement for the above?
Thanks.
kdkd
Format the parameter as 'YYYYMMDD'
"kd" <kd@.discussions.microsoft.com> wrote in message
news:FC6115C1-BD59-4B91-A237-6E10C38EB3A8@.microsoft.com...
> Hi All,
> I have a datetime column in a table on the SQL database. I need to insert
> values into the datetime column from vb.net code. Here is my code:
> dim nameval, str, qry as string
> nameval = "abc"
> str = "2005/03/16 14:20"
> qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," &
"'"
> str & "')"
> ...
> ..
> ocmd.ExecuteNonQuery()
> ...
> ...
> The error message that I get is as follows:
> "The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value. The statement has been terminated. .Net
> SqlClient Data Provider"
> The problem I think is due to passing a string for a datetime field. My
> question is, if I convert the string to datetype using CDate(str), then I
> would have to again convert the date to string in order to form the insert
> statement. So, the ultimate result will be again passing a string for the
> datetime field!
> I know that this is a simple syntax problem, which I don't seem to get
right!
> Would anybody be able to give me insert statement for the above?
> Thanks.
> kd
>
I have a datetime column in a table on the SQL database. I need to insert
values into the datetime column from vb.net code. Here is my code:
dim nameval, str, qry as string
nameval = "abc"
str = "2005/03/16 14:20"
qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," & "'"
str & "')"
...
..
ocmd.ExecuteNonQuery()
...
...
The error message that I get is as follows:
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value. The statement has been terminated. .Net
SqlClient Data Provider"
The problem I think is due to passing a string for a datetime field. My
question is, if I convert the string to datetype using CDate(str), then I
would have to again convert the date to string in order to form the insert
statement. So, the ultimate result will be again passing a string for the
datetime field!
I know that this is a simple syntax problem, which I don't seem to get right
!
Would anybody be able to give me insert statement for the above?
Thanks.
kdkd
Format the parameter as 'YYYYMMDD'
"kd" <kd@.discussions.microsoft.com> wrote in message
news:FC6115C1-BD59-4B91-A237-6E10C38EB3A8@.microsoft.com...
> Hi All,
> I have a datetime column in a table on the SQL database. I need to insert
> values into the datetime column from vb.net code. Here is my code:
> dim nameval, str, qry as string
> nameval = "abc"
> str = "2005/03/16 14:20"
> qry = "insert into tab1(name,dateval) values(" & "'" & nameval & "'," &
"'"
> str & "')"
> ...
> ..
> ocmd.ExecuteNonQuery()
> ...
> ...
> The error message that I get is as follows:
> "The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value. The statement has been terminated. .Net
> SqlClient Data Provider"
> The problem I think is due to passing a string for a datetime field. My
> question is, if I convert the string to datetype using CDate(str), then I
> would have to again convert the date to string in order to form the insert
> statement. So, the ultimate result will be again passing a string for the
> datetime field!
> I know that this is a simple syntax problem, which I don't seem to get
right!
> Would anybody be able to give me insert statement for the above?
> Thanks.
> kd
>
Inserting values
Dear All,
In sql-server2000
In a table i am having a column of datatype varchar(8000).
While inserting the record through executenonquery, i am insert only
255 characters rest of the characters are getting trucated.
My question in how i will able to insert the row of that particular
column more than 255 characters
Thanx in advance.
Regardsfix the query.
i dunno what the query is or the command parameter info, so you'll just have to read on how to change the length of that sqldbtype.|||Dear Kragie,
thanx for your reply.
i am using command parameter info is command text and and directly using "Insert command".
Regards
Inserting value to Identity Column
I want all users in database to be able to call Set IDENTITY_INSERT
[table] = ON
However when a user who is not the dbo logs in to execute the stored
procedure I get the following error message.
prcIdentityFudgeSave, Line 8
The current user is not the database or object owner of table
'IdentityFudge'. Cannot perform SET operation.
Please Help
(See Stored Prc below that the users are trying to execute)
CREATE PROCEDURE prcIdentityFudgeSave
(
@.IdentityBeforeTrigger int
) AS
--Fudge the Identity Column
delete From IdentityFudge
SET IDENTITY_INSERT IdentityFudge ON
Insert Into IdentityFudge (IndentityFudgeID) values
(@.IdentityBeforeTrigger)
GO> I want all users in database to be able to call Set IDENTITY_INSERT
> [table] = ON
Then why are you using IDENTITY at all?|||give permission to the SQL Server login [SQL Server authentication] / Window
login [Windows Authentication] to acess the table and sp.
"dermot" <dfrench@.tommyfrench.co.uk> wrote in message
news:1110465514.012467.136020@.f14g2000cwb.googlegroups.com...
>I want all users in database to be able to call Set IDENTITY_INSERT
> [table] = ON
> However when a user who is not the dbo logs in to execute the stored
> procedure I get the following error message.
> prcIdentityFudgeSave, Line 8
> The current user is not the database or object owner of table
> 'IdentityFudge'. Cannot perform SET operation.
> Please Help
> (See Stored Prc below that the users are trying to execute)
>
> CREATE PROCEDURE prcIdentityFudgeSave
> (
> @.IdentityBeforeTrigger int
> ) AS
> --Fudge the Identity Column
> delete From IdentityFudge
> SET IDENTITY_INSERT IdentityFudge ON
> Insert Into IdentityFudge (IndentityFudgeID) values
> (@.IdentityBeforeTrigger)
> GO
>|||Joel,
I think that is not enough. See "SET IDENTITY_INSERT (Permissions)" in BOL.
AMB
"Joel Leong" wrote:
> give permission to the SQL Server login [SQL Server authentication] / Wind
ow
> login [Windows Authentication] to acess the table and sp.
> "dermot" <dfrench@.tommyfrench.co.uk> wrote in message
> news:1110465514.012467.136020@.f14g2000cwb.googlegroups.com...
>
>|||If the value in this column has meaning to your users then don't use
IDENTITY. Why would you want to do this?
David Portas
SQL Server MVP
--
[table] = ON
However when a user who is not the dbo logs in to execute the stored
procedure I get the following error message.
prcIdentityFudgeSave, Line 8
The current user is not the database or object owner of table
'IdentityFudge'. Cannot perform SET operation.
Please Help
(See Stored Prc below that the users are trying to execute)
CREATE PROCEDURE prcIdentityFudgeSave
(
@.IdentityBeforeTrigger int
) AS
--Fudge the Identity Column
delete From IdentityFudge
SET IDENTITY_INSERT IdentityFudge ON
Insert Into IdentityFudge (IndentityFudgeID) values
(@.IdentityBeforeTrigger)
GO> I want all users in database to be able to call Set IDENTITY_INSERT
> [table] = ON
Then why are you using IDENTITY at all?|||give permission to the SQL Server login [SQL Server authentication] / Window
login [Windows Authentication] to acess the table and sp.
"dermot" <dfrench@.tommyfrench.co.uk> wrote in message
news:1110465514.012467.136020@.f14g2000cwb.googlegroups.com...
>I want all users in database to be able to call Set IDENTITY_INSERT
> [table] = ON
> However when a user who is not the dbo logs in to execute the stored
> procedure I get the following error message.
> prcIdentityFudgeSave, Line 8
> The current user is not the database or object owner of table
> 'IdentityFudge'. Cannot perform SET operation.
> Please Help
> (See Stored Prc below that the users are trying to execute)
>
> CREATE PROCEDURE prcIdentityFudgeSave
> (
> @.IdentityBeforeTrigger int
> ) AS
> --Fudge the Identity Column
> delete From IdentityFudge
> SET IDENTITY_INSERT IdentityFudge ON
> Insert Into IdentityFudge (IndentityFudgeID) values
> (@.IdentityBeforeTrigger)
> GO
>|||Joel,
I think that is not enough. See "SET IDENTITY_INSERT (Permissions)" in BOL.
AMB
"Joel Leong" wrote:
> give permission to the SQL Server login [SQL Server authentication] / Wind
ow
> login [Windows Authentication] to acess the table and sp.
> "dermot" <dfrench@.tommyfrench.co.uk> wrote in message
> news:1110465514.012467.136020@.f14g2000cwb.googlegroups.com...
>
>|||If the value in this column has meaning to your users then don't use
IDENTITY. Why would you want to do this?
David Portas
SQL Server MVP
--
Friday, February 24, 2012
inserting text > 65535
hi,
i want to insert text of size > 65535 in a text column of sql-server. I'm presently using mcp command to do this, but this utility doesn't allow these long files, and work well upto 65 kb files.
is there a way out.
thanks in advanceThis error had been noted back in Version 4.2 of SQL, see technet article
BUG: BCP Cannot Copy Text/Image Data > 64K from SQL Server (Q111921) (http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q111921)
Try setting the packet size on BCP (flag -a) to the max which is 65535|||That doesn't work... I set the size 100000. When I inserted small files that were loaded smoothly. But for bigger files it says max packaet size is 65k.
I rephrase my problem again:::::
=====================
I'm working with sql server.
I need to load large xml files in the databse, fragment it into the
tables.
(1) I don't know a way to load file with the help of script that can load
text value of > 65k. I'm presently doing with the help of "mcp" command.
But this has got size limitation. So how do u load big file in some of the
"text" field of a table.
(2) I need to do fragmentation of this file. But I cannot declare text
type variable in the procedure. I am able to parse files of upto 8000
characters, but how do I do it if I have a document > 8000 char stored in
a "text" field. I just don't know how to call sp_xml_preparedocument with
a text attribute. I think u cannot say something like
sp_xml_preparedocument @.idoc out,(select textfield from doctable)
where idoc and textfield are integer and text respectively.
i want to insert text of size > 65535 in a text column of sql-server. I'm presently using mcp command to do this, but this utility doesn't allow these long files, and work well upto 65 kb files.
is there a way out.
thanks in advanceThis error had been noted back in Version 4.2 of SQL, see technet article
BUG: BCP Cannot Copy Text/Image Data > 64K from SQL Server (Q111921) (http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q111921)
Try setting the packet size on BCP (flag -a) to the max which is 65535|||That doesn't work... I set the size 100000. When I inserted small files that were loaded smoothly. But for bigger files it says max packaet size is 65k.
I rephrase my problem again:::::
=====================
I'm working with sql server.
I need to load large xml files in the databse, fragment it into the
tables.
(1) I don't know a way to load file with the help of script that can load
text value of > 65k. I'm presently doing with the help of "mcp" command.
But this has got size limitation. So how do u load big file in some of the
"text" field of a table.
(2) I need to do fragmentation of this file. But I cannot declare text
type variable in the procedure. I am able to parse files of upto 8000
characters, but how do I do it if I have a document > 8000 char stored in
a "text" field. I just don't know how to call sp_xml_preparedocument with
a text attribute. I think u cannot say something like
sp_xml_preparedocument @.idoc out,(select textfield from doctable)
where idoc and textfield are integer and text respectively.
inserting single precision data into sql server float column
Hi,
iam using the bcp api to load data into sql server. The data to be loaded
is single precision and hence my bcp_bind type is SQLFLT4. The column in my
sql server table is a FLOAT(which is of course double precision).
If i try to insert say a value 73.22 it gets inserted as 73.22000122070313.
I mean the documentation says that implicit conversion for these types are
allowed. So iam not sure why this happens.
Appreciate any inputs.
VivekHi Vivek,
Thats the way float works:
"Approximate-number data types for use with floating point numeric
data. Floating point data is approximate; therefore, not all values in
the data type range can be represented exactly. "
DECLARE @.SOMEValue Float(2)
SEt @.SomeValue = 1.100001
SELECT @.SOMEValue
For more precicion you have to use another database like decimal.
HTH, Jens Suessmeyer.|||Because a float in SQL Server is an *approximate* floating point
representation, essentially meaning if you round it to the appropriate
number of significant digits then you'll get the number you're after but
it's only stored as accurately as the binary numbering system can manage
(defined by IEEE 754). The same would happen if you used real rather
than float. I think what you're after is an *exact* floating point
representation, which corresponds to the numeric (or decimal) data types
in SQL Server (i.e. fixed precision & scale).
See Using decimal, float and real data
<http://msdn.microsoft.com/library/e...con_03_6mht.asp> in
SQL Books Online.
*mike hodgson*
http://sqlnerd.blogspot.com
Vivek wrote:
>Hi,
> iam using the bcp api to load data into sql server. The data to be loaded
>is single precision and hence my bcp_bind type is SQLFLT4. The column in my
>sql server table is a FLOAT(which is of course double precision).
>If i try to insert say a value 73.22 it gets inserted as 73.22000122070313.
>I mean the documentation says that implicit conversion for these types are
>allowed. So iam not sure why this happens.
>Appreciate any inputs.
>Vivek
>|||I think my query was not stated clearly. If i load the same value into a
REAL column it shows exactly what i inserted. (73.22)
Similarily if i store that value in a double precision program variable and
load into a FLOAT column it shows exactly what i stored.
The problem is when the value is in a single precision program variable and
i load into a FLOAT
"Mike Hodgson" wrote:
> Because a float in SQL Server is an *approximate* floating point
> representation, essentially meaning if you round it to the appropriate
> number of significant digits then you'll get the number you're after but
> it's only stored as accurately as the binary numbering system can manage
> (defined by IEEE 754). The same would happen if you used real rather
> than float. I think what you're after is an *exact* floating point
> representation, which corresponds to the numeric (or decimal) data types
> in SQL Server (i.e. fixed precision & scale).
> See Using decimal, float and real data
> <http://msdn.microsoft.com/library/e...con_03_6mht.asp> in
> SQL Books Online.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Vivek wrote:
>
>|||> I think my query was not stated clearly. If i load the same value into a
> REAL column it shows exactly what i inserted. (73.22)
We understood the question. You do not understand the issue. Did you read
BOL regarding their definition and usage (Accessing and Changing Relational
Data / Using decimal, float, and real Data)? If so, then contine with
http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse
representation of a value with the actual value. What you claim to see can
also be an artifact of whatever technique you use to "see" the value after
storage in the database. Below is a script that demonstrates the problem
more clearly.
set nocount on
declare @.test1 real, @.test2 float, @.test3 float(2)
set @.test1 = 73.22
set @.test2 = 73.22
set @.test3 = 73.22
select @.test1, @.test2, @.test3
select cast(@.test1 as varbinary(8)), cast(@.test2 as varbinary(8)),
cast(@.test3 as varbinary(8))
print @.test1
print @.test2
print @.test3|||That's what i thought initially. (I use Query Analyzer btw) After inserting
the same value (from a double precision and single precision variable
respectively) into a FLOAT column, if i do a 'select * from tfloat' i get
this output:
73.22
73.22000122070313
Would you say that the actual values of both rows are the same regardless of
what i see above?
The binary values are:
0x40524E147AE147AE
0x40524E1480000000
"Scott Morris" wrote:
> We understood the question. You do not understand the issue. Did you re
ad
> BOL regarding their definition and usage (Accessing and Changing Relationa
l
> Data / Using decimal, float, and real Data)? If so, then contine with
> http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse
> representation of a value with the actual value. What you claim to see ca
n
> also be an artifact of whatever technique you use to "see" the value after
> storage in the database. Below is a script that demonstrates the problem
> more clearly.
> set nocount on
> declare @.test1 real, @.test2 float, @.test3 float(2)
> set @.test1 = 73.22
> set @.test2 = 73.22
> set @.test3 = 73.22
> select @.test1, @.test2, @.test3
> select cast(@.test1 as varbinary(8)), cast(@.test2 as varbinary(8)),
> cast(@.test3 as varbinary(8))
> print @.test1
> print @.test2
> print @.test3
>
>|||On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote:
>That's what i thought initially. (I use Query Analyzer btw) After inserting
>the same value (from a double precision and single precision variable
>respectively) into a FLOAT column, if i do a 'select * from tfloat' i get
>this output:
>73.22
>73.22000122070313
>Would you say that the actual values of both rows are the same regardless o
f
>what i see above?
>The binary values are:
>0x40524E147AE147AE
>0x40524E1480000000
Hi Vivek,
These binary values explainexactly what's going on.
The closest representation in a double precision representation is,
obviosuly, 0x40524E147AE147AE. When you store that in a single precision
variable or column, it has to be rounded to the closest that can be
represented in the 24 bits set aside for single precision, which is
apparently 0x40524E148. If you then store this in a double precision
column, the extra bits are added again - but of course as 0 bits, since
SQL Server has no memory of the bits that were prreviously lost. And so
it ends up as 0x40524E1480000000.
Hugo Kornelis, SQL Server MVP|||Thanks Hugo. That sounds reasonable. So do i just avoid these kind of
insertions and stick to single to single and double to double precision
insertions?
"Hugo Kornelis" wrote:
> On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote:
>
> Hi Vivek,
> These binary values explainexactly what's going on.
> The closest representation in a double precision representation is,
> obviosuly, 0x40524E147AE147AE. When you store that in a single precision
> variable or column, it has to be rounded to the closest that can be
> represented in the 24 bits set aside for single precision, which is
> apparently 0x40524E148. If you then store this in a double precision
> column, the extra bits are added again - but of course as 0 bits, since
> SQL Server has no memory of the bits that were prreviously lost. And so
> it ends up as 0x40524E1480000000.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote:
>Thanks Hugo. That sounds reasonable. So do i just avoid these kind of
>insertions and stick to single to single and double to double precision
>insertions?
Hi Vivek,
I don't know what the requirements of your applications are. But as a
rule of thumb, I'd recommend to avoid conversions as much as possible,
stick to the same precision. Once you've lost precision, there's no way
to get it back. But OTOH, storing data at more than required precision
is just a waste of space.
Find the precision you need, then design your DB and application around
that.
Hugo Kornelis, SQL Server MVP|||Thank you guys.
"Hugo Kornelis" wrote:
> On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote:
>
> Hi Vivek,
> I don't know what the requirements of your applications are. But as a
> rule of thumb, I'd recommend to avoid conversions as much as possible,
> stick to the same precision. Once you've lost precision, there's no way
> to get it back. But OTOH, storing data at more than required precision
> is just a waste of space.
> Find the precision you need, then design your DB and application around
> that.
> --
> Hugo Kornelis, SQL Server MVP
>
iam using the bcp api to load data into sql server. The data to be loaded
is single precision and hence my bcp_bind type is SQLFLT4. The column in my
sql server table is a FLOAT(which is of course double precision).
If i try to insert say a value 73.22 it gets inserted as 73.22000122070313.
I mean the documentation says that implicit conversion for these types are
allowed. So iam not sure why this happens.
Appreciate any inputs.
VivekHi Vivek,
Thats the way float works:
"Approximate-number data types for use with floating point numeric
data. Floating point data is approximate; therefore, not all values in
the data type range can be represented exactly. "
DECLARE @.SOMEValue Float(2)
SEt @.SomeValue = 1.100001
SELECT @.SOMEValue
For more precicion you have to use another database like decimal.
HTH, Jens Suessmeyer.|||Because a float in SQL Server is an *approximate* floating point
representation, essentially meaning if you round it to the appropriate
number of significant digits then you'll get the number you're after but
it's only stored as accurately as the binary numbering system can manage
(defined by IEEE 754). The same would happen if you used real rather
than float. I think what you're after is an *exact* floating point
representation, which corresponds to the numeric (or decimal) data types
in SQL Server (i.e. fixed precision & scale).
See Using decimal, float and real data
<http://msdn.microsoft.com/library/e...con_03_6mht.asp> in
SQL Books Online.
*mike hodgson*
http://sqlnerd.blogspot.com
Vivek wrote:
>Hi,
> iam using the bcp api to load data into sql server. The data to be loaded
>is single precision and hence my bcp_bind type is SQLFLT4. The column in my
>sql server table is a FLOAT(which is of course double precision).
>If i try to insert say a value 73.22 it gets inserted as 73.22000122070313.
>I mean the documentation says that implicit conversion for these types are
>allowed. So iam not sure why this happens.
>Appreciate any inputs.
>Vivek
>|||I think my query was not stated clearly. If i load the same value into a
REAL column it shows exactly what i inserted. (73.22)
Similarily if i store that value in a double precision program variable and
load into a FLOAT column it shows exactly what i stored.
The problem is when the value is in a single precision program variable and
i load into a FLOAT
"Mike Hodgson" wrote:
> Because a float in SQL Server is an *approximate* floating point
> representation, essentially meaning if you round it to the appropriate
> number of significant digits then you'll get the number you're after but
> it's only stored as accurately as the binary numbering system can manage
> (defined by IEEE 754). The same would happen if you used real rather
> than float. I think what you're after is an *exact* floating point
> representation, which corresponds to the numeric (or decimal) data types
> in SQL Server (i.e. fixed precision & scale).
> See Using decimal, float and real data
> <http://msdn.microsoft.com/library/e...con_03_6mht.asp> in
> SQL Books Online.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Vivek wrote:
>
>|||> I think my query was not stated clearly. If i load the same value into a
> REAL column it shows exactly what i inserted. (73.22)
We understood the question. You do not understand the issue. Did you read
BOL regarding their definition and usage (Accessing and Changing Relational
Data / Using decimal, float, and real Data)? If so, then contine with
http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse
representation of a value with the actual value. What you claim to see can
also be an artifact of whatever technique you use to "see" the value after
storage in the database. Below is a script that demonstrates the problem
more clearly.
set nocount on
declare @.test1 real, @.test2 float, @.test3 float(2)
set @.test1 = 73.22
set @.test2 = 73.22
set @.test3 = 73.22
select @.test1, @.test2, @.test3
select cast(@.test1 as varbinary(8)), cast(@.test2 as varbinary(8)),
cast(@.test3 as varbinary(8))
print @.test1
print @.test2
print @.test3|||That's what i thought initially. (I use Query Analyzer btw) After inserting
the same value (from a double precision and single precision variable
respectively) into a FLOAT column, if i do a 'select * from tfloat' i get
this output:
73.22
73.22000122070313
Would you say that the actual values of both rows are the same regardless of
what i see above?
The binary values are:
0x40524E147AE147AE
0x40524E1480000000
"Scott Morris" wrote:
> We understood the question. You do not understand the issue. Did you re
ad
> BOL regarding their definition and usage (Accessing and Changing Relationa
l
> Data / Using decimal, float, and real Data)? If so, then contine with
> http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse
> representation of a value with the actual value. What you claim to see ca
n
> also be an artifact of whatever technique you use to "see" the value after
> storage in the database. Below is a script that demonstrates the problem
> more clearly.
> set nocount on
> declare @.test1 real, @.test2 float, @.test3 float(2)
> set @.test1 = 73.22
> set @.test2 = 73.22
> set @.test3 = 73.22
> select @.test1, @.test2, @.test3
> select cast(@.test1 as varbinary(8)), cast(@.test2 as varbinary(8)),
> cast(@.test3 as varbinary(8))
> print @.test1
> print @.test2
> print @.test3
>
>|||On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote:
>That's what i thought initially. (I use Query Analyzer btw) After inserting
>the same value (from a double precision and single precision variable
>respectively) into a FLOAT column, if i do a 'select * from tfloat' i get
>this output:
>73.22
>73.22000122070313
>Would you say that the actual values of both rows are the same regardless o
f
>what i see above?
>The binary values are:
>0x40524E147AE147AE
>0x40524E1480000000
Hi Vivek,
These binary values explainexactly what's going on.
The closest representation in a double precision representation is,
obviosuly, 0x40524E147AE147AE. When you store that in a single precision
variable or column, it has to be rounded to the closest that can be
represented in the 24 bits set aside for single precision, which is
apparently 0x40524E148. If you then store this in a double precision
column, the extra bits are added again - but of course as 0 bits, since
SQL Server has no memory of the bits that were prreviously lost. And so
it ends up as 0x40524E1480000000.
Hugo Kornelis, SQL Server MVP|||Thanks Hugo. That sounds reasonable. So do i just avoid these kind of
insertions and stick to single to single and double to double precision
insertions?
"Hugo Kornelis" wrote:
> On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote:
>
> Hi Vivek,
> These binary values explainexactly what's going on.
> The closest representation in a double precision representation is,
> obviosuly, 0x40524E147AE147AE. When you store that in a single precision
> variable or column, it has to be rounded to the closest that can be
> represented in the 24 bits set aside for single precision, which is
> apparently 0x40524E148. If you then store this in a double precision
> column, the extra bits are added again - but of course as 0 bits, since
> SQL Server has no memory of the bits that were prreviously lost. And so
> it ends up as 0x40524E1480000000.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote:
>Thanks Hugo. That sounds reasonable. So do i just avoid these kind of
>insertions and stick to single to single and double to double precision
>insertions?
Hi Vivek,
I don't know what the requirements of your applications are. But as a
rule of thumb, I'd recommend to avoid conversions as much as possible,
stick to the same precision. Once you've lost precision, there's no way
to get it back. But OTOH, storing data at more than required precision
is just a waste of space.
Find the precision you need, then design your DB and application around
that.
Hugo Kornelis, SQL Server MVP|||Thank you guys.
"Hugo Kornelis" wrote:
> On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote:
>
> Hi Vivek,
> I don't know what the requirements of your applications are. But as a
> rule of thumb, I'd recommend to avoid conversions as much as possible,
> stick to the same precision. Once you've lost precision, there's no way
> to get it back. But OTOH, storing data at more than required precision
> is just a waste of space.
> Find the precision you need, then design your DB and application around
> that.
> --
> Hugo Kornelis, SQL Server MVP
>
Inserting Serial No Column in result of a query
Hello Friends
My problem is
Suppose Query is - Select * from tbl_Employee
TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST
I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this.
Serial Name Post
1 XYZ QER
2 SDF OPO
3 WER IPO
If any body knows please post the solution its urgent.
Where is the serial number supposed to come from?Jason
|||http://www.aspfaq.com/show.asp?id=2427
|||
Serial Number is supposed to be generated through query only.
|||Using the #temp table option described in the article will achieve that for you.
CREATE TABLE
#temp
(
Rank INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
firstName VARCHAR(50),
lastName VARCHAR(50)
)
INSERT INTO
#temp
(
firstName,
lastName
)
SELECT
firstName,
lastName
FROM
people
ORDER BY
lastName,
firstName
SELECT
*
FROM
#temp
ORDER BY
Rank
DROP TABLE #temp
|||I take it no one bothered reading the link I posted..?
![Confused [8-)]](http://pics.10026.com/?src=/emoticons/emotion-43.gif)
|||Yes I did. I just recommended the #temp table approach in it. Thething I didn't like about the way it was done in the article, however,is that it does not create the #temp table first. For performancereasons any #temp table should be explicitly created, not created by aSELECT INTO.
|||What performance reasons?
That may have been true in previous versions of SQL Server (some lockswere placed on tempdb during temp table creation), but it's not anissue in SQL Server 2000.
|||There are a few reasons to my understanding:
the SELECT INTO method will always require a stored procedure recompilation; the explicit CREATE TABLE will not necessarily
|||Regarding recompilation, I don't think SELECT INTO necessarily causes it. I see no difference in behavior between thefollowing three sprocs, monitoring the SQL Compilations/sec and SQLRe-Compilations/sec counters and tracing the SP:Recompile event (on anotherwise dead server):
create proc xxx
as
select 1 as somecol
into #blah
GO
create proc yyy
as
create table #blah(somecol int)
insert #blah values (1)
GO
create proc zzz
as
declare @.blah table(somecol int)
insert @.blah values (1)
GO
I ran each of these numerous times and saw no recompiles with any ofthem. I'm not aware of whether or not SELECT INTO is more likelyto recompile in certain cases, but merely using the syntax does notcause re-compilation.
|||Yes, I meant log shipping. And since my use of SELECT INTO oncecaused our DBA to have to work through the night I have stayed far awayfrom it. The recovery model on the database might very well havechanged since then.
Thanks for your insight. Hopefully the original poster now has what they need. :-)
Sunday, February 19, 2012
Inserting Problem
I have a table_variable with some data now i need to INSERT it to the
table in database this table has identity column.
How can i get the all identities ?
Message posted via http://www.webservertalk.comINSERT into some table (Col1,col2,col3)
SELECT * from #TempTable
That would be the solution if your tablevariable doesnt contain the
estimated IDs (whereas the column list Col1col2 doenst contain the identity
column)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ALK via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im
Newsbeitrag news:b21535e06dbb4ad697a79231b66b0487@.SQ
webservertalk.com...
>I have a table_variable with some data now i need to INSERT it to the
> table in database this table has identity column.
> How can i get the all identities ?
> --
> Message posted via http://www.webservertalk.com|||What do you want to do with the IDENTITY values when you've got them?
Assuming you just want to return a result, you can do something like
the following. Use an alternate key of the table:
INSERT INTO YourTable (key_col, x, y, z)
SELECT key_col, x, y, z
FROM @.table_var
SELECT T.id /* IDENTITY key */
FROM YourTable AS T
JOIN @.table_var AS V
ON T.key_col = V.key_col /* Alternate key */
You must always have an alternate key because IDENTITY should never be
the only key of a table.
David Portas
SQL Server MVP
--|||Thanks, exactly
Message posted via http://www.webservertalk.com
table in database this table has identity column.
How can i get the all identities ?
Message posted via http://www.webservertalk.comINSERT into some table (Col1,col2,col3)
SELECT * from #TempTable
That would be the solution if your tablevariable doesnt contain the
estimated IDs (whereas the column list Col1col2 doenst contain the identity
column)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ALK via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im
Newsbeitrag news:b21535e06dbb4ad697a79231b66b0487@.SQ
webservertalk.com...
>I have a table_variable with some data now i need to INSERT it to the
> table in database this table has identity column.
> How can i get the all identities ?
> --
> Message posted via http://www.webservertalk.com|||What do you want to do with the IDENTITY values when you've got them?
Assuming you just want to return a result, you can do something like
the following. Use an alternate key of the table:
INSERT INTO YourTable (key_col, x, y, z)
SELECT key_col, x, y, z
FROM @.table_var
SELECT T.id /* IDENTITY key */
FROM YourTable AS T
JOIN @.table_var AS V
ON T.key_col = V.key_col /* Alternate key */
You must always have an alternate key because IDENTITY should never be
the only key of a table.
David Portas
SQL Server MVP
--|||Thanks, exactly
Message posted via http://www.webservertalk.com
inserting ole-object
Hello
I want to insert an iostream object into a ms acces db. I use ole-obect as
the data type for the specific column. Inserting a new record works fine but
somehow the field where the ole-object (iostream) has to be interted stays
empty.
Is ole-obect a proper data type for an iostream or should I use something
else.
thanks for the response
stijn[posted and mailed]
Stijn Oude Brunink (soudebrunink@.chello.nl) writes:
> I want to insert an iostream object into a ms acces db. I use ole-obect
> as the data type for the specific column. Inserting a new record works
> fine but somehow the field where the ole-object (iostream) has to be
> interted stays empty.
> Is ole-obect a proper data type for an iostream or should I use something
> else.
You should probably ask this question in comp.databases.ms-access. This
newsgroup is for MS SQL Server, which is something else.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Inserting of Duplicate Records Error Message
I would like to know what options I have with regards to trapping a duplicate record
before it tries to post to a SQL database. I have set the column to unique in SQL. But
when I try to use ASP and post a duplicate record I get a system error. I would like to
just create a referential error to notify the user that they cannot post a duplicate record
please try again. Any help would be greatly appreciated.
before it tries to post to a SQL database. I have set the column to unique in SQL. But
when I try to use ASP and post a duplicate record I get a system error. I would like to
just create a referential error to notify the user that they cannot post a duplicate record
please try again. Any help would be greatly appreciated.
RTRefer toTry... Catch... Block in the code
Does this helps?
Inserting Null Value
Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?
Ie. I have a column named Ordered Date which is type DT_Date. Based on some condition within a script component task, I want to set the value to NULL, however, since the type is date it will not allow nulls.
Found the answer, it was fairly simple. Row.FieldName_ISNULL() property is a read/write property. This property can be used to set nulls.
|||I'm assuming you are also doing some other work in the Script, but I thought I would mention that you can also do this in the Derived Column transform with the NULL() function. In your case, NULL(DT_DATE).|||Let me just add that the <columnname>_IsNull property may be read/write, or may be read-only, depending on the corresponding Usage Type specified for the individual column.-Doug
|||
I have a simple package that takes data from excel sources, runs a Data Transformation to get the decimal conversion etc into the same format as SQL ... but some of the columns are nullable - so my Data Transformation fails when it hits a null value in the source... (converting from float in the source to decimal in the destination.. though becuase of the empty values it sees the source as a string). Seems like it should be simple to allow it to pass nulls but I can't seem to figure it out (am new to SSIS). None of the fields in the transformation appear to be editable so that I can add the NULL() as mentioned above - if I got into the Advanced editor I can manually type the data type but NULL(DT_DECIMAL) gives me error "DataTypeConverter cannot convert from System.String."
Can anyone point me in the right direction? :)
|||<<a Data Transformation to get the decimal conversion etc into the same format as SQL >>
I believe your problem is in the decimal conversion processing you mention. The expression - or code - should be written to handle the occurence of nulls without bombing...
|||That is just it... I am not "writing" anything - its not a script component transformation task but rather just a "Data Conversion" object. If you edit the Data Conversion object it has input and then output... you can change the output alias, datatype, scale, codepage etc but there isn't any options regarding nulls that I can find.
|||So the problem is that the source data looks like an empty string, and in that case you want to put NULL in the destination? You might try using a Derived Column instead of the Data Conversion. There, you can write an expression.
For example, if your string input column is named "InputCol", and you are converting to DT_DECIMAL with scale 10, your expression for the new column would look like:
LEN(InputCol) == 0 ? NULL(DT_DECIMAL, 10) : (DT_DECIMAL, 10)InputCol
Let me know if that helps.
Mark
Inserting Null Value
Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?
Ie. I have a column named Ordered Date which is type DT_Date. Based on some condition within a script component task, I want to set the value to NULL, however, since the type is date it will not allow nulls.
Found the answer, it was fairly simple. Row.FieldName_ISNULL() property is a read/write property. This property can be used to set nulls.
|||I'm assuming you are also doing some other work in the Script, but I thought I would mention that you can also do this in the Derived Column transform with the NULL() function. In your case, NULL(DT_DATE).|||Let me just add that the <columnname>_IsNull property may be read/write, or may be read-only, depending on the corresponding Usage Type specified for the individual column.-Doug
|||
I have a simple package that takes data from excel sources, runs a Data Transformation to get the decimal conversion etc into the same format as SQL ... but some of the columns are nullable - so my Data Transformation fails when it hits a null value in the source... (converting from float in the source to decimal in the destination.. though becuase of the empty values it sees the source as a string). Seems like it should be simple to allow it to pass nulls but I can't seem to figure it out (am new to SSIS). None of the fields in the transformation appear to be editable so that I can add the NULL() as mentioned above - if I got into the Advanced editor I can manually type the data type but NULL(DT_DECIMAL) gives me error "DataTypeConverter cannot convert from System.String."
Can anyone point me in the right direction? :)
|||<<a Data Transformation to get the decimal conversion etc into the same format as SQL >>
I believe your problem is in the decimal conversion processing you mention. The expression - or code - should be written to handle the occurence of nulls without bombing...
|||That is just it... I am not "writing" anything - its not a script component transformation task but rather just a "Data Conversion" object. If you edit the Data Conversion object it has input and then output... you can change the output alias, datatype, scale, codepage etc but there isn't any options regarding nulls that I can find.
|||So the problem is that the source data looks like an empty string, and in that case you want to put NULL in the destination? You might try using a Derived Column instead of the Data Conversion. There, you can write an expression.
For example, if your string input column is named "InputCol", and you are converting to DT_DECIMAL with scale 10, your expression for the new column would look like:
LEN(InputCol) == 0 ? NULL(DT_DECIMAL, 10) : (DT_DECIMAL, 10)InputCol
Let me know if that helps.
Mark
Subscribe to:
Comments (Atom)