By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

How to copy a table (with the data) from one server to another?

mikek12004
100+
P: 200
How to copy a table (with the data) from one server to another (the two servers are in different PCs)? Basically if I could move the data to a txt file inthe form of the insert command (like the import/export of phpmyadmin) all would be ok
Dec 3 '08 #1
Share this Question
Share on Google+
16 Replies


ck9663
Expert 2.5K+
P: 2,878
Do you want to transfer the table in SQL Server A to SQL Server B as a table? Or you want to export the content of a table from SQL Server A to another PC?

-- CK
Dec 3 '08 #2

mikek12004
100+
P: 200
Tables from SQL sever A to SQL server B which is in different host (those two are not related in any way) (For the table sructure I script the table as "Create into" take the query and run it in the other SQL -with the appropriate modifications -is anything similar for the table data?)
Dec 4 '08 #3

ck9663
Expert 2.5K+
P: 2,878
You can use your management studio and use the Import wizard or connect to the other SQL server through linked server and do a SELECT * INTO...

-- CK
Dec 4 '08 #4

P: 1
you can use the sql import export wizard for copying the table structure with data.but am not sure if u are copying more than table,the relationship will exists in the new table or not.
Dec 29 '08 #5

P: 2
choose destination database -->right click-->tasks-->import Data-->choose source databse ,then following the wizard choose the source table,ok?
Jan 7 '09 #6

mikek12004
100+
P: 200
A little different problem, how can you copy a table (with its data) from one database to another when BOTH DATABASES ARE ON THE SAME SQL server? Tried the
Expand|Select|Wrap|Line Numbers
  1. SELECT * INTO NewTable FROM existingdb.dbo.existingtable;
but then when I checked the resulting generated scripts from the 2 tables saw that the declaration of a column as primary key was gone () any suggestions?
Jan 20 '09 #7

ck9663
Expert 2.5K+
P: 2,878
Kindly make a separate post for this one so that others can view and learn from this.

Thanks,

-- CK
Jan 20 '09 #8

mikek12004
100+
P: 200
Didn't get it, make a different post you mean start a new thread? For this case I guess the title of this post includes this question also. In any case here are a bit more details, I have a databese with some tables which get regulary updated I want these tables to be able to 'copy' them in a 'backup' database make some other arrangments to the original database and then 'post' them back. As mention above the 'SELECT INTO' command makes all my primary keys disappear, so for the tables structure I will take the create scripts generated by SQL for the tables I want and then I will populate them with an 'insert' into my problem is that before all that I want to be able to check if a table exists in the backup database and if it does to make a 'drop' so the following 'create table' command would not cause an error saw somewhere something like 'if exists' but couldn't make it to work. Again any ideas would be helpful
Jan 21 '09 #9

ck9663
Expert 2.5K+
P: 2,878
SELECT INTO creates a new table. Since it's a new table, all relationship, constraints, etc will not be automatically created. This looks like a production data. What you could do is to backup the entire database twice. One is your actual backup that you can keep/archive. The other is for you to do your "other arrangements". You have to put your production database offline first so no one can change it. After you're done with your "arrangements" drop your production database and use your "arranged" database as your new production.

To check if the table is existing, trythis function.

Good luck.

-- CK
Jan 21 '09 #10

mikek12004
100+
P: 200
The problem is that from the one databese I want 1 table (say 2 columns one primary key with autoincrement) to copy it with its data to another database tried the "insert into DB.table1 select * from DB.table2" but throws error when trying to write in the primary key column also tried the export wizard still the same. Any ideas?
Jan 30 '09 #11

ck9663
Expert 2.5K+
P: 2,878
What's the error?

-- CK
Jan 30 '09 #12

P: 2
@ck9663
Dear can you tell me, i have a problem when i export any database from server a to server b then tables are export but there primary key and Auttoincrement and bind valuse not moved. you have to again set it

tell me what to i do to export table with these valuse.

regards
vikas
Apr 22 '09 #13

mikek12004
100+
P: 200
Personally I wanted to move 15 tables form database A to B in SQL 2008 so I used the "insert to" command and then for all restriction (primary keys, etc) which are in seperate folder on the right tree-view panel. right-click script as->add restriction (or something like that) and the SQL will automatically generate the code for each restriction, executed that code in the new database and vouala! you're done
Apr 23 '09 #14

P: 2
i am using sql 2005. in this i open data base A then onthe table i right click and then chosse script-->create to-->new query editer
then i database B then i open his New query editer and past that script but it show error near '(' but i dont thing so there is some erro

tell me what to i do.
Apr 23 '09 #15

mikek12004
100+
P: 200
Try the import/export wizard, this will get you the data for sure if the restrictions re not copied you will have to do it manually with each table as said above,
to copy table/data use
Expand|Select|Wrap|Line Numbers
  1. SELECT * INTO test.dbo.Table_1 FROM source.dbo.Table_1;
  2.  
and for the restriction the SQL will produce something like
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE test.dbo.Table_1 ADD CONSTRAINT PK_Table_1 PRIMARY KEY (id)
Apr 24 '09 #16

P: 1
To copy data from one table to another where the tables are located in different databases (regardless of their type or location), you can use a tool called Data Moving Tool. It does exactly what you need.
Nov 28 '10 #17

Post your reply

Sign in to post your reply or Sign up for a free account.