Update One Based on ANother Table

[Mike]

Here is my situation;

I have two tables in a MS-SQL DB. One table with dollar amounts and service
codes. I have a second table that I want to move some information into from
the first table. The catch is I want to move one field as is from the first
table to the second, but the rest of the fields in the second table are
calculations based on fields in the first table.

The first table is called XFILE. It has fields SVCCODE, PRICE, DWAGES,
DMATLS, etc. The second table has the same field names and I want to move
the SVCCODE from XFILE to Cost_Percent with no changes. For DWAGES in the
Cost_Percent table I want to do the following calculation;

[ XFILE.DWAGE] divided by [XFILE.PRICE] and put the results in Cost_Percent
table DWAGES field

So basically I am putting a percent in the Cost_Percent table. I can move
the data from one table to another ok, but I can not figure out how to write
the query in the Query Analyzer to do this.

I am ruining SQL2000 Standard on a Win2K3 server. I am using Query Analyzer
and SQL Enterprise Manager from an XP-Pro WS.

I have looked in the 'Books On-Line' for the answer but I sort of new to SQL
and can't find the answer that I am sure is staring me in the face.

Thanks in advance for any help.

Mike Charney
m charney at dunlap hospital dot org

[David Portas]

INSERT INTO Cost_Percent (svccode, price, dwages, dmatls)
SELECT svccode, price, dwages/price, dmatls
FROM xfile
WHERE ... ?

--
David Portas
SQL Server MVP
--

[Mike]

Thanks!! That did the trick!!

"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

INSERT INTO Cost_Percent (svccode, price, dwages, dmatls)
SELECT svccode, price, dwages/price, dmatls
FROM xfile
WHERE ... ?

--
David Portas
SQL Server MVP
--

[Mike]

Ok I have another problem with some of the data. I did not notice this but
some of the columns where nvarchar data types and the math is not working on
them. I tried to change the data type to decimal but Enterprise Manager is
giving me and error saying it can not convert the data type.

The table I am working was imported and the data types were nvarchar but I
managed to change them to varchar data types but I need them to be numeric
or decimal data types so that the math will work.

Can any one tell how to change these?

Thanks!

"Mike" <no***@forgetit.com> wrote in message
news:5M***********@newssvr17.news.prodigy.com...

Thanks!! That did the trick!!

"David Portas" <RE****************************@acm.org> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

INSERT INTO Cost_Percent (svccode, price, dwages, dmatls)
SELECT svccode, price, dwages/price, dmatls
FROM xfile
WHERE ... ?

--
David Portas
SQL Server MVP
--

[Hugo Kornelis]

On Thu, 13 Oct 2005 15:50:35 GMT, Mike wrote:

Ok I have another problem with some of the data. I did not notice this but
some of the columns where nvarchar data types and the math is not working on
them. I tried to change the data type to decimal but Enterprise Manager is
giving me and error saying it can not convert the data type.

The table I am working was imported and the data types were nvarchar but I
managed to change them to varchar data types but I need them to be numeric
or decimal data types so that the math will work.

Can any one tell how to change these?

Hi Mike,

If you are 100% sure that all values in the data can be converted to
numeric datatype, you can use ALTER TABLE:

ALTER TABLE MyTable
ALTER COLUMN MyColumn new_datatype

If you do have bad data, you'll have to find and correct that first.
This query might help you find the dirty data:

SELECT something
FROM MyTable
WHERE REPLACE(REPLACE(MyColumn,'.',''),'-','') LIKE '%[^0-9]%'
OR MyColumn LIKE '%.%.%'
OR MyColumn LIKE '_-%'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Mike (no***@forgetit.com) writes:

Ok I have another problem with some of the data. I did not notice this
but some of the columns where nvarchar data types and the math is not
working on them. I tried to change the data type to decimal but
Enterprise Manager is giving me and error saying it can not convert the
data type.

The table I am working was imported and the data types were nvarchar but I
managed to change them to varchar data types but I need them to be numeric
or decimal data types so that the math will work.

Can any one tell how to change these?

One would guess that there is some junk data in that column that is
not convertible. (And which would explain the error you got when you
tried bulk insert.)

Try this for a start:

SELECT * FROM tbl WHERE isnueric(col) = 0

Review that data, and see if you can repair it.

Unfortunately, isnumeric() is not reliable, since it returns 1 if the
data can be converted to any numeric data type, so once you fixed those
with isnumeric = 0, you need to do refined checking. This is a possibility:

SELECT SUM(convert(decimal(28,14), col) FROM tbl

If this passes, you are fine. If it does not, you still have junk. If you
have a manageable number of service codes, you can do:

SELECT SUM(convert(decimal(28,14), col) FROM tbl WHERE SVCCODE = 'code1'
go
SELECT SUM(convert(decimal(28,14), col) FROM tbl WHERE SVCCODE = 'code2'
go

It's important that you run this in spearate batches, because the convert
error aborts the batch.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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