Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Export contents of SQL 2000 table to overwrite another table?

Posted on 2009-04-23
6
Medium Priority
?
550 Views
Last Modified: 2012-05-06
I'm running SQL Server 2000 on a Windows Server 2003 machine, and I've got multiple databases set up, some of which are clones.

As of sometime last week, one of the tables in our primary database was damaged, and I'm trying to export the data from it's clone to that table. I want to go from one database table to another database table, copying the data but retaining the destination table's identification. Basically I just need to replace the damaged data with good data.

I'd prefer to use the Import/Export wizard if possible, as I'm not very good at SQL statements. Any help would be appreciated.

Thanks!
0
Comment
Question by:SysAdmin06
  • 3
  • 3
6 Comments
 
LVL 6

Expert Comment

by:Brainstormer
ID: 24215687
I do not remember the SQL server 2000 management tool, but in SQL Server 2005 Management Studio there is an option for "Generate Script" by right clicking on a database. There you decide the options and what table you want to include. I am sure something similar in 2000.
0
 

Author Comment

by:SysAdmin06
ID: 24216460
Brainstormer,

There is, thank you, but what do I do from there? I'm trying to copy the table SYACTFIL_SQL in the database data_001 ---> the table SYACTFIL_SQL in the database DATA_100. What kind of script do I generate? Insert, Select, Update, etc? When I choose one of those options, how do I write the script?
0
 
LVL 6

Accepted Solution

by:
Brainstormer earned 1500 total points
ID: 24216653
There may be 2 steps involved in process, depending on the corruption of the primary table.
Depending on weather you want to keep or not some data from the corrupt database there are 2 directions:

If the table and data is corrupt you will need to recreate the table structure again using Enterprise Manager to generate the TABLE CREATE script

If the table is intact, just the data is corrupt you need to use the Bulk Copy Program, a command line utility to dump the data from the "good" db to the "corrupt" db.

Some instructions on that are here:
http://www.codeproject.com/KB/database/ScriptDatabase.aspx
http://www.paladn.com/component/content/article/158.html
http://www.devarticles.com/c/a/SQL-Server/An-Introduction-To-The-Bulk-Copy-Utility/


There is also a possibility to transfer data between 2 tables of separate databases, see links, just make sure you have backups before you test:

http://www.eggheadcafe.com/conversation.aspx?messageid=31826523&threadid=31826523

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23632297.html


0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:SysAdmin06
ID: 24216701
Here's a script I borrowed from someone else and modified for my tables:

USE [data_001]
GO

SELECT *
INTO [DATA_100].[dbo].[SYACTFIL_SQL]
FROM [SYACTFIL_SQL]
GO

That claims to run successfully, but when I check the data in the destination table, it's still unchanged. Mainly, I'm trying to fill three columns of data that are currently filled with zeroes, with the data from the columns of the other table. After I run this, the destination table still has zeroes. Is there something I have to add to overwrite data?
0
 
LVL 6

Expert Comment

by:Brainstormer
ID: 24217306
The SELECT statement does not change anything, you need to use either INSTERT or most likely UPDATE.

Check this link for more info:http://www.eggheadcafe.com/conversation.aspx?messageid=31826533&threadid=31826523


0
 

Author Comment

by:SysAdmin06
ID: 24217387
Thanks for the help, Brainstormer. While researching, I found this:

http://www.ombelt.com/

You have to buy the tools individually, which is unfortunate, but their Copy Table tool is one of the easiest pieces of software I've seen. Enter source DB, source table, destination DB, destination table, go through a few very simple options and you're done. It worked perfectly, and saved me a ton of time and stress trying to figure everything out on my own.

Thanks for the great tips though!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question