Copying data from one pc to another in SQL

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

General SQL Server Forums

New to SQL Server Programming

Copying data from one pc to another in SQL

Author

Topic

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-02-08 : 06:28:05

Hi all,
I am totally new in SQL.
I have sql installed in 2 pc's. One for my testing and one for my team to use.
Any flat file I import in sql is done on my c and tested. If file I copy those table with DTS wizard to the another SQL server running on another pc.

Can this be automated? I want only tables to be included.

Pl advise.

Regards,
Spareus

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 06:37:40

~~Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139512~~

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-02-08 : 06:41:20

I have already deleted another duplicate post of this same message.
Pl help.

Regards,
Spareus

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 06:46:39

Pity you didn't delete this, later, one as I had already typed my answer in your earlier message but that was lost because you had deleted it.

You want to re-create just empty tables on the second PC, with no data?

Best to do this by generating a Script of the tables on the First PC, and then running that on the second PC.

In enterprise manager (assuming SQL 2000) right click the database and choose "Generate scripts", select the tables, and the options for whether you want indexes etc. to be included, and whether any existing tables should be Dropped and then Recreated (which will lose any data they contain).

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-02-16 : 02:51:13

Thanks Kristen.
Sorry for the delay in the reply.
But I want to copy tables along with the data. I have not created any Indexes or primery keys.
(just coz I dont know the advantages / dis advantages of the same.
This will be used daily by my team and updated on daily basis from my pc to another team pc.

I hope to get some solution.

Regards,
Spareus

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 03:13:02

quote:
Originally posted by spareus

Thanks Kristen.
Sorry for the delay in the reply.
But I want to copy tables along with the data. I have not created any Indexes or primery keys.
(just coz I dont know the advantages / dis advantages of the same.
This will be used daily by my team and updated on daily basis from my pc to another team pc.

I hope to get some solution.

Regards,
Spareus

Are you trying to make the dbs on two pcs in synch? if yes, then i think what you need is to setup replication between them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-02-17 : 01:49:39

Hi visakh16,
Thanks. You explained it in right words. Both Db will be in sync except few tables and views i created for testing.
If replication is right thing, Pl let me know how to proceed.

Regards,
Spareus

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 01:54:50

quote:
Originally posted by spareus

Hi visakh16,
Thanks. You explained it in right words. Both Db will be in sync except few tables and views i created for testing.
If replication is right thing, Pl let me know how to proceed.

Regards,
Spareus

Yup. then replication would be right thing.
However how frequently you want sync to happen? if real time, what you need is transactional replication.
Anyways refer the below

http://www.databasejournal.com/features/mssql/article.php/1438201/Setting-Up-Transactional-Replication-A-Step-by-step-Guide.htm

http://www.databasejournal.com/features/mssql/article.php/1438231/Setting-Up-Merge-Replication-A-Step-by-step-Guide.htm

http://www.databasejournal.com/features/mssql/article.php/1458491/Setting-Up-Snapshot-Replication-A-Step-by-step-Guide.htm

http://www.sqlteam.com/article/choosing-a-replication-type

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-02-17 : 02:54:57

Hi visakh16,
Many thanks for your pointers. I will go thru the same.
However, I do not need sync in real time. Sync will be done at night on daily basis.
Thanks again.

Regards,
Spareus

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 03:00:16

quote:
Originally posted by spareus

Hi visakh16,
Many thanks for your pointers. I will go thru the same.
However, I do not need sync in real time. Sync will be done at night on daily basis.
Thanks again.

Regards,
Spareus

then you dont really need replication. what you can do is design a set of ssis packages which captures the deltas (changed data) of your required objects (tables/views) and do the relevant operations in your other db (update/insert/delete). you can create a master package to execute all of these individual packages and then schedule master package to execute each night at your convienient time by means of sql agent job.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-02-17 : 05:38:27

Well, I will need more help from you. There is nothing like ssis in help file. Please elaborate. Also I am not sure about what you mean by design of packages. I have seen only DTS packages which can be saved in sql server. There is no way in that waizard where I can execute multiple DTS packages. Request you to please explain me how to do this or where can I find it.

Thabnks for your time given to help me.

Regards,
Spareus

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 05:46:39

quote:
Originally posted by spareus

Well, I will need more help from you. There is nothing like ssis in help file. Please elaborate. Also I am not sure about what you mean by design of packages. I have seen only DTS packages which can be saved in sql server. There is no way in that waizard where I can execute multiple DTS packages. Request you to please explain me how to do this or where can I find it.

Thabnks for your time given to help me.

Regards,
Spareus

SSIS means SQL Server Integration Services. Its an advanced tool like DTS and similar to DTS package you create ssis packages here with the extension .dtsx
You can design packages using SQL Business Intelligence development studio that comes with workstation components in installation CD.
You'll be designing packages in visual studio IDE or business intelligence IDE which will have similar but much more advanced tasks like DTS.

see steps of how to create a package here

http://msdn.microsoft.com/en-us/library/ms169917.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

spareus
Yak Posting Veteran

52 Posts

Posted - 2010-02-17 : 07:48:07

I have searched all menus in my SQL server enterprise manager and quary analyser but could not find ssis. Some researche in google told me that it is available in new sql servers but in sql 2000 not available. Any other idea?

Regards,
Spareus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources