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 Administration
 Timelining a SQL server Table

Author  Topic 

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-03-23 : 11:01:54
We are on SQL server 2005 and have currently a table CustInfo to store customer information.This table is loaded every month with fresh data after deletion of the existing data.The Primary key in this table is CustID.

We were asked to timeline the CustInfo Table such that whenever there is a change in the customer information it has to insert a enddate to the existing customer and then recreate another record for the same customer.

This can be accomplished if there is no primarykey on the CustInfo Table but having the primary key is not letting us create duplicates in the table

I would like to know how we can achieve this or any other alternative ways to accomplish this


Note:This table is used by users on a website to search for customers based on the CustId

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-23 : 13:03:14
Well you'll have to define how this "new" customer will get added to the table. Show us some sample data and what you want it to look like after you recreate the same customer.

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

Subscribe to my blog
Go to Top of Page

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-03-25 : 10:35:47
I would like to know general suggestions from SQL Geeks to have better ideas

Iam still trying to test for a better solution.Currently I have created a test CustInfo table in my local database without any primary keys in it and using Slow changing dimension taks in SSIS to load data from a dummy table to the CustInfo table.

The SCD(Slow changing dimension) task has the ability to update data or insert new records for updated data and will assign an end date to the previous data

This is all workingonly if there are no primary keys in the table.I will post the DDL of our existing table to make it more sense to the geeks
Go to Top of Page
   

- Advertisement -