[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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
0
JMO9966
Asked:
JMO9966
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
SQL_SERVER_DBACommented:
It's a preference, you can use the import/export wizard or
use the insert method using the syntax you specified.
0
 
JMO9966Author Commented:
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
0
 
SQL_SERVER_DBACommented:
well, make sure the servers are linked...if they are on same server then do this...
if exists(select * from db2.dbo.Customers)
Begin
delete table db2.dbo.Customers
select * from db1.dbo.customers into db2.dbo.Customers
end


otherwise preceed with the server name
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JMO9966Author Commented:
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'.
0
 
ptjcbCommented:
Are the servers linked?
0
 
JMO9966Author Commented:
No, but the DB's are on the same server\instance.

Thanks
0
 
SQL_SERVER_DBACommented:
if exists(select * from [REandDv10].dbo.Customers)
Begin
delete table [REandDv10].dbo.Customers
select * from REandD.dbo.customers into [REandDv10].dbo.Customers
end
0
 
David ToddSenior DBACommented:
Hi,

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

Regards
  David
0
 
JMO9966Author Commented:
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'.
0
 
ptjcbCommented:
select * from REandD.dbo.customers into [REandDv10].dbo.Customers

should be

select * into [REandDv10].dbo.Customers from REandD.dbo.customers
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now