Solved

How to copy a table from one database into another?

Posted on 2014-03-09
18
353 Views
Last Modified: 2014-03-10
MS SQL Server 2012

Hi. I've been researching this and can't quite find the right SQL syntax to use.

I need to copy a table from one database into another (on the same instance of MS SQL Server 2012).

Something like

SELECT *
INTO database name / table name
FROM database name / table name

Thank you for your help.

Eric
0
Comment
Question by:Eric Bourland
  • 10
  • 4
  • 2
  • +2
18 Comments
 
LVL 7

Assisted Solution

by:Anoo S Pillai
Anoo S Pillai earned 63 total points
Comment Utility
insert  into targetDB.targetSchema.TargetTable ( Cols)
select sourceCols
from   sourceDB.SourceSchema.SourceTable  

The following script illustrates the concept :-

use tempDb 
create table dbo.TargetTab( n1 int , n2 int ) 
Go 
use master 
create table dbo.SourceTab( n1 int , n2 int ) 
Go 
insert into master.dbo.SourceTab (n1, n2 )  values ( 1, 2) 
go 
select * from master.dbo.SourceTab
select * from tempDb.dbo.TargetTab
go 
insert into tempDb.dbo.TargetTab
select n1 , n2 from master.dbo.SourceTab 
go 
select * from master.dbo.SourceTab
select * from tempDb.dbo.TargetTab
go 

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 249 total points
Comment Utility
You can also do this (assuming that the table does not exist on the target database):
USE SourceDatabase

SELECT *
INTO TargetDatabase.dbo.tablename               -- Assuming the schema is dbo
FROM dbo.tablename
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Interesting! Here is what I am working with:

Source database: cepDC
Source table: tbl_CEP_Documents
Source columns:
DocumentID
,DocumentTitle
,DocumentType
,DocumentAuthor
,DocumentAbstract
,DocumentKeyword
,DocumentImage
,DocumentPublicationDate
,SSMA_TimeStamp
,DateRecordModified

Target database: tnasca
Target table: TNASCADocuments
Target columns: same as source

Would the SQL query go something like:  
 
insert  into tnasca.targetSchema.TNASCADocuments (
DocumentID
,DocumentTitle
,DocumentType
,DocumentAuthor
,DocumentAbstract
,DocumentKeyword
,DocumentImage
,DocumentPublicationDate
,SSMA_TimeStamp
,DateRecordModified
)
select (
DocumentID
,DocumentTitle
,DocumentType
,DocumentAuthor
,DocumentAbstract
,DocumentKeyword
,DocumentImage
,DocumentPublicationDate
,SSMA_TimeStamp
,DateRecordModified
)
from   cepDC.SourceSchema.tbl_CEP_Documents

Thank you again for your help.

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Anthony, just saw your note -- so, something like:

SELECT *
INTO tnasca.dbo.TNASCADocuments
FROM cepDC.dbo.tbl_CEP_Documents
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Or rather:

USE cepDC
SELECT *
INTO tnasca.dbo.TNASCADocuments
FROM dbo.tbl_CEP_Documents

The schema seems to be dbo.

And I am not at risk of deleting any data by doing this, right? =)

Thank you again.

Eric
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 249 total points
Comment Utility
And I am not at risk of deleting any data by doing this, right? =)
It will error out if the table already exists.  So no there is no danger.

You need to ask yourself does the table already exist in the target database and do you want the schema (indexes, constraints, triggers, etc) to match exactly with the source?
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
>>>You need to ask yourself does the table already exist in the target database

No.

>>>>do you want the schema (indexes, constraints, triggers, etc) to match exactly with the source?

Yes!
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
anoospillai and Anthony, I really appreciate your help -- I am feeling some anxiety about this task. =) I am learning a lot.

Do you think this script will work?

USE cepDC
SELECT *
INTO tnasca.dbo.TNASCADocuments
FROM dbo.tbl_CEP_Documents

Or do I need to add other syntax to handle to schema?

Thank you again. I really appreciate your time.

Eric
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 249 total points
Comment Utility
So since it does not exist and you want to keep the same schema as the source database, I would follow the advice given by anoospillai.  In other words, first create the table in the target database and then use the INSERT statement to populate the data.

