copy database one database to onther database

hi

i want to copy one database table to onther database table using script?

my database is ms-sql server 2000 


Nothing is really over,untill the moment stop trying for it...
Amitsp(MCTS,MCP)
sqlreporting.blogspot.com
0
prajapatiamit2003
3/27/2007 10:31:08 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
1243 Views

Similar Articles

[PageSpeed] 17

You can use import/export task and DTS package created automatically by Enterprise manager or just try

 select * INTO [newdatabase].dbo.[newTable] from [sourceDatabase].dbo.[sourcetable]

 good luck


Thanks

JPazgier
0
jpazgier
3/27/2007 11:43:23 AM

no dear i want to this programattically

iusing c #,source is my database in local machine and target is other computer means server 


Nothing is really over,untill the moment stop trying for it...
Amitsp(MCTS,MCP)
sqlreporting.blogspot.com
0
prajapatiamit2003
4/4/2007 6:15:05 AM

I use SQL Packager form Red-Gate for that. It generates .Net code and SQL for you. You can download a trial from http://www.red-gate.com/products/SQL_Packager/index.htm.


Richard
http://www.richardsoeteman.net
0
richardsoeteman
4/4/2007 6:20:55 AM

Hi prajapatiamit2003,

 

About copying data from tables which in different SQL servers. You can use OPENDATASOURCE query data from remote Server and insert data to local table:

 

INSERT INTO Server1.dbo.Categories SELECT * FROM OPENDATASOURCE(

         'SQLOLEDB',

         'Data Source=Server2;User ID=MyUID;Password=MyPass'

         ).Northwind.dbo.Categories

 

Try this code in Query Analyzer first, If it work perfectly then you can use it in C# like:

    string queryString = "T-SQL above";

    using (SqlConnection connection = new SqlConnection(

               connectionString))

    {

        SqlCommand command = new SqlCommand(queryString, connection);

        connection.Open();

        command.ExecuteNonQuery();

    }

If the T-SQL works fail in Query Analyzer, you should first troubleshoot base on the error message.

 

Hence, if the table is small, I suggest you another solution with a little lower performance.

There are two steps for this solution:

  1. Get data from remote table use SqlDataReader and fill data in DataTable.
  2. Then insert every rows from DataTable to table in SQL Server by following code: 

        foreach(TableRow row in table.Rows)

        {

            command.CommandText= String.Format("INSERT INTO table VALUES ( {0},{1})", row[0], row[1]);

            connection.Open();

            command.ExecuteNonQuery();

            connection.Close();

        }

 

Becuase data will be loaded into an in-memory DataTable object first and then insert into table one by one, so the performance may be very poor if you want to copy large table.

I hope this helps.

 


Sincerely,
Benson Yu
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
0
Benson
4/4/2007 8:19:33 AM
Reply:

Similar Artilces:

Coping Database objects from one database to another blank database.
I want to create a duplicate database   in sql 2000 using asp.net from a webform I created a database using CREATE DATABASE ....... But how to copy tables, views, stored procedures to newly created database from old using asp.net from webform Is there any another method to create a duplicate database with another name from existing database on same server ? yes you can do have another DB with different name and that has everything the same .....!!!Fadil Alnassar www.fadilalnassar.com | FREE Nodil Tab Controlhttp://www.mefranchising.com One way it can be done is with...

copying tables from one database to another database
i am using visual web developer 2005 and SQL Express 2005 with VB  as the code behindi have two databases . i want to copy all the tables with all the contents from one database to another database programaticallyhow to achieve this ?please help me  easiest way is to use the detach - attach method or backup and restoreKH...

How to copy a table from one database to another database ?
Hello to you, I'm newbie using pb8.02 I'm uisng ms-access 2000 I'd database abc & xyz that contains customer table I want to copy some record on abc.customer to xyz.customer How ? What is the easy way ? should I using datawindow ? (i meant should I always had dw to got this purpose ? ) please advice steven DataWindow purists will flinch, but I think an embedded SQL INSERT INTO xyz.customer (SELECT ... FROM abc.customer) is the clearest way to do that. Two datawindows/datastores, retrieve the first, RowsCopy, Update on the second will also work. Pi...

How to copy table from one database to another database ?
Hello to you, I'm uisng pb8 How to copy some rows from customer table in (ABC database) to customer table in (XYZ databases) the selection row is : select * from customer where startdate = 2002-06-15 Please show me the code, step by step steven On Sun, 16 Jun 2002 22:30:37 -0400, steven wrote: >Hello to you, >I'm uisng pb8 > >How to copy some rows from customer table in (ABC database) to customer >table in (XYZ databases) > >the selection row is : >select * from customer where startdate = 2002-06-15 > >Please show me the code...

how to copy one database entries to another database
hi i have a windows based application and a web based application. the database of windows based application and web based application are same but located on different machiene. like windows based application is located on local machiene and web based databased is located on another machiene. Now my prob is that after inserting all the entries in local database i want to copy these entries to web database using my windows application on single click of the button. but i have no idea about it. how could i do this. Thanks in adwance. well you have to create a DTS id ur using sql 2000 or SSI...

Move a Table from one SQL 2005 Database to Another SQL 2005 Database
I created a table in an SQL 2005 database, however, I want to move that table and the data to another SQL 2005 database (within the same server). I am new to SQL 2005 and detailed instructions would be greatly appreciated. Thanks Bill You have two options the first is clean but the second is easier.  Try the links below for details.  Hope this helps. http://msdn2.microsoft.com/en-us/library/ms174335.aspx http://msdn2.microsoft.com/en-us/library/ms188029.aspx  Kind regards,Gift Peddie Thanks, but the documentations is somewhat overwhelming and was hoping of a ba...

