Solved

how to copy data rows from one database to another

Posted on 2011-03-08
5
514 Views
Last Modified: 2012-05-11
Hi !

I have a Database Db1 in SQL Server 2005. I am creating a new Database Db2 on the same machine in same SQL Server instance. I have 5 columns and more than 100 rows in table1 of Db1 and I want to copy 3 columns and all rows of Table1 and put it in Table2, Db2.

What could be the easiest way to do that ? Thanks.
0
Comment
Question by:pratz09
5 Comments
 
LVL 13

Assisted Solution

by:agarwalrahul
agarwalrahul earned 300 total points
ID: 35068240
Write the Query:

if table not exists in DB2:

Select Column1,Column2, Column3 into db2.tablename from db1.tablename

if Table Exists in DB2:

Insert into db2.tablename Select column1, Column2,column3 from db1.tablename
0
 
LVL 3

Expert Comment

by:zulumike
ID: 35068258
I'm no sql expert but i believe this is one way to do it:
If Db2 and table 2 is an empty new table, you can just copy entire table/db and simply delete the coums you don`t want via sql server management studio.
0
 
LVL 6

Accepted Solution

by:
jonaska earned 200 total points
ID: 35068330
Agree with agarwalrahul. Altough you should use the tre part syntax.
db.tablename will not work.
db.dbo.tablename or db..tablename works better.
Example:
INSERT INTO desDb..destTable  (ID, Text, Comment)
SELECT ID, Text, Comment FROM sorceDb..sourceTable

Open in new window

0
 
LVL 13

Expert Comment

by:agarwalrahul
ID: 35068358
Insert into db2.tablename (column1, Column2,column3)  Select column1, Column2,column3 from db1.tablename
0
 

Author Closing Comment

by:pratz09
ID: 35068378
Thanks guys,

db.dbo.tablename works better... since it is cross Database query, without dbo, it throws object not recognized error for the second database.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Unable to save view in SSMS 21 69
email about the whoisactive result 7 35
how to install/upgrade the Blitz responder kit 8 41
Please help with the below query - SQL Server 11 16
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

813 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

10 Experts available now in Live!

Get 1:1 Help Now