Let me know if you have any questions about those steps.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I'll give this a try. Here is my first attempt. I want to create table TNASCADocuments in database tnasca. So, how does this look?

          CREATE TABLE tnasca.dbo.TNASCADocuments
        (       
       DocumentID int IDENTITY(1,1) NOT NULL
      ,DocumentTitle             nvarchar(1024) NULL
      ,DocumentType                  nvarchar(1024) NULL
      ,DocumentAuthor            nvarchar(1024) NULL
      ,DocumentAbstract            nvarchar(2048) NULL
      ,DocumentKeyword             nvarchar(255) NULL
      ,DocumentImage            nvarchar(1024) NULL
      ,DocumentPublicationDate            datetime NULL
      ,DateCreated                   datetime NULL
      ,DateModified             datetime NULL
      )
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 249 total points
Comment Utility
That looks fine, however you are not including any indexes constraints, etc.  Does it not have any?
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Anthony -- I am not sure. Please pardon my ignorance. And I have a feeling I am about to learn some things. =) I do not think my table has indexes or constraints. How could I tell that? It is just a table in MS SQL Server that contains information inputted through a form in ColdFusion. I attach a screenshot:

database screenshot
How can I tell if I have these indexes, and so on? Thank you again.

Eric
0
 
LVL 4

Assisted Solution

by:Rodrigo Munera
Rodrigo Munera earned 126 total points
Comment Utility
Hi Eric, is there a particular reason you're not using the "import data..." feature in the SQL server manager?
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Rodrigo -- it did not occur to me.

But now that I think about it, I see how that would work. Good idea, and thank you. =)

My inexperience in these matters is apparent.

Having done this work so far, I would like to learn how to create this table, though. =)

Eric
0
 
LVL 4

Accepted Solution

by:
Rodrigo Munera earned 126 total points
Comment Utility
There's folders under the table, to see if there's an index, look under the indexes folder, any indexes will be stored in there.

SQL server management studio will create code for creating tables/indexes/pk/etc...

To do that, right-click on the table/index/pk/etc and select "script table/index/pk as > Create > new query editor window". You'll get a new window with the code.

So if you want to create a table in another database in the same server with the same identical fields, then go to the table you want to duplicate, right-click on the table, select "script table as > Create to > New Query editor window",

You'll get a new window that starts with the line USE [name_of_database] and then more code below.

All you have to do is change the name of the database in brackets to the target database.

So if your original table is in "database1" and you want to copy it to "database2", the first line in the code will be:

USE [database1]

if you change that line to:

USE [database2]

Then the "create" code written by the SQL server manager will be executed in the new database and will create the table there.

If the database already has a table with that name, you'll get an error saying that the table cannot be created because there's already a database with the same name already.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Very helpful. I am studying this and will try it out, and report back here later.

Thank you all, again. Hope your evening is going well.

Eric
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 62 total points
Comment Utility
Note that because of this DocumentID int IDENTITY(1,1) NOT NULL you won't be able to do a straight copy of the data from table to table.

If you aren't worried about the DocumentID changing then it is
USE cepDC

INSERT INTO tnasca.dbo.TNASCADocuments (DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentKeyword, DocumentImage, DocumentPublicationDate, DateCreated, DateModified)
SELECT DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentKeyword, DocumentImage, DocumentPublicationDate, DateCreated, DateModified
FROM dbo.tbl_CEP_Documents

Open in new window


If you want to keep the DocumentID changing then it is
USE cepDC

SET IDENTITY_INSERT tnasca.dbo.TNASCADocuments ON
INSERT INTO tnasca.dbo.TNASCADocuments (DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentKeyword, DocumentImage, DocumentPublicationDate, DateCreated, DateModified)
SELECT DocumentID, DocumentTitle, DocumentType, DocumentAuthor, DocumentAbstract, DocumentKeyword, DocumentImage, DocumentPublicationDate, DateCreated, DateModified
FROM dbo.tbl_CEP_Documents
SET IDENTITY_INSERT tnasca.dbo.TNASCADocuments OFF

Open in new window

0
 
LVL 3

Author Closing Comment

by:Eric Bourland
Comment Utility
Anoo, Anthony, Rodrigo, Jim -- thank you very much for your help. It's working now -- I have my new tables in the right database and they are tested and working. I learned a great deal from this discussion. Hope you all are having a great day. Take care.

Eric
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now