Task & Issue
- I had to load a big number of contact information records into a table.
- The contact records were all missing ContactNo, which is primary key. The source table has ContactNo column, but the field were not filled in at all. Thus, the P.K. field needs to be populated.
- I got a range of integer numbers that I can use as P.K for this set of contract records
(The number begins from 109578, and it should increment by 1)
- How can I assign the P.K. numbers(ContactNo) to the records and load them into the target table? (without using a cursor; doing a set-based operation)
- Identity( dataType, startNumber, incrementBy )
This function populates an integer(dataType) number for each records. The integer number begins with the startNumber and increments by incrementBy. This function allowed me to assign a unique contactNo to each record without using a cursor and looping.
- I just used a temp table instead of attempting to load the data directly into the target table. I am sure that someone can find more efficient way of accomplishing this kind of task.
Step 1
SELECT IDENTITY ( int, 109578 , 1 ) AS contactID, *
INTO #temp_Contact_Load
FROM Source_DB.dbo.CONTACT
Step 2
INSERT INTO Target_DB.dbo.CONTACT
( CONTACTNO
,FIRSTNAME
,LASTNAME
,EMAILADDRESS
,PHONE
...
)
SELECT
contactID -- select contactID field instead of ContactNo field from the Temp Table..
,FIRSTNAME
,LASTNAME
,EMAILADDRESS
,PHONE
...
FROM #temp_Contact_Load
-------------------------------------------
Reference : http://msdn.microsoft.com/en-us/library/aa933208%28v=sql.80%29.aspx
