migrating one database to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

migrating one database to another

Author

Topic

shelbyoh
Starting Member

14 Posts

Posted - 2008-09-29 : 14:26:21

I tryed ssis just because my database is not normalized it does,t work. so I wrote a program. but have problems. The soft where creates an intermediate database to migrate an Access Database to with allow all null values. Then I have to migrate from the intermediate database to my real database because it does not allow null values it crashes. So I wrote so it will ignore null values. So cool that works now I am having problems because my real database have example( contact table) ( 25 column names) intermediate database (contact table) (10 column names) because the table are different in the number columns names I getting errors where the columns don't exist. I would like to have my code do something to the effect of

if objectcolumen exist() update else goto next, or skip,
is this possible and if so can you give me example.

I have a store procedure in the procedure I would like for it to skip over a column if it don't exist in the first database when merging or migrated to the new database.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-29 : 14:28:04

SSIS works fine on denormalized databases.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shelbyoh
Starting Member

14 Posts

Posted - 2008-09-29 : 14:40:15

Thanks Tara, yet I'm using sql server express 2005 It doesn't work well with ssis I have tried creating packages and had to download patches for ssis to work with the problems I was having with null values so i wrote an application in c# that works fine except this last part. Also this application is going to be use by technician out in the field the will not have access to ssis. I need this last part and application is finish.

This my stored procedure I trying to have it if column title if exist in update
else goto to Next column
the same for all I just need and example

CREATE PROCEDURE [dbo].[ContactsInserttmp]
(
@EId int,
@Title nvarchar(10),
@LastName nvarchar(30),
@FirstName nvarchar(25),
@Address1 nvarchar(35),
@Address2 nvarchar(35),
@City nvarchar(25),
@State nvarchar(25),
@ZIP nvarchar(25),
@Country nvarchar(25),
@PhoneHome nvarchar(25),
@PhoneWork nvarchar(25),
@PhoneFax nvarchar(25),
@BusinessName nvarchar(30),
@GuestNote ntext,
@HKNote ntext,
@Email nvarchar(75),
@EmailPermission bit,
@MailPermission bit,
@Category int,
@Flag1 bit,
@Flag2 bit,
@Flag3 bit,
@Flag4 bit,
@Flag5 bit,
@Flag6 bit,
@OccasionDate1 datetime,
@OccasionDate2 datetime,
@OccasionDate3 datetime,
@OccasionDate4 datetime,
@Occasion1 int,
@Occasion2 int,
@Occasion3 int,
@Occasion4 int,
@Interest1 int,
@Interest2 int,
@Interest3 int,
@Interest4 int,
@IATA int,
@AgentCommission float,
@Active bit,
@deleted bit
)
AS
SET NOCOUNT OFF;

Declare @CId int
Declare @Venue int

Execute dbo.ContactsVenueNewId
@NewId = @CId OUTPUT,
@Venue = @Venue OUTPUT

/* Put the INSERT and hotlsp_InvoiceHistoryInsert in a transaction so we
* log the proper row version */
INSERT INTO dbo.Contact(CId, EId, Title, LastName, FirstName, Address1, Address2, City, State, ZIP, Country, PhoneHome, PhoneWork, PhoneFax, BusinessName, GuestNote, HKNote, Email, EmailPermission, MailPermission, Category, Flag1, Flag2, Flag3, Flag4, Flag5, Flag6, OccasionDate1, OccasionDate2, OccasionDate3, OccasionDate4, Occasion1, Occasion2, Occasion3, Occasion4, Interest1, Interest2, Interest3, Interest4,
IATA, AgentCommission, Active, CreatedOn, Servervenue, deleted)
VALUES (@CId, @EId, @Title, @LastName, @FirstName, @Address1, @Address2, @City, @State, @ZIP, @Country, @PhoneHome, @PhoneWork, @PhoneFax, @BusinessName, @GuestNote, @HKNote, @Email, @EmailPermission, @MailPermission, @Category, @Flag1, @Flag2, @Flag3, @Flag4, @Flag5, @Flag6, @OccasionDate1, @OccasionDate2, @OccasionDate3, @OccasionDate4, @Occasion1, @Occasion2, @Occasion3, @Occasion4, @Interest1, @Interest2, @Interest3, @Interest4,
@IATA, @AgentCommission, @Active, GetDate(), @Venue, @deleted);
SELECT CId, EId, Title, LastName, FirstName, Address1, Address2, City, State, ZIP, Country, PhoneHome, PhoneWork, PhoneFax, BusinessName, GuestNote, HKNote, Email, EmailPermission, MailPermission, Category, Flag1, Flag2, Flag3, Flag4, Flag5, Flag6, OccasionDate1, OccasionDate2, OccasionDate3, OccasionDate4, Occasion1, Occasion2, Occasion3, Occasion4, Interest1, Interest2, Interest3, Interest4,
IATA, AgentCommission, Active, ts, deleted
FROM dbo.Contact
WHERE (CId = @CId)

/* Store this to audit trail */
EXEC dbo.ContactHistoryInsert @CId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources