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

[mikek12004]

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

[ck9663]

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

[mikek12004]

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?)

[ck9663]

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

[pattr]

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.

[jordan51341]

choose destination database -->right click-->tasks-->import Data-->choose source databse ,then following the wizard choose the source table,ok?

[mikek12004]

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

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?

[ck9663]

Kindly make a separate post for this one so that others can view and learn from this.

Thanks,

-- CK

[mikek12004]

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

[ck9663]

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

[mikek12004]

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?

[ck9663]

What's the error?

-- CK

[meet2vikas1234]

@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

[mikek12004]

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

[meet2vikas1234]

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.

[mikek12004]

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

SELECT * INTO test.dbo.Table_1 FROM source.dbo.Table_1;
 

and for the restriction the SQL will produce something like

ALTER TABLE test.dbo.Table_1 ADD CONSTRAINT PK_Table_1 PRIMARY KEY (id)

[JB Brossard]

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.