I have two databases, one is called Natalie_playground
and one is called LiveDB
.
Since I want to practice insert, update things, I want to copy some of the tables from the LiveDB
to Natalie_playground
.
The tables I want to copy are called:
Customers, Computers, Cellphones, Prices
What I tried to do is that (using SSMS) right click on a table but there is no Copy in there!
Assuming that you have two databases, for example A and B:
If target table not exists, the following script will create (I do not recommend this way):
SELECT table_A.FIELD_1, table_A.FIELD_2,......, table_A.FIELD_N
INTO COPY_TABLE_HERE
FROM A.dbo.table_from_A table_A
If target table exists, then:
INSERT INTO TABLE_TARGET
SELECT table_A.FIELD_1, table_A.FIELD_2,......, table_A.FIELD_N
FROM A.dbo.table_from_A table_A
Note: if you want learn and practice this, you can use previous scripts, but if you want copy the complete structure and data from database to another, you should use, "Backup and restore Database" or, "Generate Script Database with data" and run this into another database.
Right click on your database -> under Tasks choose Generate scripts, follow the wizard, choose your tables and check the check box that says 'script table data' (or similar) generate it to an SQL script and execute it on your other DB.
You can also try SQL Server Import/Export wizard. If target tables do not exist already they will be created when you run the wizard.
Check out MSDN for more details http://msdn.microsoft.com/en-us/library/ms141209.aspx
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With