I come from a Foxpro/dBase background (almost 20 yrs of DBF files) and
I'm new to SQL. I've been experimenting with VB.Net and MSDE for a few
months now and I'm very impressed. I got the go ahead to convert a
major xBase application to SQL/VB.Net.
I am not sure how to do certain tasks in SQL. I need to know how to do
the following...
Move to the last record in a table (in xBase, it is "go bottom")
Move to the first record in a table (in xBase, it is "go top")
Append a new blank record to a table (in xBase, it is "append blank")
Move to the next record in a table
In the DBF world, some tasks require filtering a file (say all records
belonging to invoice <n>) and then processing each record in a loop.
This involved moving a record pointer with either a "goto" or "skip"
command. I suspect the SQL equivalent is to use the "SELECT" statement
to filter the records and then to use the "UPDATE" or "DELETE" commands
to edit the table. Do I have the right idea?
Thanks.
Richard
Hi Richard,
Don't take this personally, but from the questions you are asking, you have
a bit of a learning curve ahead of you. First you need to stop looking at
SQL data as a list of sequential records. SQL data is retrieved and
manipulated as sets of data. There really is no equivalent to "move to last
record" or first record. If you need to update or select a particular record
then you need to define your records with either a primary key or some other
unique contraint on the data and provide the SQL statement that will extract
a distinct record. I suggest that you find a good general book on SQL and
study up on relational database theory. As a recommendation "Data &
Databases: Concepts In Practice" by Joe Celko is a very good book that lays
down the basics of relational database design.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:u%23WzFEO$EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> I come from a Foxpro/dBase background (almost 20 yrs of DBF files) and I'm
> new to SQL. I've been experimenting with VB.Net and MSDE for a few months
> now and I'm very impressed. I got the go ahead to convert a major xBase
> application to SQL/VB.Net.
> I am not sure how to do certain tasks in SQL. I need to know how to do
> the following...
> Move to the last record in a table (in xBase, it is "go bottom")
> Move to the first record in a table (in xBase, it is "go top")
> Append a new blank record to a table (in xBase, it is "append blank")
> Move to the next record in a table
> In the DBF world, some tasks require filtering a file (say all records
> belonging to invoice <n>) and then processing each record in a loop. This
> involved moving a record pointer with either a "goto" or "skip" command.
> I suspect the SQL equivalent is to use the "SELECT" statement to filter
> the records and then to use the "UPDATE" or "DELETE" commands to edit the
> table. Do I have the right idea?
> Thanks.
> Richard
|||Hi Jim,
I'm not taking it personally, I appreciate you comments
I had a feeling that I'd have to use the "select" command to filter the
record(s) that I want to work with and then to think in terms of 'a set
of records'.
I have already defined primary keys for my imported DBF files. I know
how to 'update' information in existing records, but I was browsing the
'SQL Book Online' and couldn't find any references on how to combine
records from multiple tables. I know about the 'join' command, but my
request is a bit different. Say I had a existing table with 1000
records and I had the user input information into a similar table with
15 new records (exact same layout) and I wanted to merge the two tables
into one table with 1015 records, how would I do this?
Thanks for the recommendation, I'll check it out. Is it a general book
or one specific to MS SQL?
Richard
Jim Young wrote:
> Hi Richard,
> Don't take this personally, but from the questions you are asking, you have
> a bit of a learning curve ahead of you. First you need to stop looking at
> SQL data as a list of sequential records. SQL data is retrieved and
> manipulated as sets of data. There really is no equivalent to "move to last
> record" or first record. If you need to update or select a particular record
> then you need to define your records with either a primary key or some other
> unique contraint on the data and provide the SQL statement that will extract
> a distinct record. I suggest that you find a good general book on SQL and
> study up on relational database theory. As a recommendation "Data &
> Databases: Concepts In Practice" by Joe Celko is a very good book that lays
> down the basics of relational database design.
> Jim
|||I do not question why you have the extra table with exactly the same layout
(columns, I guess). It is very simple the do what you want. Assume, tableA
contains the 15 (or any number of) rows of user inputs and you want to add
all of them of some of them into tableB. You could use this SQL statement:
INERT INTO tableB (Col1,Col2,Col3...)
SELECT Filed1,Field2,Field3...
FROM tableA
WHERE ... /*here the WHERE clause allows you to choose what rows in tableA
being transfered to tableB.
You also can see from above SQL statement, tableA does not have to be the
same structure as tableB. You only need to make sure the fields selected in
SELECT...clause match the fields (field count and data type) those in INSERT
INTO clause.
Since you just moved to SQL Server/MSDE, I'd sit down for a couple of days
to stduy/investigete T-SQL, rather than browse SQL Book on-line for
particular processing.
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:e6Ad#LQ$EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> I'm not taking it personally, I appreciate you comments
> I had a feeling that I'd have to use the "select" command to filter the
> record(s) that I want to work with and then to think in terms of 'a set
> of records'.
> I have already defined primary keys for my imported DBF files. I know
> how to 'update' information in existing records, but I was browsing the
> 'SQL Book Online' and couldn't find any references on how to combine
> records from multiple tables. I know about the 'join' command, but my
> request is a bit different. Say I had a existing table with 1000
> records and I had the user input information into a similar table with
> 15 new records (exact same layout) and I wanted to merge the two tables
> into one table with 1015 records, how would I do this?
> Thanks for the recommendation, I'll check it out. Is it a general book
> or one specific to MS SQL?
> Richard
>
>
> Jim Young wrote:
have[vbcol=seagreen]
at[vbcol=seagreen]
last[vbcol=seagreen]
record[vbcol=seagreen]
other[vbcol=seagreen]
extract[vbcol=seagreen]
and[vbcol=seagreen]
lays[vbcol=seagreen]
|||The Celko book is about general relational database theory (very little
SQL). A good book about T-SQL, the SQL variant that SQL Server uses, is "The
Guru's Guide to Transact-SQL" by Ken Henderson. Also Microsoft Press's
"Inside SQL Server 2000" is an essential book for anyone that is in the
business of working with SQL Server.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:e6Ad%23LQ$EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> I'm not taking it personally, I appreciate you comments
> I had a feeling that I'd have to use the "select" command to filter the
> record(s) that I want to work with and then to think in terms of 'a set of
> records'.
> I have already defined primary keys for my imported DBF files. I know how
> to 'update' information in existing records, but I was browsing the 'SQL
> Book Online' and couldn't find any references on how to combine records
> from multiple tables. I know about the 'join' command, but my request is
> a bit different. Say I had a existing table with 1000 records and I had
> the user input information into a similar table with 15 new records (exact
> same layout) and I wanted to merge the two tables into one table with 1015
> records, how would I do this?
> Thanks for the recommendation, I'll check it out. Is it a general book or
> one specific to MS SQL?
> Richard
>
>
> Jim Young wrote:
|||Hi Norman,
That's exact what I was looking for, thanks!
I moved over all the DBF files into SQL. That extra table was the
template user entered data into before 'posting' the transaction.
I think I'll take yours (any others) advice and read a T-SQL specific
book. The book on-line I see are a great reference AFTER one becomes
more familiar with T-SQL.
I was intending to spend at least a week or two experimenting with
various database operations using small scale example, but I think I'll
order the book first
Thanks again.
Richard
Norman Yuan wrote:
> I do not question why you have the extra table with exactly the same layout
> (columns, I guess). It is very simple the do what you want. Assume, tableA
> contains the 15 (or any number of) rows of user inputs and you want to add
> all of them of some of them into tableB. You could use this SQL statement:
> INERT INTO tableB (Col1,Col2,Col3...)
> SELECT Filed1,Field2,Field3...
> FROM tableA
> WHERE ... /*here the WHERE clause allows you to choose what rows in tableA
> being transfered to tableB.
> You also can see from above SQL statement, tableA does not have to be the
> same structure as tableB. You only need to make sure the fields selected in
> SELECT...clause match the fields (field count and data type) those in INSERT
> INTO clause.
> Since you just moved to SQL Server/MSDE, I'd sit down for a couple of days
> to stduy/investigete T-SQL, rather than browse SQL Book on-line for
> particular processing.
>
|||Hi Jim,
Thanks for the recommendations. Those two sound more like what I am
looking for. I've been using relational databases (dBase, Clipper,
Paradox, FoxPro, etc) for years, but I agree that I should get one of
the T-SQL books.
My clients are all small businesses and use SBS 2000. In otherwords, I
don't want to spend time learning about larger enterprise systems, just
small, single server systems with 5-15 users.
I went to some seminars and they gave out SQL books but they were for
large organizations (multiple servers, load balancing, forest, trees,
advance security. etc).
Are either of these books geared for the small business? Is there one
you'd think suites my needs better?
Richard
p.s. Is the MS Press author is "Delaney"? I'd like to double check.
Jim Young wrote:
> The Celko book is about general relational database theory (very little
> SQL). A good book about T-SQL, the SQL variant that SQL Server uses, is "The
> Guru's Guide to Transact-SQL" by Ken Henderson. Also Microsoft Press's
> "Inside SQL Server 2000" is an essential book for anyone that is in the
> business of working with SQL Server.
> Jim
>
|||None of these books are written to address a specific implementation. They
will serve you well, no matter what your deployment size is. There have been
a couple of books written to address MSDE specifically. One that I have is
"MSDE Bible" by IDG Books. But, MSDE is so much like SQL Server that any
book that is useful for SQL Server will do for MSDE also. Most, if not all,
of the MSDE specific information can be found in the Books Online.
Jim
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:%23VBz2KZ$EHA.1260@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi Jim,
> Thanks for the recommendations. Those two sound more like what I am
> looking for. I've been using relational databases (dBase, Clipper,
> Paradox, FoxPro, etc) for years, but I agree that I should get one of the
> T-SQL books.
> My clients are all small businesses and use SBS 2000. In otherwords, I
> don't want to spend time learning about larger enterprise systems, just
> small, single server systems with 5-15 users.
> I went to some seminars and they gave out SQL books but they were for
> large organizations (multiple servers, load balancing, forest, trees,
> advance security. etc).
> Are either of these books geared for the small business? Is there one
> you'd think suites my needs better?
> Richard
> p.s. Is the MS Press author is "Delaney"? I'd like to double check.
> Jim Young wrote:
No comments:
Post a Comment