Solved

How to copy a table from one database into another?

Posted on 2014-03-09
18
358 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
ID: 39916007
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
ID: 39916024
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
ID: 39916029
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Author Comment

by:Eric Bourland
ID: 39916034
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
ID: 39916080
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
ID: 39916144
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
ID: 39916147
>>>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
ID: 39916215
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
ID: 39916238
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
 
LVL 3

Author Comment

by:Eric Bourland
ID: 39916286
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
ID: 39916303
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
ID: 39916344
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
ID: 39916347
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
ID: 39916354
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
ID: 39916508
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
ID: 39916509
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
ID: 39916572
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
ID: 39918791
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

770 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