Update Table

[Jonas Asberg]

Hi,

I got a new SQL Server database and are in the process of porting my
Access databases to my new SQL Server. I have worked with Access and
Oracle in the past but I am new to the SQL Server environment. I have
a tool called ConVersion that is helping me rewrite my queries in
Access into SQL Server procedures. I got a few of them to be error
free but when I run them I get a message stating that there is already
data in a table with that name. Why is it not overwriting my previous
data if I have a create table statement? What can I do to overwrite my
old data and repopulate it in Access I could just have a create table
and it would overwrite my old data with new data, is this not possible
in SQL Server? Do I have to have two procedures one delete procedure
and one create to get this done? I would apprecate all the help I can
get.

Thanks,

JCA

[John Bandettini]

Re: Update Table

Jonas

You need to check if the table exists, and delete it if it does before
running a create table. Format would be like this. (tblLog is the
table name in this example.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblLog]') and OBJECTPROPERTY( id, N'IsUserTable') =
1)
drop table [dbo].[tblLog]
GO


Hope this helps

John

jonascasberg@ya hoo.com (Jonas Asberg) wrote in message news:<9dbdc505. 0411282048.46f7 9c0b@posting.go ogle.com>...[color=blue]
> Hi,
>
> I got a new SQL Server database and are in the process of porting my
> Access databases to my new SQL Server. I have worked with Access and
> Oracle in the past but I am new to the SQL Server environment. I have
> a tool called ConVersion that is helping me rewrite my queries in
> Access into SQL Server procedures. I got a few of them to be error
> free but when I run them I get a message stating that there is already
> data in a table with that name. Why is it not overwriting my previous
> data if I have a create table statement? What can I do to overwrite my
> old data and repopulate it in Access I could just have a create table
> and it would overwrite my old data with new data, is this not possible
> in SQL Server? Do I have to have two procedures one delete procedure
> and one create to get this done? I would apprecate all the help I can
> get.
>
> Thanks,
>
> JCA[/color]

[Erland Sommarskog]

Re: Update Table

Jonas Asberg (jonascasberg@y ahoo.com) writes:[color=blue]
> I got a new SQL Server database and are in the process of porting my
> Access databases to my new SQL Server. I have worked with Access and
> Oracle in the past but I am new to the SQL Server environment. I have
> a tool called ConVersion that is helping me rewrite my queries in
> Access into SQL Server procedures. I got a few of them to be error
> free but when I run them I get a message stating that there is already
> data in a table with that name. Why is it not overwriting my previous
> data if I have a create table statement?[/color]

There is no implicit DROP TABLE with CREATE TABLE (or CREATE anything
else for that matter) in SQL Server. And I think most people would prefer
that way. You maybe want to lose your old data, but most other people
probably want to keep theirs.
[color=blue]
> What can I do to overwrite my old data and repopulate it in Access I
> could just have a create table and it would overwrite my old data with
> new data, is this not possible in SQL Server? Do I have to have two
> procedures one delete procedure and one create to get this done? I would
> apprecate all the help I can get.[/color]

I don't know about the ConVersion tool, but typical for this kind of srcipt
is to have something like:

IF EXISTS (object_id('tbl ', 'U') IS NOT NULL
DROP TABLE tbl
go
CREATE TABLE ...

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[jonascasberg@yahoo.com]

Re: Update Table

Thanks for the book link. I am very excited to get a chance to learn
SQL Server with a new database but it can be a daunting experience at
times. Is there any other tools that you guys can suggest that I use to
port my MS Access to SQL Server databases? I hear that there are great
tools such as Erwin Data that I can use to Reverse Engineer my Access
database to a model and then forward engineer it to SQL Server, does
this sound feasible at all?

[Erland Sommarskog]

Re: Update Table

(jonascasberg@y ahoo.com) writes:[color=blue]
> Thanks for the book link. I am very excited to get a chance to learn
> SQL Server with a new database but it can be a daunting experience at
> times. Is there any other tools that you guys can suggest that I use to
> port my MS Access to SQL Server databases? I hear that there are great
> tools such as Erwin Data that I can use to Reverse Engineer my Access
> database to a model and then forward engineer it to SQL Server, does
> this sound feasible at all?[/color]

I guess that there are several ways to skin the cat. MS has an Access
Upgrade Wizard, but of what I have heard from people who know both
Access and SQL Server, the result it produces is poor.

There are huge differences between Access and SQL Server, and I seem to
recall that I have heard, that you get the best quality with manual
rewriting.

I should hasten to add that I don't have any experience of Access, or
porting from Access, at all.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ryan]

Re: Update Table

I've spent a lot of time working with MS Access and SQL and like
Erland says, the best method (IMHO) is to do a manual re-write. If I
had to use a tool to do this, I use the Borland Datapump (provided
with Delphi/Paradox)to give me the basic structure and then work on
the rest from there correcting as I go. It is however, worth the
effort to move to SQL in most cases.

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns95B0EE 73A456DYazorman @127.0.0.1>...[color=blue]
> (jonascasberg@y ahoo.com) writes:[color=green]
> > Thanks for the book link. I am very excited to get a chance to learn
> > SQL Server with a new database but it can be a daunting experience at
> > times. Is there any other tools that you guys can suggest that I use to
> > port my MS Access to SQL Server databases? I hear that there are great
> > tools such as Erwin Data that I can use to Reverse Engineer my Access
> > database to a model and then forward engineer it to SQL Server, does
> > this sound feasible at all?[/color]
>
> I guess that there are several ways to skin the cat. MS has an Access
> Upgrade Wizard, but of what I have heard from people who know both
> Access and SQL Server, the result it produces is poor.
>
> There are huge differences between Access and SQL Server, and I seem to
> recall that I have heard, that you get the best quality with manual
> rewriting.
>
> I should hasten to add that I don't have any experience of Access, or
> porting from Access, at all.[/color]