CODEWITHSUNDEEP
sqlimportsql-server-2008-r2
mahfuz01
mahfuz01

Reputation: 443

import table and stored procedures

I am trying to export the tables [around 40] and stored procedures [around 120+] in SQL Server 2008 R2 from dev server to prod server.

I have created a .sql file [right clicking on the database in SSMS, choosing Tasks -> Generate Scripts], but when I am trying to import the table and stored procedures into the prod server [right clicking on the database in SSMS, New Query then copying the content in] it is giving me a long list of errors

Mostly

There is already an object named 'tblMyTable' in the database
Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.tblMyTable'

Any idea what I am doing wrong or what should be done? Thanks in advance.

Upvotes: 0

Views: 658

Answers (2)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

if you are importing the whole database to production you might as well do a restore with replace to the production database.

120 SPs and 20 tables seemed to be the whole database. so Restore with replace should be done.

Upvotes: 0

JStead
JStead

Reputation: 1730

The problem with your current technique is that assumes your target is an empty database. So it will reinsert everything with no attempt at merging data and this is what causes your duplicate primary keys. If you use Management Studio you have to do all the merging of data yourself.

My recommendation is first to look into redgate it's not free but all the time you will save it will be worth it. You will need to use both SQL Compare and Data Compare ( http://www.red-gate.com/products/sql-development/sql-data-compare/ ).

Another alternative is to use Visual Studio 2010 premium if you have it( http://msdn.microsoft.com/en-us/library/aa833435.aspx and http://msdn.microsoft.com/en-us/library/dd193261.aspx). This gives both a data compare and a schema compare option. It is not as good as redgate but I found it works most of the time.

If you are looking for free alternatives check out this stack post https://stackoverflow.com/questions/377388/are-there-any-free-alternatives-to-red-gates-tools-like-sql-compare.

Upvotes: 3

Related Questions