copy data in SQL Server

While i try to copy data from one table to another table in sql server 2008 i am getting below error.

There is already an object named 'table123' in the database.

Just i am copying this table to another database.

i already created schema in another database with same table name.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team Lead, ex-Business Systems Analyst , ex-Senior Application EngineerCommented:
There is already an object named 'table123' in the database.
are you using Select ... Into statement for data copying?

try use a Insert into ... Select statement instead.

OR else you need to drop that table before running your existing statement.
VijayAuthor Commented:
I used select * into .
If i use select * into, it is creating table and copying data. But it not creating constraints.
Nitin SontakkeDeveloperCommented:
It will not. Furthermore, please be aware that the metadata tables, such as sys.tables, sys.columns are exclusively locked during the execution of such statement and as such this isn't quite a good practice in case of large table in production environment.

Better still, just create a script from original table (with constraint of, course), change table name in the script, execute the script and then import the data with the help of insert from
Vikas GargAssociate Principal EngineerCommented:

To move data from one table to another is to use T SQL like this.

Insert into DestinationTable
Select * From SourceTable

Open in new window

You can run this when both table has same structure. If different structure then use column name at both places.

When you try to run Select * into
Create table script called by default and since the table already there that is why the error is coming that the table already exists.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VijayAuthor Commented:
Thank you Vikas
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.