Copying data - 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 Administration (2000)

Copying data

Author

Topic

frejoh82
Starting Member

3 Posts

Posted - 2005-10-10 : 02:34:24

I have a DB where all table- and column-names are in my native language. Now I have set up a new DB where the table- and column-names are in English instead. What is the fastest way to copy the data from the original db to the new db? One way is to manually import the data with the import/export-wizard, but then I have to click import, choose source and destination, choose which source column that corresponds to each destination column, and so on, for each of the tables in correct order (due to the relationships among the tables) and then wait for all the data being copied. Is there any better way to do this?

Regards Fredrik

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 04:17:36

Are all the columns in the same order?

If so how about


INSERT INTO NewDatabase.dbo.MyTable
SELECT *
FROM OldDatabase.dbo.MyTable

You'll need to twiddle IDENTITY_INSERT for those tables where you have an Identity column.

The script could be generated by querying the INFORMATION_SCHEMA.TABLES view - something like


SELECT 'INSERT INTO NewDatabase.dbo.' + TABLE_NAME + '
SELECT *
FROM OldDatabase.dbo.' + TABLE_NAME
+ CHAR(13)+CHAR(10) + 'GO'
FROM	INFORMATION_SCHEMA.TABLES

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 04:46:26

or

Take a backup of OldDB

Backup Database OldDB to disk='C:\oldDB.bak'

Restore it to the newDB


RESTORE DATABASE newDB
   FROM DISK ='C:\oldDB.bak'
   WITH MOVE 'oldDB_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\data\newDB_Data.MDF',
   MOVE 'oldDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\data\newDB_log.MDF',replace

Madhivanan

Failing to plan is Planning to fail

frejoh82
Starting Member

3 Posts

Posted - 2005-10-10 : 08:23:16

Thank you for your answers.

The columns are in the same order, but the tables are not (because of that the names are changed to english names, but I don't know if that has any impact). Madhivanan, does your solution work when the names are different in the two DB:s? (I'm pretty new to SQL Server).

The DB:s are located at different servers that I have registered in the Enterprise manager, so I don't know if I can run a sql-query that selects from a DB on one server and inserts in a DB on another server?

Once again, thank you for your answers.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 08:52:13

Do you want to keep existing objects in the NewDatabase and only want to copy new objects from old Database to new?

Madhivanan

Failing to plan is Planning to fail

frejoh82
Starting Member

3 Posts

Posted - 2005-10-10 : 09:06:12

quote:
Originally posted by madhivanan

Do you want to keep existing objects in the NewDatabase and only want to copy new objects from old Database to new?

Madhivanan

Failing to plan is Planning to fail

I will run a script that creates the empty "english" tables in the new DB (with "swedish" views that let old applications continue working until I have changed the source code for the applications). So what I want is to copy the data from the old "swedish" DB into my new empty "english" tables in the new DB.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 09:10:17

Kris is correct

Run this in Query Analyser

SELECT 'INSERT INTO NewDatabase.dbo.' + TABLE_NAME + '
SELECT *
FROM OldDatabase.dbo.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

Then copy the results back to Query Analyser and run each insert Statement one by one

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources