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 this possible without using Cursors?

Hello!
I have a stored proc that checks the file existance in one location and
copy them to another location. The stored proc reads the record one by
one and builds the DOS COPY command. In the end, it exccutes the DOS
command using xp_cmdshell. See below the code.
The stored proc is working great but I had to use the CURSOR for reading
the records. I was wondering if I can avoid using it? Is this possible?
how? Thanks in advance for your help!
CREATE TABLE [dbo].[FILE_PATH] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Source] [varchar] (150) NULL ,
[Destination] [char] (150) NULL ,
[Environment] [char] (25) NULL ,
[Filename] [varchar] (50) NULL
) ON [PRIMARY]
GO
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverA\folder1','\\serverD\folder1
','Development',
'file1')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverA\folder1','\\serverD\folder1
','Development',
'file2')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
CREATE proc CopyFiles
as
set nocount on
declare @.source varchar(150)
declare @.destination varchar(150)
declare @.DOScmd varchar(300)
declare @.source_dir varchar(200)
declare @.filename varchar(30)
declare @.environment varchar(200)
declare @.environementTmp varchar(200)
declare @.errortext varchar(200)
select
@.environementTmp =
case @.@.servername
when 'server A' then 'Development'
when 'server B' then 'UAT'
when 'server C' then 'Production'
end
create table #files(filename sysname NULL)
declare filecursor cursor for
select source, destination, environment, [filename]
from file_path
open filecursor
fetch next from filecursor into @.source, @.destination, @.environment,
@.filename
while (@.@.fetch_status = 0)
begin
if @.environementTmp = @.environment
begin
set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
+ ' /b'
insert #files exec master..xp_cmdshell @.source_dir
if (select filename from #files where filename is not null) = @.filename
begin
set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
' +'"'+ rtrim(@.destination)+'"'
exec master.dbo.xp_cmdshell @.DOScmd
end
else
begin
set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
exist!!!'
exec master.dbo.xp_sendmail
@.recipients = 'abc123',
@.copy_recipients = 'abc123'
@.subject = @.errortext,
@.message = @.errortext
end
end
truncate table #files
fetch next from filecursor into @.source, @.destination, @.environment,
@.filename
end
close filecursor
deallocate filecursor
GO
*** Sent via Developersdex http://www.examnotes.net ***Test Test wrote:
> Hello!
> I have a stored proc that checks the file existance in one location
> and copy them to another location. The stored proc reads the record
> one by one and builds the DOS COPY command. In the end, it exccutes
> the DOS command using xp_cmdshell. See below the code.
> The stored proc is working great but I had to use the CURSOR for
> reading the records. I was wondering if I can avoid using it? Is this
> possible? how? Thanks in advance for your help!
> CREATE TABLE [dbo].[FILE_PATH] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Source] [varchar] (150) NULL ,
> [Destination] [char] (150) NULL ,
> [Environment] [char] (25) NULL ,
> [Filename] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
>
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file1')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production',
> 'file5') go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file2')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production',
> 'file5')
>
> CREATE proc CopyFiles
> as
>
> set nocount on
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> declare @.source_dir varchar(200)
> declare @.filename varchar(30)
> declare @.environment varchar(200)
> declare @.environementTmp varchar(200)
> declare @.errortext varchar(200)
>
> select
> @.environementTmp =
> case @.@.servername
> when 'server A' then 'Development'
> when 'server B' then 'UAT'
> when 'server C' then 'Production'
> end
> create table #files(filename sysname NULL)
> declare filecursor cursor for
> select source, destination, environment, [filename]
> from file_path
> open filecursor
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> while (@.@.fetch_status = 0)
> begin
> if @.environementTmp = @.environment
> begin
> set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
> + ' /b'
> insert #files exec master..xp_cmdshell @.source_dir
> if (select filename from #files where filename is not null) =
> @.filename begin
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
> ' +'"'+ rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> end
> else
> begin
> set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
> exist!!!'
> exec master.dbo.xp_sendmail
> @.recipients = 'abc123',
> @.copy_recipients = 'abc123'
> @.subject = @.errortext,
> @.message = @.errortext
> end
> end
> truncate table #files
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> end
> close filecursor
> deallocate filecursor
> GO
>
Something tells me more time is spent executing xp_cmdshell than the
time used for the cursor, so you're probably fine. Cursors are good for
this type of operation IMO since it makes the code relatively clear. You
should always use local, read-only forward-only cursors, for this type
of read-only processing so update your declare statement accordingly.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||We had a similar problem - we solved this by using COM objects.
We created our own custom set of COM objects, but WSH.FileSystemObject does
the same thing.
Essentially:-
Call master.dbo.sp_OACreate to create the com object (returns @.lpObject)
Then create a UDF to do the file exists check
Call master.dbo.sp_OAMethod for file exists passing filename returning file
exists
Then destroy the object using master.dbo.sp_OADestory
Then you'd do
UPDATE
tableWithFileNamesIn
SET
FileExists = dbo.fnFileExists( @.lpObject , FullPathToFile ) AS
FileExists
Assuming: tableWithFileNamesIn( FullPathToFile VARCHAR(260) , FileExists
BIT )
http://msdn.microsoft.com/library/d.../>
sotutor.asp
If the process isn't going to run as system admin, create a role in master
called COMCreator, and add all the sp_OA... (except for OAStop) to that
role and add the process user account to COMCreator.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uB58v9A0FHA.268@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have a stored proc that checks the file existance in one location and
> copy them to another location. The stored proc reads the record one by
> one and builds the DOS COPY command. In the end, it exccutes the DOS
> command using xp_cmdshell. See below the code.
> The stored proc is working great but I had to use the CURSOR for reading
> the records. I was wondering if I can avoid using it? Is this possible?
> how? Thanks in advance for your help!
> CREATE TABLE [dbo].[FILE_PATH] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Source] [varchar] (150) NULL ,
> [Destination] [char] (150) NULL ,
> [Environment] [char] (25) NULL ,
> [Filename] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
>
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file1')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file2')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
>
> CREATE proc CopyFiles
> as
>
> set nocount on
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> declare @.source_dir varchar(200)
> declare @.filename varchar(30)
> declare @.environment varchar(200)
> declare @.environementTmp varchar(200)
> declare @.errortext varchar(200)
>
> select
> @.environementTmp =
> case @.@.servername
> when 'server A' then 'Development'
> when 'server B' then 'UAT'
> when 'server C' then 'Production'
> end
> create table #files(filename sysname NULL)
> declare filecursor cursor for
> select source, destination, environment, [filename]
> from file_path
> open filecursor
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> while (@.@.fetch_status = 0)
> begin
> if @.environementTmp = @.environment
> begin
> set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
> + ' /b'
> insert #files exec master..xp_cmdshell @.source_dir
> if (select filename from #files where filename is not null) = @.filename
> begin
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
> ' +'"'+ rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> end
> else
> begin
> set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
> exist!!!'
> exec master.dbo.xp_sendmail
> @.recipients = 'abc123',
> @.copy_recipients = 'abc123'
> @.subject = @.errortext,
> @.message = @.errortext
> end
> end
> truncate table #files
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> end
> close filecursor
> deallocate filecursor
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

is this possible

hey all,
i have a transaction table i need to add a record to. the primary key is a 2
field column. RecordID and then the SequenceID.
How i understand it is:
To add a new record i have to
1. Find the last sequence number used
2. Then add the new record
can i do this in one stored procedure?
thanks,
rodcharWhy not use an IDENTITY column?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||Again provide DDL...
Create Table (RecordId Int, SequenceId Int
, Constraint Primary Key (RecordId, SequenceId))
Insert Table(RecordId, SequenceId)
Select RecordId, Max(SequenceId) + 1
From Table
Group By RecordId
BTW, this does have issues in a multi-user environment. If two people were t
o
execute this function at exactly the same time, they'll get the same answer
and
thus a collision. A better way would be to make a small table that stores th
e
last value used.
Thomas
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> hey all,
> i have a transaction table i need to add a record to. the primary key is a
2
> field column. RecordID and then the SequenceID.
> How i understand it is:
> To add a new record i have to
> 1. Find the last sequence number used
> 2. Then add the new record
> can i do this in one stored procedure?
> thanks,
> rodchar
>|||"Thomas" <thomas@.newsgroup.nospam> wrote in message
news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same
answer and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
..which would have the same issue -- what would stop two readers from
getting the value simultaneously?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||The problem is that this is an existing table in production.
"Adam Machanic" wrote:

> Why not use an IDENTITY column?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
> 2
>
>|||Just increase Isolation Leel to Repeatable Read (or Serializeable) to preven
t
this issue from arising...
"Thomas" wrote:

> Again provide DDL...
> Create Table (RecordId Int, SequenceId Int
> , Constraint Primary Key (RecordId, SequenceId))
> Insert Table(RecordId, SequenceId)
> Select RecordId, Max(SequenceId) + 1
> From Table
> Group By RecordId
> BTW, this does have issues in a multi-user environment. If two people were
to
> execute this function at exactly the same time, they'll get the same answe
r and
> thus a collision. A better way would be to make a small table that stores
the
> last value used.
>
> Thomas
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:88DBED0D-2707-4B78-AB8A-405F6E56F259@.microsoft.com...
>
>|||If you can withstand the entire table being locked during the insert process
,
this would also be a viable choice.
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to prev
ent
> this issue from arising...
> "Thomas" wrote:
>|||If you use a small table that stores the next value, you can lock the table
and
increment the "next" value. In essence, serializing the retrieval of the nex
t id
value. However, it does mean you may get gaps.
Thomas
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OI6wTkcQFHA.1884@.TK2MSFTNGP15.phx.gbl...
> "Thomas" <thomas@.newsgroup.nospam> wrote in message
> news:OuVOPfcQFHA.3496@.TK2MSFTNGP09.phx.gbl...
> to
> answer and
> the
> ...which would have the same issue -- what would stop two readers from
> getting the value simultaneously?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> Just increase Isolation Leel to Repeatable Read (or Serializeable) to
prevent
> this issue from arising...
How would that prevent issues?
QA Window 1:
--
use tempdb
go
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
QA Window 2:
--
use tempdb
go
set transaction isolation level serializable
go
begin tran
select id
from x
go
Serializable blocks only if writes have taken place.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
> "Thomas" wrote:
>
were to
answer and
stores the
is a 2|||Adam,
Yes with just a read, but if you use the relation from t he select as
Insert values, you are doing more than just a read, and qry window 2 will
block..
create table x(id int)
go
insert x values (1)
go
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Wait here while you run Qry WIndow 2 --
Commit Tran
-- ******************************
--Query Window 2
--
set transaction isolation level serializable
go
begin tran
Insert x (id)
select id + 1 from x
-- Now go back and commit Query Window 1
-- ---
"Adam Machanic" wrote:

> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:BBBD444B-B41C-4C6D-8872-7BFDA0364971@.microsoft.com...
> prevent
> How would that prevent issues?
> QA Window 1:
> --
> use tempdb
> go
> create table x(id int)
> go
> insert x values (1)
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> QA Window 2:
> --
> use tempdb
> go
> set transaction isolation level serializable
> go
> begin tran
> select id
> from x
> go
>
> Serializable blocks only if writes have taken place.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> were to
> answer and
> stores the
> is a 2
>
>

Is this guaranteed: SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3

SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
Will the statement below, always return the first record of the same
stand-alone SELECT statement as below:
SELECT * FROM ... ORDER BY Field1, Field2, Field3
Thanks,
JayYes, assuming you use the ORDER BY clause and the data remains constant.
Insert a new row, and it may be the new top result.
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>|||Didn't you see the contrary example that Razvan posted?
http://groups.google.com/group/micr...3752b9548322706
David Portas
SQL Server MVP
--|||In SQL Server 2005, we are a bit more consistent with TOP + ORDER BY
semantics than perhaps some previous releases.
Here are the basic rules:
1. ORDER BY determines the presentation order for the _output_ of a query.
2. Within the same select block, an ORDER BY implies that TOP returns the
TOP N rows (not necessarily in a specific order).
3. ORDER BY in subselects or views does *not* guarantee the output of a
containing query.
So, for TOP N... ORDER BY ... with no containing select block, both the set
and the order are guaranted.
Within a subquery, TOP N ... ORDER BY guarantees the set but not the output
order (you need a top-level ORDER BY to guarantee output order).
Conor Cunningham
SQL Server Query Optimization Team
"Jay" <jay6447@.hotmail.com> wrote in message
news:1131529297.922481.160800@.g49g2000cwa.googlegroups.com...
> SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3
> Will the statement below, always return the first record of the same
> stand-alone SELECT statement as below:
> SELECT * FROM ... ORDER BY Field1, Field2, Field3
>
> Thanks,
> Jay
>

Is there an XMLTYPE in SQL 2000?

Oracle 9 has an XMLTYPE data type.

I am interested in inserting a formatted block of XML in a SQL 2000 column to record a set of data in one column.

Is there a way to do this, or a datatype that comes close?

Thanks.As far as I know you have to use NTEXT. The new version of SQLServer due out later this year addresses the XML data type.