Increment Record Identify on Import 1

[dfrazell]

I'm writing a stored procedure to import customer contacts from an import table into a master customer contact table. I'm having difficulty figuring out how to increment the contact identifier (ContID) for each contact within a customer.

For example if the import table looks like this:

CustID  ContID  Contact
1000             Bill
1000             John

2000             Sue
2000             Mary
2000             Sara

Should look like this after import:

CustID  ContID  Contact
1000        1    Bill
1000        2    John

2000        1    Sue
2000        2    Mary
2000        3    Sara

To further complicate this, if CustID 1000 is already in the Master Customer Contact with x contacts then the resulting import should look like this:

CustID  ContID  Contact
1000     1 + x   Bill
1000     2 + x   John

2000         1   Sue
2000         2   Mary
2000         3   Sara

How can I accomplish this?

Thanks!

 

[gmmastros]

What version of SQL Server? 2000? 2005? (it matters).

Also... are you first importing in to a staging table? If you aren't, then you should.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dfrazell]

I'm on SQL 2000.

Yes - I've got a staging table setup.

 

[gmmastros]

SQL2005 has some nice windowing functions that you could use (if you were on SQL2005). This is still doable, but in a different way.

Before I show you some code, I suggest you add an identity column to your staging table. It's also important that the data be entered in to the staging table in the proper order. It should be ordered by CustId. If it's not, you will get very weird (and very bad) results.

In the code I show below, I first create 2 table variables. One allows me to simulate your staging table, and the other allows me to simulate your customer table. This allows me to easily test the query without mucking about with real tables. If you are satisfied with the queries, then you will need to modify them to use your actual tables.

[green]-- Setup test data in to table variables.[/green]
Declare @StagingTable Table(RowId Int Identity(1,1), CustId Int, ContId Int, Contact VarChar(20))

Insert Into @StagingTable Values(1000,NULL,'Bill')
Insert Into @StagingTable Values(1000,NULL,'John')
Insert Into @StagingTable Values(2000,NULL,'Sue')
Insert Into @StagingTable Values(2000,NULL,'Mary')
Insert Into @StagingTable Values(2000,NULL,'Sara')

Declare @Customers Table(CustId Int, ContId Int, Contact VarChar(20))
Insert Into @Customers Values(1000, 1, 'Greg')
Insert Into @Customers Values(1000, 2, 'Tom')

[green]-- Update the ContId to increment for
-- each CustId[/green]
Update StagingTable
Set    ContId = StagingTable.RowId - MinRows.MinRowId + 1
From   @StagingTable As StagingTable
       Inner Join (
         Select CustId, Min(RowId) As MinRowId
         From   @StagingTable
         Group By CustId
         ) As MinRows
         On StagingTable.CustId = MinRows.CustId

[green]-- Display the results[/green]
Select * From @StagingTable

[green]-- We still need to increment the values
-- when we find a matching CustId in the customer
-- table.[/green]
Update	StagingTable
Set     ContId = ContId + MaxContId
From    @StagingTable As StagingTable
        Inner Join (
          Select CustId, Max(ContId) As MaxContId
          From   @Customers C
          Group By CustId
          ) As MaxContIdAlias
          On StagingTable.CustId = MaxContIdAlias.CustId

[green]-- Display the results again.[/green]
Select * From @StagingTable

If there is any part of this that doesn't make sense, let me know and I will explain it for you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dfrazell]

George,

Thanks for the excellent and very detailed solution. I've installed it and have it working.

Thanks again!

Dave