surrogate key generation - SQL Server Forums

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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

surrogate key generation

Author

Topic

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2007-12-05 : 01:34:53

Hi,
How to create surrogate key in a dimension table?
What transformations can be used to create it?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-05 : 07:56:57

Have you considered using an identity column? That is what they were designed for.

Be One with the Optimizer
TG

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2007-12-05 : 08:08:55

How to set identity column?
Will it work for incremental load?
Should i use any variable to increment its value?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-05 : 08:18:20

When a table is being created or when a column is being added to an existing table, you can define it as identity:
myNewIdentityCol int identity(1,1) --see books online

When rows are inserted to the table, Sql Server maintains and increments the value. Here is a quick example:


create table #myTable (rowid int identity(1,1), myValue int)

insert #myTable (myValue) values (100)
insert #myTable (myValue) values (100)
insert #myTable (myValue) values (100)

select * from #myTable

drop table #myTable

output:
rowid       myValue     
----------- ----------- 
1           100
2           100
3           100

What are you using to "load" your data?

Be One with the Optimizer
TG

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2007-12-05 : 08:26:14

SSIS packages are used to load data.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-05 : 08:37:51

There are several topics already posted about your question. Here is one:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92270&SearchTerms=identity,ssis

As you can see, I searched for "identity ssis"
See if any of these discussions help solve your problem.

A quick google search turned up this. I have no idea if it will help but at least it is some more infor for you:
http://sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx
"Surrogate key generation in SSIS"

Be One with the Optimizer
TG

varalakshmi
Yak Posting Veteran

98 Posts

Posted - 2007-12-06 : 05:03:43

Thanks TG for ur help.
But still working on it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources