Copy one coulm to another

Hi Everyone.
I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is unique.
I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I do
this?
Thanks for your help.If you want to update all values in the LOGIN column, then it should be as
simple as this:
UPDATE MyTable
SET LOGIN = LICENSE_NO
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||If I understand what you are trying to do...
update t1
set LOGIN=LICENSE_NO
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.|||"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.
UPDATE FOO set LOGIN=LICENSE_NO
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks very much. Works great.
"Plamen Ratchev" wrote:
> If you want to update all values in the LOGIN column, then it should be as
> simple as this:
> UPDATE MyTable
> SET LOGIN = LICENSE_NO
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

Copy one coulm to another

Hi Everyone.
I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is unique.
I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I do
this?
Thanks for your help.
If you want to update all values in the LOGIN column, then it should be as
simple as this:
UPDATE MyTable
SET LOGIN = LICENSE_NO
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||If I understand what you are trying to do...
update t1
set LOGIN=LICENSE_NO
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.
|||"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:13B60DE7-BE32-426B-9EFB-1FA8B8010871@.microsoft.com...
> Hi Everyone.
> I have one table that has CUST_NO, LICENSE_NO and LOGIN. CUST_NO is
> unique.
> I need to copy whatever is in LICENSE_NO field to LOGIN field. How can I
> do
> this?
> Thanks for your help.
UPDATE FOO set LOGIN=LICENSE_NO
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks very much. Works great.
"Plamen Ratchev" wrote:

> If you want to update all values in the LOGIN column, then it should be as
> simple as this:
> UPDATE MyTable
> SET LOGIN = LICENSE_NO
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
sqlsql

copy on condition

i have two tables A and B with the same fields,

If the id field of table B equals id field in Table A i need to update th edata for that id row.
If the id field doesn;t match then i need to insert a new record in tale A for that id

that is i need to perform insertion or updation into table A depending on table B data
Can anyone give me some idea how to start?

You need two statements.
UPDATE A SET col1 = b.col1, col2 = b.col2, ... FROM A INNER JOIN B on A.pkcol = B.pkcol
INERT INTO A (pkcol, col1, col2, col3)
SELECT pkcol, col1, col2, col3
FROM B
WHERE pkcol NOT IN (SELECT pkcol FROM A)|||

Thank you very much Alex!
One more question, I have millions of data in the table, will the 2 queries work well for such huge data
.I mean performance wise. I have created indexes .

|||You'll just have to try it to find out. No way of guessing that without knowing what the tables are, how the indexes are, hardware, load, etc ...

copy fields of one table to the field structure of another table

How can I copy fields (not records) from one table to the field structure of
another table (in the same database on a sql server 2000)? So that I have
not to re-type the name and properties of the fields and to be sure the name
and properties of the fields are the same in both tables.
Thanks,
HugoScript Table definition and create new table with different name.
In Query Analizer right click on table -> Script Object to New Window As ->
Create. Type new name for table and run script.
"Educo Gent" wrote:

> How can I copy fields (not records) from one table to the field structure
of
> another table (in the same database on a sql server 2000)? So that I have
> not to re-type the name and properties of the fields and to be sure the na
me
> and properties of the fields are the same in both tables.
> Thanks,
> Hugo
>
>|||SELECT * FROM SourceTable
Into DestinationTable WHERE 1=0
Remeber that it will not copy the keys and indexes
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Educo Gent" <educo.gent@.skynet.be> wrote in message
news:ndHOd.8101$Ll6.561275@.phobos.telenet-ops.be...
> How can I copy fields (not records) from one table to the field structure
> of another table (in the same database on a sql server 2000)? So that I
> have not to re-type the name and properties of the fields and to be sure
> the name and properties of the fields are the same in both tables.
> Thanks,
> Hugo
>|||Hi Roji
The syntax is wrong
> SELECT * FROM SourceTable
> Into DestinationTable WHERE 1=0
SELECT * INTO Dest FROM Source WHERE 1=0
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:O0bvQS2DFHA.1292@.TK2MSFTNGP10.phx.gbl...
> SELECT * FROM SourceTable
> Into DestinationTable WHERE 1=0
> Remeber that it will not copy the keys and indexes
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Educo Gent" <educo.gent@.skynet.be> wrote in message
> news:ndHOd.8101$Ll6.561275@.phobos.telenet-ops.be...
structure
>|||You are right Uri. Thanks
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23QWD0W2DFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hi Roji
> The syntax is wrong
>
> SELECT * INTO Dest FROM Source WHERE 1=0
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:O0bvQS2DFHA.1292@.TK2MSFTNGP10.phx.gbl...
> structure
>

copy field from one table to another

I am trying to copy the values of one table to another one:
Select ProdNum, Name,NYUSAPrice,MPN
into ProductsQuartz [ProdNum,Name,Price,MPN]
from productswithprice
Both table already exist, trying to map the fields but I get an error '
AAlso tried:
Insert into
ProductsQuartz ('ProdNum','Name','Price','MPN')
values ('productswithprice.ProdNum',
'productswithprice.Name','productswithprice.NYUSAPrice','productswithprice.M
PN')
But I get an error, this are fields from another table in the database, how
can I accoplish this ?
A
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:e4Lgt10BFHA.1392@.tk2msftngp13.phx.gbl...
>I am trying to copy the values of one table to another one:
> Select ProdNum, Name,NYUSAPrice,MPN
> into ProductsQuartz [ProdNum,Name,Price,MPN]
> from productswithprice
> Both table already exist, trying to map the fields but I get an error '
> A
>|||Hi
Insert into
ProductsQuartz ('ProdNum','Name','Price','MPN')
Select productswithprice.ProdNum,
productswithprice.Name,productswithprice.NYUSAPrice,productswithprice.MPN
from Productswithprice
Hth
"Aleks" <arkark2004@.hotmail.com> wrote in message
news:O8mV950BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Also tried:
> Insert into
> ProductsQuartz ('ProdNum','Name','Price','MPN')
> values ('productswithprice.ProdNum',
>
'productswithprice.Name','productswithprice.NYUSAPrice','productswithprice.M
PN')
> But I get an error, this are fields from another table in the database,
how
> can I accoplish this ?
> A
>
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:e4Lgt10BFHA.1392@.tk2msftngp13.phx.gbl...
>|||thx
"AM" <shahdharti@.gmail.com> wrote in message
news:uwvPX$0BFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi
> Insert into
> ProductsQuartz ('ProdNum','Name','Price','MPN')
> Select productswithprice.ProdNum,
> productswithprice.Name,productswithprice.NYUSAPrice,productswithprice.MPN
> from Productswithprice
> Hth
> "Aleks" <arkark2004@.hotmail.com> wrote in message
> news:O8mV950BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> 'productswithprice.Name','productswithprice.NYUSAPrice','productswithprice
.M
> PN')
> how
>

Copy field data from table to another

Let's say, I have Table1 and Table2, both with the exactly same
structure and data in it, except that Table1.Field1 is empty in data
but Table2.Field1 have data in it.

How could I copy all the data from Table2.Field1 to Table1.Field1?
Is there any simpler way except looping through all the records to do
it?Sorry, just some add on to my previous question.
If I have this:

Table1
RecId ==> record ID
Name ==> name (empty)
Desc ==> description
... (another 23 fields to go)

Table2
RecId ==> recordID
Name ==> name (with data)
Desc ==> description
... (another 29 fields to go)

The Table1.Name is empty in data, then Table2.Name have data.
How could I write a SQL statement to copy all the data from Table2.Name
to Table1.Name since the table structure is different. I need to copy
the data in by mathing the RecordId.

I mean, copy Table2.Name to Table1.Name where their recordID is the
same.
Just as an extra info, there're 500,000 records in the tables.

Thanks a lot in advance.

Peter CCH|||Hi

Assuming recordid is a key that you match the records on. If your fields
have no value I assume they are NULL.

UPDATE T
SET Name = S.NAME,
Desc = S.Desc,
...

FROM Table 1 T
JOIN Table2 S ON s.recordid = t.recordid
WHERE Name IS NULL
OR DESC IS NULL

If you dont want to update table1 if it has a value try something like.

UPDATE T
SET Name = CASE WHEN NAME IS NULL THEN S.NAME ELSE NAME END,
Desc = CASE WHEN NAME IS NULL THEN S.DESC ELSE DESC END,,
...

FROM Table 1 T
JOIN Table2 S ON s.recordid = t.recordid
WHERE Name IS NULL
OR DESC IS NULL

John

"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1112424964.224162.251730@.g14g2000cwa.googlegr oups.com...
> Sorry, just some add on to my previous question.
> If I have this:
> Table1
> RecId ==> record ID
> Name ==> name (empty)
> Desc ==> description
> ... (another 23 fields to go)
> Table2
> RecId ==> recordID
> Name ==> name (with data)
> Desc ==> description
> ... (another 29 fields to go)
> The Table1.Name is empty in data, then Table2.Name have data.
> How could I write a SQL statement to copy all the data from Table2.Name
> to Table1.Name since the table structure is different. I need to copy
> the data in by mathing the RecordId.
> I mean, copy Table2.Name to Table1.Name where their recordID is the
> same.
> Just as an extra info, there're 500,000 records in the tables.
>
> Thanks a lot in advance.
>
>
> Peter CCH|||Since it join both of the tables first then only update the value, if
the table have 500,000 records, will the SQL statement above takes very
long time? (Assume it update that 500,000 records)|||Hi

5000000 rows would not take that long, but as the where condition limits the
rows you may see less being updated. You may also want to check that you are
not updating with the same values.

UPDATE T
SET Name = S.NAME,
[Desc] = S.[Desc],
...

FROM Table 1 T
JOIN Table2 S ON s.recordid = t.recordid
WHERE ( T.Name IS NULL AND T.Name <> S.Name )
OR ( T.[DESC] IS NULL AND T.[DESC] <> S.[DESC])

John

"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1112454364.911855.240540@.o13g2000cwo.googlegr oups.com...
> Since it join both of the tables first then only update the value, if
> the table have 500,000 records, will the SQL statement above takes very
> long time? (Assume it update that 500,000 records)|||Peter CCH (petercch.wodoy@.gmail.com) writes:
> Since it join both of the tables first then only update the value, if
> the table have 500,000 records, will the SQL statement above takes very
> long time? (Assume it update that 500,000 records)

Updating 500000 rows is usually not snap. Exactly how long time depends
on your hardware, but also the definition of the column. If the column
is fixed length, all updates can be in place. But if the column is a
varchar or varbinary column, many rows will grow out of their current
pages, so that must be a lot of page splits and data moved around. Again,
exactly how massive this effect will be depends on the data. If the
average length is three characters, you have have luck and most pages
have space to spare. If the average length is 100 bytes, this is less
likely.

Another thing that matters is whether data and log files have the space.
Updating half a million rows will take a toll on the log file. Exactly
how big that toll is, depends on the width of the table. If each row
is 10 bytes in averages you need a lot less log if the average row length
is 360 bytes, If the log does not have the space, it will have to
autogrow and autogrow does not come for free. If you are running with
simple recovery, you can hold down the log explosion by doing the
update in batches of 50000 or so.

But in short, the only way to get answer to a question like this one is
to benchmark.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsqlsql

Copy Data Issue

I need to create a new field and copy over an existing field from the same table. Since this table is about 3 million rows. It will take about 1 hour 30 minutes to complete.

Any good way around?

Thanks!

-J8

-------------
UPDATE Customer SET ColB = ColA WHERE ColA IS NOT NULLWhat (if any) point is there to this exercise?

-PatP|||Check this (http://www.dbforums.com/showthread.php?&p=3687532) thread out.|||The New column ColB will become a key column for sorting and also accept new kind of data while orig. ColA column continually receive data as it was. the historical data need to be copy over for sorting ...

copy a table

Hello all!

How to copy a table in MS SQL Server 2000 without chaning a structure? I
mean, I have one table, which has autoincrement numeric field (ID). When
I copy this table by exporting this table into the same database folder
I loose the specification of the field ID. Now it is not autoincrement
field but usual int field. Is it possible to copy this table without
changing data and structure of a table?

Kindest regards
Thank you
Marcin from Poland

*** Sent via Developersdex http://www.developersdex.com ***Marcin Zmyslowski wrote:
> Hello all!
> How to copy a table in MS SQL Server 2000 without chaning a
> structure? I mean, I have one table, which has autoincrement numeric
> field (ID). When I copy this table by exporting this table into the
> same database folder I loose the specification of the field ID. Now
> it is not autoincrement field but usual int field. Is it possible to
> copy this table without changing data and structure of a table?

With DTS copy object might do the trick. Otherwise, create DDL with QA or
EM, create the new table and then copy the data with "SET IDENTITY_INSERT
your_table ON". HTH

robert|||Insert into table1(columns)
select columns from othertable

Madhivanan

Copy a BLOB field in a stored-procedure from

Hello
I try to run the following query:
Update DestTable
set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID = 123)
Where DestTableID = 321
I always get the error message that this is not possible with image data.
Does anyone know a solution on how to copy a BLOB from one table to another?
I tried with WRITETEXT but this does not work either.
Many thanks
Thomas (thomas.steiner@.novaesprit.ch)Can you post your WRITETEXT code please.
Rick
"Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> Hello
> I try to run the following query:
> Update DestTable
> set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID =123)
> Where DestTableID = 321
> I always get the error message that this is not possible with image data.
> Does anyone know a solution on how to copy a BLOB from one table to
another?
> I tried with WRITETEXT but this does not work either.
> Many thanks
> Thomas (thomas.steiner@.novaesprit.ch)|||Here it is:
Declare @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(content)
FROM SambaReports_Test.dbo.Documents
Where DocumentID = 171
WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
RequestDataBody from RequestData where RequestID = 2328)
This results in a 'missing datastream' error. When I put a text-string
instead of the select-statement it works.
Thomas
"Rick Sawtell" wrote:
> Can you post your WRITETEXT code please.
> Rick
>
> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> > Hello
> > I try to run the following query:
> >
> > Update DestTable
> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID => 123)
> > Where DestTableID = 321
> >
> > I always get the error message that this is not possible with image data.
> > Does anyone know a solution on how to copy a BLOB from one table to
> another?
> > I tried with WRITETEXT but this does not work either.
> >
> > Many thanks
> >
> > Thomas (thomas.steiner@.novaesprit.ch)
>
>|||AFAIK, you can't pass a subquery to UPDATETEXT. The script below shows how
to copy text data between tables. If your data type is ntext, you'll need
to add calculations to allow 2 bytes per character.
DECLARE @.ptrval binary(16),
@.StartIndex int,
@.RequestDataBodyLength int,
@.RequestDataBody varchar(8000)
--clear any existing data and init text pointer
UPDATE Documents
SET content = NULL
WHERE DocumentID = 171
SET @.StartIndex = 0
--get length of data to copy
SELECT @.RequestDataBodyLength = DATALENGTH(RequestDataBody)
FROM RequestData
WHERE RequestID = 2328
--get text pointer
SELECT @.ptrval = TEXTPTR(content)
FROM Documents
WHERE DocumentID = 171
--copy data in chunks of 8000
WHILE @.StartIndex < @.RequestDataBodyLength
BEGIN
SELECT @.RequestDataBody = SUBSTRING(RequestDataBody, @.StartIndex, 8000)
FROM RequestData
WHERE RequestID = 2328
UPDATETEXT Documents.content @.ptrval @.StartIndex 0 @.RequestDataBody
SET @.StartIndex = @.StartIndex + DATALENGTH(@.RequestDataBody)
END
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Grosswesir" <Grosswesir@.discussions.microsoft.com> wrote in message
news:73535098-61B7-4BEC-88AC-B125C0F5155C@.microsoft.com...
> Here it is:
> Declare @.ptrval binary(16)
> SELECT @.ptrval = TEXTPTR(content)
> FROM SambaReports_Test.dbo.Documents
> Where DocumentID = 171
> WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
> RequestDataBody from RequestData where RequestID = 2328)
> This results in a 'missing datastream' error. When I put a text-string
> instead of the select-statement it works.
> Thomas
> "Rick Sawtell" wrote:
>> Can you post your WRITETEXT code please.
>> Rick
>>
>> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
>> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
>> > Hello
>> > I try to run the following query:
>> >
>> > Update DestTable
>> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID
>> > =>> 123)
>> > Where DestTableID = 321
>> >
>> > I always get the error message that this is not possible with image
>> > data.
>> > Does anyone know a solution on how to copy a BLOB from one table to
>> another?
>> > I tried with WRITETEXT but this does not work either.
>> >
>> > Many thanks
>> >
>> > Thomas (thomas.steiner@.novaesprit.ch)
>>|||Hi Dan
It works fine! Thanks a lot.
However I had to change the line:
SUBSTRING(RequestDataBody, @.StartIndex, 8000)
to
SUBSTRING(RequestDataBody, @.StartIndex+1, 8000)
Best regards
Thomas
"Dan Guzman" wrote:
> AFAIK, you can't pass a subquery to UPDATETEXT. The script below shows how
> to copy text data between tables. If your data type is ntext, you'll need
> to add calculations to allow 2 bytes per character.
>
> DECLARE @.ptrval binary(16),
> @.StartIndex int,
> @.RequestDataBodyLength int,
> @.RequestDataBody varchar(8000)
> --clear any existing data and init text pointer
> UPDATE Documents
> SET content = NULL
> WHERE DocumentID = 171
> SET @.StartIndex = 0
> --get length of data to copy
> SELECT @.RequestDataBodyLength = DATALENGTH(RequestDataBody)
> FROM RequestData
> WHERE RequestID = 2328
> --get text pointer
> SELECT @.ptrval = TEXTPTR(content)
> FROM Documents
> WHERE DocumentID = 171
> --copy data in chunks of 8000
> WHILE @.StartIndex < @.RequestDataBodyLength
> BEGIN
> SELECT @.RequestDataBody => SUBSTRING(RequestDataBody, @.StartIndex, 8000)
> FROM RequestData
> WHERE RequestID = 2328
> UPDATETEXT Documents.content @.ptrval @.StartIndex 0 @.RequestDataBody
> SET @.StartIndex = @.StartIndex + DATALENGTH(@.RequestDataBody)
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Grosswesir" <Grosswesir@.discussions.microsoft.com> wrote in message
> news:73535098-61B7-4BEC-88AC-B125C0F5155C@.microsoft.com...
> > Here it is:
> >
> > Declare @.ptrval binary(16)
> >
> > SELECT @.ptrval = TEXTPTR(content)
> > FROM SambaReports_Test.dbo.Documents
> > Where DocumentID = 171
> > WRITETEXT SambaReports_Test.dbo.Documents.content @.ptrval (Select
> > RequestDataBody from RequestData where RequestID = 2328)
> >
> > This results in a 'missing datastream' error. When I put a text-string
> > instead of the select-statement it works.
> >
> > Thomas
> >
> > "Rick Sawtell" wrote:
> >
> >> Can you post your WRITETEXT code please.
> >>
> >> Rick
> >>
> >>
> >> "Groswesir" <Groswesir@.discussions.microsoft.com> wrote in message
> >> news:A6217B40-987E-485B-A55A-3F97167B8A93@.microsoft.com...
> >> > Hello
> >> > I try to run the following query:
> >> >
> >> > Update DestTable
> >> > set DestBLOB = (select SourceBLOB from SourceTable where SourceTableID
> >> > => >> 123)
> >> > Where DestTableID = 321
> >> >
> >> > I always get the error message that this is not possible with image
> >> > data.
> >> > Does anyone know a solution on how to copy a BLOB from one table to
> >> another?
> >> > I tried with WRITETEXT but this does not work either.
> >> >
> >> > Many thanks
> >> >
> >> > Thomas (thomas.steiner@.novaesprit.ch)
> >>
> >>
> >>
>
>

