Creating Database Table and Moving Data

Posted: July 1, 2012 in SQL Server
Tags: , , ,

When the need comes to port tables from one server / database to another server / database there is an easy way than creating the tables manually and importing data into table.

By using the following query you can import the table structure and the data of the required table.

  1. SELECT *
  2. INTO [DestinationServerName\SQLServerInstanceName].[DestinationDatabaseName].[OwnerName].[DestinationTableName]
  3. FROM [SourceServerName\SQLServerInstanceName].[SourceDatabaseName].[OwnerName].[SourceTableName]

Using the above method you can transfer tables between different databases, Server instances or different database servers.

Consider the following example in which I am transferring the Customers table from Northwind database to my Test database.

  1. SELECT *
  2. INTO [Test].[dbo].[Customers]
  3. FROM [Northwind].[dbo].[Customers]

Here SELECT statement will behave the same way as in a normal SELECT * FROM TableName statement meaning you can use all the techniques used in SELECT statements to filter the data needed to be ported. For example following query will only bring customers who are from UK.

  1. SELECT *
  2. INTO [Test].[dbo].[Customers]
  3. FROM [Northwind].[dbo].[Customers]
  4. WHERE Country = 'UK'

One thing to remember is, this method will not import the keys, meaning if you had a primary key set to CustomerId it will not exist in your new table. So you need to add the keys (primary and secondary) to the new table to match it with the source table. If you had identity specification turned on in your source table this will set it for you, so you do not need to set it again.

Advertisements
Comments
  1. I found something similar elsewhere and really liked. Some more of this please! Thx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s