Need guidance on how to Insert a new record between existing records

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Need guidance on how to Insert a new record...

Need guidance on how to Insert a new record between existing records

Rate Topic

Display Mode

Topic Options

Author

Message

Nilssond

Posted Friday, March 08, 2013 3:09 PM

Say Hey Kid

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:35 PM
Points: 683, Visits: 334

Here is a the table structure:

MemberNbr Varchar(11)
MemberCardNumber Varchar(10)
EffectiveDate Int
TermDate Int

Sample data is:

Membernbr MemberCardNumber EffectiveDate TermDate
12345678909 A020129091 20120101 20120430
12345678909 A020129091 20120501 20120630
12345678909 A020129091 20120701 20120831
12345678909 A020129091 20120901 0

I receive an incoming file which indicates that this member updated his CardNumber to
B020129091 on 20120516 and I need to update the table to reflect this change in all his records
going forward from 20120516, overwriting, if necessary records which exist in the table and which
have effective dates > than 20120516 while also maintaining the TermDates.

Does anyone have some guidance on the SQL to do this?

Post #1428781

Lynn Pettis

Posted Friday, March 08, 2013 3:14 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 7:39 AM
Points: 21,620, Visits: 27,453

Would help if we know what the income record looked like for one. Also, if you could provide the DDL (CREATE TABLE) statement for the table, the sample data as a series of INSERT INTO statements, and what the expected results should look like when the code completes that would be helpful.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1428784

Sean Lange

Posted Friday, March 08, 2013 3:21 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 8,608, Visits: 8,252

This is nothing more than updating the table where date is greater than the date being passed in. The reason you are having a problem is because you have dates stored as an integer. You first have to painstakingly split that int into a usable datetime and this becomes trivial.

Update YourTable
set CardNumber = NewCardNumber
where MemberNbr = PassedInMemberNbr
and EffectiveDate >= PassedInDate

If there is ANY chance you can change this table to use the correct datatype for dates it will go a long way to making your life easier.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1428793

ScottPletcher

Posted Friday, March 08, 2013 5:19 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 1,324, Visits: 1,778

Hmm, seems to me that in this case the query is the same either way, with exactly the same WHERE clause structure.

The only difference is that the WHERE comparison value, the value compared to the column value, is also an int instead of a date.

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1428816

« Prev Topic | Next Topic »

Permissions