converting columns in an INSERT (was "SQL Query")

I'm trying to create an Insert query and I'm having difficulty in 2 areas:

First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.

INSERT INTO ITEM_MASTER
SELECT *
FROM ITEM_MASTER_TEMP

Second, I've tried the following"conversions" in the SELECT statement, to no avail:

CONVERT(Money, MILL_COST) As MILL_COST
CONVERT(Money, CONVERT(Varchar(50), MILL_COST)
CAST(MILL_COST AS Money)

Any pointers much appreciated...First of all, I'd strongly suggest that you list out the columns. That solves all kinds of problems before they get a chance to happen to you. If you are determined to do things the hard way, you don't have to enumerate the columns yourself, but I'd still recommend it.

You ought to be able to use any of those conversions if you like, but as long as the contents of the column can be converted to MONEY, the SQL Server engine ought to handle the conversion for you.

-PatP|||"SELECT *" is shorthand for "I'm a lazy programmer". I would never leave it in any finished code. Bad. Bad. Bad bad code.

converting a float to a varchar _without_E syntax?

I have to output a column in a very specific format:
single quote integer number single quote
like this:
'100000'
But the data is in a float. When I do:
'''' + CAST(quantity as varchar) + ''''
I get:
'2e+007'
which is useless. I can't figure out the trick to telling SQL Server not to
use e notation. Is there a way?
MauryCAST(CAST(quantity as integer) as varchar)
Providing it casts to integer without overflowing.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6EAD6EF9-8ED7-44D3-897C-EFB3B995589C@.microsoft.com...
>I have to output a column in a very specific format:
> single quote integer number single quote
> like this:
> '100000'
> But the data is in a float. When I do:
> '''' + CAST(quantity as varchar) + ''''
> I get:
> '2e+007'
> which is useless. I can't figure out the trick to telling SQL Server not
> to
> use e notation. Is there a way?
> Maury|||"Russell Fields" wrote:
> CAST(CAST(quantity as integer) as varchar)
> Providing it casts to integer without overflowing.
Yikes!
Is it just me or would we all be a lot better off if MS put some time into
painfully obvious basic functionality like CONVERT instead of gee-wiz
features none of us actually use?
Maury|||Maury,
Maybe, but in cases like this the question is always: What do you expect as
your answer?
You wanted an integer this time, but at other times you might have wanted
the E notation. The code snippet made your intention explicit by saying 1)
make the float into an integer, then 2) make the integer into a string.
Interestingly, the CONVERT function offers 'styles', but I don't think any
absolutely matched your need to be an integer. You could try:
SELECT CONVERT(VARCHAR(15),floatquantity, 0)
But the definition of style 0 is "A maximum of 6 digits. Use in scientific
notation, when appropriate", so you might get 1234 or 123.4 or 1.234E9 all
depending on the value.
It seems that, in this case, you are really asking for some more flexible
styles for conversion. A good place for making such suggestions is at:
https://connect.microsoft.com/SQLServer. I found one suggestion like this
posted by Adam Machanic back in 2005 and "closed by design" by Microsoft.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126338
But you could raise it again.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:25B21A42-7E78-423C-A51A-0E79294BF6CF@.microsoft.com...
> "Russell Fields" wrote:
>> CAST(CAST(quantity as integer) as varchar)
>> Providing it casts to integer without overflowing.
> Yikes!
> Is it just me or would we all be a lot better off if MS put some time into
> painfully obvious basic functionality like CONVERT instead of gee-wiz
> features none of us actually use?
> Maury

Convert single record to table

Hi all!

I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like?

Thanks.

Try:

create table dbo.t3 (

pk_col int not null,

grp int not null,

Accnt int,

Assesed int,

Paid int,

constraint pk_t3 primary key (pk_col, grp) clustered

)

insert into dbo.t3(pk_col, grp, Accnt, Assesed, Paid)

select pk_col, 1 as grp, Accnt1as Accnt, Assesed1as Assesed, Paid1 as Paid

from dbo.t1

union all

select pk_col, 2 as grp, Accnt2, Assesed2, Paid2

from dbo.t1

union all

select pk_col, 3 as grp, Accnt3, Assesed3, Paid3

from dbo.t1

order by pk_col, grp

AMB

|||

Thanks! it worked great!

Convert single record to table

Hi all!

I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like?

Thanks.

Try:

create table dbo.t3 (

pk_col int not null,

grp int not null,

Accnt int,

Assesed int,

Paid int,

constraint pk_t3 primary key (pk_col, grp) clustered

)

insert into dbo.t3(pk_col, grp, Accnt, Assesed, Paid)

select pk_col, 1 as grp, Accnt1as Accnt, Assesed1as Assesed, Paid1 as Paid

from dbo.t1

union all

select pk_col, 2 as grp, Accnt2, Assesed2, Paid2

from dbo.t1

union all

select pk_col, 3 as grp, Accnt3, Assesed3, Paid3

from dbo.t1

order by pk_col, grp

AMB

|||

Thanks! it worked great!

Convert datetime

How can I easily convert two columns in a table to a single datetime column:
the columns look like this:
Date_In
19990220
20000114
19980524
Time_In
1304
0237
1503
I should also add that these columns are of varchar data type since the
source data is a text file.
Thanks!ALTER TABLE tablename ADD Date_Time_In SMALLDATETIME
GO
UPDATE tablename SET Date_Time_In = Date_In + ' ' +
LEFT(Time_In,2)+':'+RIGHT(Time_In,2)
WHERE IsDate(Date_In + ' ' + LEFT(Time_In,2)+':'+RIGHT(Time_In,2)) = 1
-- identify those with bad data:
SELECT Date_In,Time_In FROM tablename WHERE Date_Time_In IS NULL
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:49B879F1-42FA-41B0-8AA8-E22DFC4E2748@.microsoft.com...
> How can I easily convert two columns in a table to a single datetime
> column:
> the columns look like this:
> Date_In
> 19990220
> 20000114
> 19980524
>
> Time_In
> 1304
> 0237
> 1503
> I should also add that these columns are of varchar data type since the
> source data is a text file.
> Thanks!|||Hi
SELECT cast(convert(char(8),'19990220',112)+' '+ convert(char(5),
stuff('1304',3,2, ':'+right('1304',2)),108)as datetime)
"Patrice" <Patrice@.discussions.microsoft.com> wrote in message
news:49B879F1-42FA-41B0-8AA8-E22DFC4E2748@.microsoft.com...
> How can I easily convert two columns in a table to a single datetime
> column:
> the columns look like this:
> Date_In
> 19990220
> 20000114
> 19980524
>
> Time_In
> 1304
> 0237
> 1503
> I should also add that these columns are of varchar data type since the
> source data is a text file.
> Thanks!