is this possible? ...not an SQL master

Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
RickAn example:

INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable

Assumes that the character is always the last position and length of 1.

Originally posted by entangled
Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
Rick|||HI,

try to use decode(substr(backPM,length(backPM)-1,1),'R',substr(backPM,1,length(backPM)-1,backPM)

and use the same formula for AheadPM column

Originally posted by entangled
Hi,

I am trying to figure out the best way to reformat the record entries in a database.

In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

I have attached a copy of a screenshot with some notes.

Thanks in advance to anyone who sees the easy way to do this!

cheers,
Rick|||Thank you for the example. This one example pretty much addresses both issues. I will work with this and see how I can apply this approach.

many thanks,
Rick Sperling

Originally posted by dmmac
An example:

INSERT INTO newtable (county, route, px_back, backpm)
SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable

Assumes that the character is always the last position and length of 1.

is there documenation on format of transaction log entries

I searched the forums and SQL 2005 book online to see if Microsoft provides documentation on the format of transaction log entries. I could not find any...

Will Microsoft provides this documentation? It will also be nice if Microsoft will provide utilities to help us read content of transaction logs (online or backup file)? This will be quite helpful for DBAs.

Oracle provides a FREE utility called log miner... This tool is quite helpful. I know there are vendors that sell utilities to process transaction logs ( Log Explorer). I would like more flexibility.

If Microsoft does not provide documentation or utilities in SQL 2005, what would be the reasons? These things can help DBAs, thus will help Microsoft sell SQL 2005.

Thanks.

Microsoft does not provide the format of its files to everyone. Microsoft does work with third party vendors to provide additional functionality (as you mentioned).

Please work with the third party vendors to get more features and the flexibility that you want.

Thank you,
Mark Wistrom