renamed tables, causing issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

renamed tables, causing issue

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-26 : 07:26:32

Hi,

I created another database, and imported everything from another database. I then renamed all the tables. Problem is, I have this same database on another server that I need to update now.

How can I easily import the data into these tables? (they have the exact same fields, just the table name is different).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-26 : 08:07:21

One way:
In the DTS import/export wizard you have the opportunity to select your target table(s). The default name is the name of the source table, but on a table by table basis, you can select different a different table as the target.

Be One with the Optimizer
TG

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-26 : 10:30:33

Actually, now that I think of it. Its probably easier for me just to create a script that simply renames the tables! hehe

basically, my tables are like: mytableprefix_Users

I changed mytableprefix_ to say: ACURACARS_Users

So I can either manually change them in EM, or someone can be so kind to help me with a script....

or even a alter script would be cool

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-26 : 11:12:28

What name do you want the tables to have? If you want to keep the current name but use the old one then you can create views to access them:

something like

declare @old varchar(100), @new varchar(100)
select @old = 'mytableprefix_', @new = 'ACURACARS_'

select 'create view ' + @old + replace(name, @new, '') + '
as
select * from ' + @new + replace(name, @new, '') + '
go'
from sysobjects where xtype = 'U'
and name like @new + '%'

run the result to create the views

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources