Need a unique audit id

Last Post 07 Apr 2008 07:15 PM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
c9jad
New Member
New Member

--
06 Apr 2008 06:48 AM
I am wondering if anyone knows the best way to generate a unique audit id for 20 tables that are going to be loaded in my SSIS package. I have a table that keeps track of all the previous audit ids that have been used. Do I need to run a Execute SQL task for each table to get the next available audit id. I was hoping to be able to have one thing that I could reuse for each of the tables.

Any help would be appreciated.
SQLUSA
New Member
New Member

--
07 Apr 2008 07:15 PM
You should set up a UNIQUEIDENTIFIER column in each table.

Here is sample - rowguid is the name of the column - default newid():

CREATE TABLE [HumanResources].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[ContactID] [int] NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[ManagerID] [int] NULL,
[Title] [nvarchar](50) NOT NULL,
[BirthDate] [datetime] NOT NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_Employee_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Employee_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]





Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC SSAS, SSIS, SSRS - April 21-24: http://www.sqlusa.com/


You are not authorized to post a reply.

Acceptable Use Policy