Converting varchar to decimal

I'm have a varchar(50) field that I want to convert to decimal. These are
two samples:
+000000063451473.38
-000000038818201.42
Logically I want to:
-remove the + sign and leave the - sign
-remove any leading 0s
My desired outcome is:
63451473.38
-38818201.42
How can I reliably do this. Thanks to anyone who could help.Actually this should be down in the frontend after dataretrieval, because
string functions are not that really fast in SQL Server (2000).
DECLARE @.Number2Convert Varchar(50)
SET @.Number2Convert = '-000000063451473.38'
SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||something like this should do:
select str(your_col,18,2)
from tb
-oj
"Terri" <terri@.cybernets.com> wrote in message
news:d6389k$9ht$1@.reader2.nmix.net...
> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>|||Thanks Jens, I need to calculate with this data before it even will reach
the front end and it will be a once a day process with minimal records so
performance is not critical here.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eN3i9fAWFHA.2928@.TK2MSFTNGP10.phx.gbl...
> Actually this should be down in the frontend after dataretrieval, because
> string functions are not that really fast in SQL Server (2000).
> DECLARE @.Number2Convert Varchar(50)
> SET @.Number2Convert = '-000000063451473.38'
> SELECT (CASE LEFT(@.Number2Convert,1) WHEN '+' THEN '' ELSE '-' END) +
>
CONVERT(VARCHAR(50),CONVERT(DECIMAL(34,2
),RIGHT(@.Number2Convert,LEN(@.Number2
Convert)-1)))
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Terri" <terri@.cybernets.com> schrieb im Newsbeitrag
> news:d6389k$9ht$1@.reader2.nmix.net...
are
>|||Terri
Use Cast Function.. Example
Select Cast('+000000063451473.38' as Decimal(38,3))
Charly
"Terri" wrote:

> I'm have a varchar(50) field that I want to convert to decimal. These are
> two samples:
> +000000063451473.38
> -000000038818201.42
> Logically I want to:
> -remove the + sign and leave the - sign
> -remove any leading 0s
> My desired outcome is:
> 63451473.38
> -38818201.42
> How can I reliably do this. Thanks to anyone who could help.
>
>

Converting varchar to datetime

I have a sql server 2000 db that has a carchar field that is currently storying date data in the following format:

June 16

Can I convert that from varchar to datetime or smalldatetime without loss of data? And, if so, what adjustments do I have to make in my ado code to continue to allow my clients to add data. Currently they add date data by selecting a month from one dropdown and the day from another.

Thanks!Create another column and use CONVERT function to conver the date and store. Then delete the old column.

Converting varbinary to varchar

I have a password field which is of varbinary. Since its a varbinary Icannot see the password in the database I only see hexadecimal values.Now my question is that how can I convert those hexadecimal values tostring or varchar so I can read the password.
any ideas ??

Not sure if you seen this or not, but it talks about ways to convert binary fields.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Nick

converting to a uniqueidentifier

Hi all

In the database there is a table for customers and a table for the saleman,
the relationship between the 2 table is a uniqueidentifer field

customer table
name salesmanager
company 1 12545-854

salesmanager table
id salesmanager
12545-854 bob smith

I need to update the customer salesmanger field when someone leaves problem is that that field
is a uniqueidentifer and it will not allow me to update

Thanks in advance
rich

Can you post the exact error message you are getting when you try to update the column? A repro script that shows the problem will also be useful.|||

Hi

The error message is

Server: Msg 8169, Level 16, State 2, Line 1

Syntax error converting from a character string to uniqueidentifier

I need to be about to update the column when a new sales manager joins or change around.

Is this possible?

Converting Text to number

Hello there
I have table with field which is a text field that provide numbers and text
I would like to drop all the records that on the current field has text and
leave only the records with the number on the field
For this i've tried to use the convertion process but it gave me an error
when the data is text
Is there a way to know if the data is text or number so i can deal with it?
any help would be useful
roy@.atidsm.co.il
' 050-7709399"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in
news:#Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl:

> Hello there
> I have table with field which is a text field that provide numbers and
> text
> I would like to drop all the records that on the current field has text
> and leave only the records with the number on the field
> For this i've tried to use the convertion process but it gave me an
> error when the data is text
> Is there a way to know if the data is text or number so i can deal with
> it?
Try ISNUMERIC(expression)|||Although ISNUMERIC will provide the desired results in many cases, it will
also consider some obscure numeric values to be numeric. You can use LIKE
to test for integer digits. The example shows some differences between
ISNUMERIC and LIKE.
CREATE TABLE MyTable
(
MyNumber varchar(10)
)
GO
INSERT INTO MyTable VALUES('')
INSERT INTO MyTable VALUES(' ')
INSERT INTO MyTable VALUES('1')
INSERT INTO MyTable VALUES(' 2')
INSERT INTO MyTable VALUES('3 ')
INSERT INTO MyTable VALUES('1D')
INSERT INTO MyTable VALUES('0D1')
INSERT INTO MyTable VALUES('0E1')
SELECT *
FROM MyTable
WHERE ISNUMERIC(MyNumber) = 0
SELECT *
FROM MyTable
WHERE MyNumber LIKE '%[^0-9]%' OR
DATALENGTH(MyNumber) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl...
> Hello there
> I have table with field which is a text field that provide numbers and
> text
> I would like to drop all the records that on the current field has text
> and
> leave only the records with the number on the field
> For this i've tried to use the convertion process but it gave me an error
> when the data is text
> Is there a way to know if the data is text or number so i can deal with
> it?
> any help would be useful
> --
>
> roy@.atidsm.co.il
> ' 050-7709399
>|||ISNUMERIC also interprets '.', '-' and '+' as numeric.
Dan Guzman wrote:
> Although ISNUMERIC will provide the desired results in many cases, it
> will also consider some obscure numeric values to be numeric. You
> can use LIKE to test for integer digits. The example shows some
> differences between ISNUMERIC and LIKE.
> CREATE TABLE MyTable
> (
> MyNumber varchar(10)
> )
> GO
> INSERT INTO MyTable VALUES('')
> INSERT INTO MyTable VALUES(' ')
> INSERT INTO MyTable VALUES('1')
> INSERT INTO MyTable VALUES(' 2')
> INSERT INTO MyTable VALUES('3 ')
> INSERT INTO MyTable VALUES('1D')
> INSERT INTO MyTable VALUES('0D1')
> INSERT INTO MyTable VALUES('0E1')
> SELECT *
> FROM MyTable
> WHERE ISNUMERIC(MyNumber) = 0
> SELECT *
> FROM MyTable
> WHERE MyNumber LIKE '%[^0-9]%' OR
> DATALENGTH(MyNumber) = 0
>
> "Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
> news:%23Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl...

Converting text to date

I have a field that stores a date as text (121205). I need to convert this field to a date, but since it is text, I cannot figure out how to do it. Any ideas? Thanks! :Dwhich database? informix? sybase? db2? access? firebird? oracle? mysql? sql server? postgresql?|||SQL Server|||Moving this thread to Microsoft SQL Server forum, but I'd use:SELECT t, Convert(DATETIME, Stuff(Stuff(t, 5, 0, '/'), 3, 0, '/'))
FROM (SELECT '121205' AS t UNION SELECT '111105' UNION SELECT '101005') AS z
-PatP|||As you can tell, Pat likes to STUFF things...

CREATE PROC mySproc99 @.x text
AS
SELECT CONVERT(datetime, CONVERT(varchar(25),@.x))
GO

EXEC mySproc99 '121205'
GO

DROP PROC mySproc99
GO

So who's to say it's not 2012?|||Brett is correct, you don't need the slashes in the US, and maybe not in the UK either... They assume a string of digits are MMDDYY. As far as I know, the slashes work in any locale.

-PatP

Converting string to datetime

Hi,

I'm in a bit of a tricky situation. I'm upgrading and existing application (VB.NET 05 and sql server 2000). One of the tables has field having datatype varchar(20) but actually storing dates. From different parts of the application the datetime values are saved basically in three formats.

1. 2004/11/26 00:00:00

2. Nov 25 2004 12:00AM
3. 24/11/2004

The problem is I need to run a datediff to calculate a date difference. I can't get my head around to convert all the three types to one data type using a sql.

Really appritiate if some one can help me out on this.

Regards,

Vije

If your data is exactly the three formats you provided, then this approach should work for you:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
MyDate nvarchar(30),
MyNewDate datetime
)

INSERT INTO @.MyTable ( MyDate ) VALUES ( '2004/11/26 00:00:00' )
INSERT INTO @.MyTable ( MyDate ) VALUES ( 'Nov 25 2004 12:00AM' )
INSERT INTO @.MyTable ( MyDate ) VALUES ( '24/11/2004' )

UPDATE @.MyTable
SET MyNewDate = CASE isdate( MyDate )
WHEN 1 THEN cast( MyDate AS datetime )
ELSE convert( datetime, MyDate, 103 )
END
SELECT *
FROM @.MyTable

RowID MyDate MyNewDate
-- - -
1 2004/11/26 00:00:00 2004-11-26 00:00:00.000
2 Nov 25 2004 12:00AM 2004-11-25 00:00:00.000
3 24/11/2004 2004-11-24 00:00:00.000

|||

Vije:

You might be able to get by with something like this:

Code Snippet

select theDt,
case when charindex(':', theDt) <> 0
and isDate(theDt) = 1
then convert(datetime, theDt)
when charindex(':', theDt) = 0
and isDate(theDt) = 0
and isDate
( parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
) = 1
then parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
end as convertedDT
from ( select '2004/11/26 00:00:00' as theDT union all
select 'Nov 25 2004 12:00AM' union all
select '24/11/2004' union all
select 'invalid'
) a
/*
where isDate(theDt) = 1
or ( charindex(':', theDt) = 0
and isDate(theDt) = 0
and isDate
( parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
) = 1
)
*/

/*
theDt convertedDT
-
2004/11/26 00:00:00 2004-11-26 00:00:00.000
Nov 25 2004 12:00AM 2004-11-25 00:00:00.000
24/11/2004 2004-11-24 00:00:00.000
invalid NULL
*/

If you don't want the invalid data to appear just uncomment the WHERE clause (that is shown in red).

As an aside, you ought to consider converting this column to a DATETIME data type. These kinds of problems will only grow until you resolve the real problem here -- which is a design problem.

|||

In one single update statement you can't do it..

But the following batch may help you.

Code Snippet

Create Table #datedata (

[Dates] Varchar(20)

);

Insert Into #datedata Values('2004/11/26 00:00:00');

Insert Into #datedata Values('Nov 25 2004 12:00AM');

Insert Into #datedata Values('24/11/2004');

Set DateFormat DMY

Update

#datedata

Set

Dates = Convert(varchar,cast(Dates as datetime) , 120) -- Finaly converted as ANSI Format

Where

Isdate(Dates) = 1

Set DateFormat YMD

Update

#datedata

Set

Dates = Convert(varchar,cast(Dates as datetime) ,120) -- Finaly converted as ANSI Format

Where

Isdate(Dates) = 1

Select * From #dateData

|||

Woooh .. We all 3 given unique and different solution.... . I like Arnie's solution, Kent you are really hard worker..

Converting SQLMoney to c# float ?

I'm using c# 2.0.

I have a datareader that reads an invoice line item from a table in my SQL Server database. The UnitPrice field is a Money field in SQL server. At the same time I have an invoice class in my application that has a UnitPrice property which is a float.

How do I convert the SQLMoney to a float using my datareader?

Right now I have:

cm.CommandText =

"SELECT ItemID, Quantity, UnitPrice, Discount FROM tblInvoiceLineItems";using (SqlDataReader dr = cm.ExecuteReader())

{

while (dr.Read())

{

LineItem li =newLineItem();

li.UnitPrice = (

float)(double)dr.GetFloat(3); // cast error here.

lineItems.Add(li);

}

}

It will be easier to use DECIMAL in the database so your employer will not loose money and believe me that is a high probability with FLOAT. This makes it easy to use Visual Basic financial class to calculate your price in the application then use standard convert to convert your value to Decimal which is the same as money in SQL Server. Why because you can only set precision and scale with DECIMAL and NUMERIC in SQL Server. Just a thought the link below covers the FCL(framework class library) types, ADO.NET types and SQL Server types. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms131092.aspx

Converting SQL numeric datatype to a mainframe (cobol) packed field

Hopefully I have the right forum for this questions...