How to copy a database table and copy it into a different database?
Hello I am a software developer with minimal SQL server administration skills. Currently I am using SQL Server 2000.I need to know if there is a way to copy a particular table from a database, and to copy the table into a different database.Basically on a project I am working on we are using a table named "Customers" from a database named QTR. We need to copy this database table into a different database named "Research". How can this be done? Is if very complicated? start sql enterprise manager.select the research databaseright clickselect all tasks | importfollow...

Copy Whole DataBase To Other DataBase
Hi..    I Have One DataBase "DB1" which contains number of tables,constrains,functions,triggers,store procedures and data entry also,I want to copy all this thing into my new DataBase "DB2", If i take back up file and restore it or import export from Old DataBase to New DataBase then i can't get whole details of the old DataBase,some of the parameter are missing,while i want whole DataBase Details into new DataBase.Kindly request You please help me regarding this issue..it's urgent for me...Thanks in advance. ~ Mark As Answer If UseFull ~Kausha...

Backing up the sql database(*.bak) and restoring the Database in VB.net
Hi There, I want to know how to backup and restore database from VB.net. ie I am trying to put two buttons on my form, Backup and Restore. When I click on Backup a Backup of the SQL Database with extension of *.bak is created. Similarly when I click on restore and select a database with *.bak extension it should restore that database. I saw a software where it created a backup of Sql database with extension of .zip containing the bak file which is password protected. when u restore the database it automatically gets unzipped and the database is restored. Can anyone help me. Thanks in advan...

Connecting to Sql database and retrieving one row of data from database
Hi Guys.I am trying to connect to a Sql Database in my application through an object of SqlConnection class,then trying to read data one row at a time through DataRow class and then displaying each column(there are total 3 columns in a row) in three textboxes.Can anyone tell me the code to do so? Hi, Reffer this link for connecting with sql server and also with Insert update Delete and select statements http://www.aspsnippets.com/post/2009/02/09/Parameterized-Queries-ADONet.aspx   Best Regardslife is name of learning!Mark as an answer if it helps...

How to write a stored procedure to copy the data from one database to another database
 I want to copy the data from one database to another database every 12 hrs.(This is variable). Say for example I have 3 applications and I want to copy the data from Parentdatabase1 and insert into CopyofParentdatabase1 for every 6 hrsParentdatabase2 and insert into CopyofParentdatabase2 for every 8 hrsParentdatabase3 and insert into CopyofParentdatabase3 for every 12 hrsI have heard that we can do this by using SQL Server Integration Services but as of now we cannot use SSIS. So I want to do this using Stored procedures. Is there a way to schedule the batch job for to run all these st...

import one table from one database to another database
if i have one table from database A, and i want to copy the table to database B, then how to do it in SQL server management studio express? is it i have to make sql query?Taj FreewarezNo limitations...It is not a shareware...It is simply free :)http://tajfreeware.blogspot.com try      select * into databaseB.dbo.TableA            from databaseA.dbo.TableA or go to database B and run   select * into TableA              from databaseA.dbo.TableA...

how to access other database in this database ?
Hi,Everyone,I used to use oracle 8i,but now for some reasons,I must change to ASE11.5,at the beginning,I don't know how to access tables among databases,in oracle, if I grant "select" to some user,this user will access other user's table in username.tablename format,I want to know how to implement this functions,please help me! thanks! Very similar in sybase ASE. grant SELECT on <user>.<tbl> to <newuser> for new user: select * from <user>.<tbl> Patrick wrote: > Hi,Everyone,I used to use oracle 8i,but now for some rea...

database in database solution
  i have a database in the database solution. but i want to add the database from database solution to App_data  folder in solution explorer. how to do this ?Jai Ganesh. J , GSD ,IndiaPlease Mark As Answer If my reply helped you.   i am having visual studio express edition and sql management express. i have opened solution explorer for a asp.net project. I have the database solution and i have the App_data in the solution Explorer. I have added a existing sql database in the database solution and wanted the newly added  sql database in the App_data of solution...

Web resources about - copy database one database to onther database - asp.net.sql-datasource

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

ERISA and All Claim databases
... laws, on the books in 18 states, requiring self-insured employers to report data about the prices they pay to “all payer claims databases.” ...

Sanders’ campaign looses access to N.H. Democratic voter database - The Boston Globe Sanders’ campaign ...
... the presidential campaign of US Senator Bernie Sanders has been denied access to the New Hampshire Democratic Party’s valuable voter database ...


Under pressure, DNC restores Bernie Sanders campaign's access to their database - Videos - CBS News
A strange twist in the race for the democratic presidential nomination: the campaign of Vermont Senator Bernie Sanders has filed a complaint ...

Over 680TB of data exposed in MongoDB databases
There are at least 35,000 publicly accessible and insecure MongoDB databases on the Internet, and their number appears to be growing. Combined ...

Database error publishes data of 3 million Hello Kitty fans
Online researcher Chris Vickery uncovered a database this weekend containing the personal information of 3.3 million accounts associated with ...

MacKeeper Leak Highlights Danger of Misconfigured Databases
The Shodan port-scanning service finds at least 35,000 MongoDB databases accessible without a password.

OracleVoice: Oracle Challenges SAP On In-Memory Database Claims
Companies that run SAP’s applications should know that most of those applications can perform much better on Oracle Database than on any other ...

Resources last updated: 12/25/2015 11:04:36 AM