How to go from test to production

Question

Post reply

How to go from test to production

Cory McRae

Say Hey Kid

Points: 664
More actions
April 26, 2004 at 4:26 pm

#111061

I have an access data base I would like to move some tables to SQL. I have imported the tables and have tweaked them so they are just the way they need to be in my test SQL data base. (They have extended proprieties and referential integrity all set up). What is the easiest way to update the tables with the latest information and move them to the production data base? I have fixed some data problems in the tables so I would rather not truncate that table and repopulate the whole thing. I just need the last two months worth of records added. I have tried doing an insert select statement but get an error that says it violates primary key restraints. I have looked through the data and did not see any duplicates in the primary key. Here is the code I have so far. I have set up the access table as a view. I am changing the name of one column and getting rid of another column.

Use database

delete (good_SQL_table)

where SchdlStartDate > '2/1/2004'

go

insert (good_SQL_table)

SELECT   [BlockID]
        , [ID#]
        , [Spec]
        , [PrintOrder]
        , [SchdlStartDate]
        , [PrintDate]
        , [PaperAR1]
        , [PaperAR2]
        , [Node_Ln_Adhsv_Batch]
        , [Node_Ln_Adhsv_Batch2]
        , [BkScrn]
        , [BlScrn]
        , [InitialWt]
        , [PrintScrap]
        , [SheetLength]
        , [SheetCount]
        , [HeatPress]
        , [HeatPressDate]
        , [DogEarPress]
        , [DogEarDate]
        , [StretchDate]
        , [StretchW]
        , [StretchL]
        , [CellCntAvg]
        , [ResinAR]
        , [BlockDensity]
        , [TrimDate]
        , [TrimW]
        , [TrimL]
        , [InitialWtwithFrame]
        , [PreStageOven]
        , [PostCureDate]
        , [PostCureOven]
        , [Closed]
        , [Split]
        , [maa_field]
        , notes as [Customer]

FROM dbo.[Core Block Tracking & Travelers___Blocks]

where SchdlStartDate > '2/1/2004'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 1

Cory

It's quite difficult with a lot of data to look through and find duplicate records. Have you tried a query of the form

SELECT PRIMARY_KEY

FROM TABLE

GROUP BY PRIMARY_KEY

HAVING COUNT(*) > 1

to check you definetly have no duplicates.

I assume that the table you are importing from has no null values in the field or fields you are using for the primary key?

I know that you've deleted the records from the table you are inserting into with the same range of start dates you wish to insert. But to make sure there no duplicates why don't you write a simple select query using an inner join on the primary key between the two tables. If there are any records which will be duplicated they will be returned.

Dave

Cory McRae Say Hey Kid Points: 664 More actions · Answer 2

Thanks David

The inner join did it there was a record with a null start date that was not being deleted. I feel kind of dumb now but it is fixed.

Is detaching and reattaching some where else the best way to copy a data base with extended properties? Or should I use the data base copy wizard? And suggestions would be helpful first time rolling out a new database. I’ve just done tables and tings before.

Cory Lee McRae