I am trying to convert a field defined in a SQL database as NUMERIC (15,2) to something I can export to a mainframe. The field on the mainframe is a packed field that is 6 (alphanumeric)chars in length. Mainframe defines as PIC X(6) and redefines as PIC 9(10)V99 PACKED(uses each byte of the 2-byte char). Must be zero filled to the left. Should look like the following for an amount of $35.25: 000000003525

Pulling my hair out...can anyone help before I go bald?A lot depends on how you are trying to export the data from the SQL database to the mainframe. If you use one of the ETL or data connector solutions, you should be able to simply "push" the data across with zero conversion effort.

If you are converting to a flat file to import into a mainframe system that isn't supported by ETL, you'll have to do the conversion on the SQL side because very few mainframe packages deal very well with "human formatted" data, which is about all current generations of SQL engines produce. The major problem here is that you are trying to get the SQL engine to format things in a way that they rarely use (I can't imagine a business user requesting data formatted as 9(10)V99).

Depending on which SQL engine you are using, the syntax will be slightly different. On the chance that you mean SQL Server, I'd use:SELECT Replace(Replace(Str(35.25, 13, 2), '.', ''), ' ', '0')If this doesn't help, post again with more details, and I bet one of the bright folks here will be able to help.

-PatP|||The replace did not work any better than the replicate function.

Here is some more info...

If I declare a variable as binary and then fill it in the following manner I get the data as I want it...

declare @.amt binary(6)
select @.amt = 0x000000003525

but, I need to convert my field "disbursement_amt" (defined as numeric (15,2) )off table "d" to binary...and I can't figure out how to do something similar to:

select @.amt = 0x<d.disbursement_amt)>

...any more help?|||Ok, my code works just fine using SQL Server 2000 and Microsoft Query Analyzer (I just tested it), what are you using (engine and client)?

-PatP|||Your code does work!...but that's not the problem...see how I used it below

declare @.field numeric(15,2)
select @.field = '35.25'
select @.field
declare @.outnum char(12)
SELECT @.outnum = Replace(Replace(Str(@.field, 13, 2), '.', ''), ' ', '0')
select @.outnum

...Great if I need the output in character format, but I need the data output (@.outnum) in binary format. CONVERT() does not do it...|||Oh, BTW, I'm using SQL Server 2000 and Embarcadero DBArtisan

Converting problem

Hello there
I'm imporing data from csv files.
one of the field with data lile '20060105' should be converted to date.
Vbscript isdate function cannot convert it to date, but sql server can
convert it.
Does someone knows whay?"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:O7A0OcAdGHA.3388@.TK2MSFTNGP05.phx.gbl...
> Hello there
> I'm imporing data from csv files.
> one of the field with data lile '20060105' should be converted to date.
> Vbscript isdate function cannot convert it to date, but sql server can
> convert it.
> Does someone knows whay?
>
Because SQL is better than VB Script? :-)
Regards
Colin Dawson
www.cjdawson.com|||when you do an import, the data in the column in the import file is matched
up with the column in the table. an implicit conversion occurs to get load
the data into the table. your text string '20060105' is converted/seen as
'YYYYMMDD'. Thus tis a valid value.
vb hostscript will intepret the value just as a string.
-oj
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:O7A0OcAdGHA.3388@.TK2MSFTNGP05.phx.gbl...
> Hello there
> I'm imporing data from csv files.
> one of the field with data lile '20060105' should be converted to date.
> Vbscript isdate function cannot convert it to date, but sql server can
> convert it.
> Does someone knows whay?
>

converting nvarchar to Minutes:Seconds in MS SQL?

I have a field in nvarchar type. It contains data like 0, :23, 1:57, ... all in minutes and seconds. Now, I need to convert it to MM:SS using query and get the Average of this column. How can I do it? I have tried Avg(Convert(nvarchar(20), [Calling Time], 108)) .. but I got error :The average aggregateoperation cannot take a nvarchar data type as an argument.

Help!!!! :(

You need to convert the minutes and seconds into some decimals which can be used in Avg function. Let's say you want to get Avg seconds, then you may try:

SELECT AvgSeconds= AVG(CASE WHEN CHARINDEX(':',[Calling Time])=0
THEN CONVERT(DECIMAL(4,2),[Calling Time])*60
WHEN CHARINDEX(':',[Calling Time])=1
THEN CONVERT(DECIMAL(4,2),RIGHT([Calling Time],LEN([Calling Time])-1))
ELSE CONVERT(DECIMAL(4,2),SUBSTRING([Calling Time],1,CHARINDEX(':',[Calling Time])-1))*60
+CONVERT(DECIMAL(4,2),SUBSTRING([Calling Time],CHARINDEX(':',[Calling Time])+1,LEN([Calling Time])))
END)
FROM test_CntTime