asked on

SQL Server Express 2005 - how to move data from one database to another ?

I'm using SQL Server 2005 Express and looking for the best way to transfer data from one database to another. The two databases are the same exact format.

I'm thinking there's an insert query to run with the db name..table name for the query syntax but wanted to check with the experts here.

Thanks,
JMO9966

SQL_SERVER_DBA

It's a preference, you can use the import/export wizard or
use the insert method using the syntax you specified.

JMO9966

ASKER

I didn't see this available in SQL Server Express 2005 and I'm not sure of the exact syntax to take all records in a Customer table in one db to the "same" table in a different database ??

Thanks

SOLUTION

SQL_SERVER_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JMO9966

ASKER

Thanks, here's my attempt but I get an error:

if exists(select * from REandDv10.dbo.Customers)
Begin
delete table REandDv10.dbo.Customers
select * from REandD.dbo.customers into REandDv10.dbo.Customers
end

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'table'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'into'.

ptjcb

Are the servers linked?

JMO9966

ASKER

No, but the DB's are on the same server\instance.

Thanks

SQL_SERVER_DBA

if exists(select * from [REandDv10].dbo.Customers)
Begin
delete table [REandDv10].dbo.Customers
select * from REandD.dbo.customers into [REandDv10].dbo.Customers
end

David Todd

Hi,

Isn't the syntax you are looking for drop table REandDv10.dbo.Customers ?

Regards
David

JMO9966

ASKER

Thanks I tried drop table and now I'm down to one error ??

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'into'.

ASKER CERTIFIED SOLUTION

ptjcb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial