Copy tables from one database to another in SQL Server
I have a database called foo and a database called bar. I have a table in foo called tblFoobar that I want to move (data and all) to database bar from database foo. What is the SQL statement to do this?
8 Answers 8
SQL Server Management Studio’s «Import Data» task (right-click on the DB name, then tasks) will do most of this for you. Run it from the database you want to copy the data into.
If the tables don’t exist it will create them for you, but you’ll probably have to recreate any indexes and such. If the tables do exist, it will append the new data by default but you can adjust that (edit mappings) so it will delete all existing data.
I use this all the time and it works fairly well.
On SQL Server? and on the same database server? Use three part naming.
This just moves the data. If you want to move the table definition (and other attributes such as permissions and indexes), you’ll have to do something else.
This should work:
It will not copy constraints, defaults or indexes. The table created will not have a clustered index.
Alternatively you could:
If your destination table exists and is empty.
If it’s one table only then all you need to do is
- Script table definition
- Create new table in another database
- Update rules, indexes, permissions and such
- Import data (several insert into examples are already shown above)
One thing you’ll have to consider is other updates such as migrating other objects in the future. Note that your source and destination tables do not have the same name. This means that you’ll also have to make changes if you dependent objects such as views, stored procedures and other.
Whit one or several objects you can go manually w/o any issues. However, when there are more than just a few updates 3rd party comparison tools come in very handy. Right now I’m using ApexSQL Diff for schema migrations but you can’t go wrong with any other tool out there.
Как скопировать таблицу sql
Существует несколько способов копирования таблицы в базе данных MS SQL Server. Предлагаю несколько вариантов создания копии таблиц. Какой из них выбрать – зависит от структуры таблицы, наличия в ней индексов, триггеров и т.п., а также желания делать что-то руками.
1. Ручной метод копирования структуры таблицы
В Micrisoft SQL Management Studio выбрать базу, выбрать таблицу, нажать правой кнопкой мыши и выбрать пункты «Script Table as» -> «CREATE TO» -> «New Query Editor Window». В окне запроса откроется код для создания таблицы. В нем нужно указать имя базы, в которой нужно сделать копию таблицы, и новое имя, если база не меняется. Как создать код для создания структуры имеющейся таблицы, показано на рисунке ниже.
Как создать код для создания структуры имеющейся таблицы
С помощью этого способа будут созданы индексы таблицы, но не скопируются триггеры. Их нужно копировать аналогичным способом.
Для копирования данных в уже созданную таблицу нужно использовать такой SQL запрос:
Клонирование таблиц в MySQL
Необходимо создать копию таблицы со всеми данными или же только копию структуры?Воспользуемся стандартными запросами MySQL для этих нужд.
Задача: нужно создать новую пользовательскую таблицу со структурой, аналогичной существующей таблице. Эта обычная ситуация, когда нужно просто скопировать структуру и ключи некоторой таблицы, без копирования данных. Выполняем запрос:
Задача: нужно склонировать таблицы базы данных со всеми данными
В случае, если вам нужно клонировать всю таблицу вместе с данными, то ниже приведенный метод может помочь вам.
How To Clone Tables in SQL
In database operations, there may be a time when you need to clone or duplicate an existing table to a new table due to their similarities in columns and attributes either for performing a test without affecting the original table or other personal reasons.
I bumped in this situation whereby I need to create a new set of tables for a new feature we are integrating. This table has quite a lot of columns and very similar to an existing table handling another set of similar data of a particular feature.
Since this existing table and the new one are quite similar, my quick solution to this is to clone the existing table to create the new table.
In SQL it's quite easy to do this as you can easily run a couple of commands to best suit your cloning needs.
In this article, I will be showing you how to duplicate and clone existing tables in SQL.
Simple Cloning
The first method is called Simple Cloning and as its name implies it create a table from another table without taking into account any column attributes and indexes.
So if I have a table called users , I can easily create another table called adminUsers without caring about the users table column attributes and indexes.
The below SQL command creates a simple copy of the users table.
Use this in order to quickly clone any table that only includes the structure and data of the original table.
Shallow Cloning
Shallow cloning is mostly used to create a copy of an existing table data structure and column attributes without the data being copied. This will only create an empty table base on the structure of the original table.
The following command would create an empty table base on the original table.
Use this if you only want the data structure and column attributes of the original table
Deep Cloning
Deep cloning is quite different from Simple Cloning and similar to Shallow cloning but with data, and as its name implies it creates a deep copy of the original table.
This means the new table gets to have all the attributes of each column and indexes of the existing table. This quite useful if you want to maintain the indexes and attributes of the existing table.
To achieve this, we’ll have to create an empty table base on the structure and attributes of the original table, then select data from the original table and insert into the new table.
To easily clone our original and also have its data copied:
Another cool thing now is, let's say you don't want all the data from the existing table and only want some data, based on some conditions then fine-tuning your SELECT queries are your best bet.
For instance, we have users with userType="admin" in our users table and we want to only copy those users only to our new table, you can easily do that like below: