By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Update Table

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

jo**********@yahoo.com (Jonas Asberg) wrote in message news:<9d**************************@posting.google. com>...
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

Jul 20 '05 #2

P: n/a
Jonas Asberg (jo**********@yahoo.com) writes:
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?
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.
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.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

P: n/a
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?

Jul 20 '05 #4

P: n/a
(jo**********@yahoo.com) writes:
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?


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

P: n/a
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 <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
(jo**********@yahoo.com) writes:
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?


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.

Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.