how to move table between databases


I have database1 , which contains two table ... table1 and table2..
I created database2 on same server and I want to move table2 in database2. how can I do that? Any script or DTS is the only way?
Please guide me.

LVL 21
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you can copy a table by getting the script generated for the structure, with which you create the table on the second database.
to copy the data:

INSERT INTO database2.dbo.table2
SELECT * FROM database1.dbo.table2

if you want to do a move, drop the table once you done the copy

in DTS, you can use the transfer objects scripts
MageDribbleConnect With a Mentor Commented:
would this statement work?  I've never tried this solution and don't have access to a SQL server right now.  My guess is it would work but the columns may not be the same datatype between the two tables.

SELECT * INTO database2.dbo.table2
FROM database1.dbo.table1
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes it would work, the data types will be copied
imran_fastConnect With a Mentor Commented:
hi  pinaldave,

you can use this

Select * into from Database1.dbo.table2
pinaldaveAuthor Commented:
Thank you all. I end up using DTS :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.