Generate Reference Tables Primary Keys

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

Transact-SQL (2005)

Generate Reference Tables Primary Keys

Author

Topic

novreis
Starting Member

5 Posts

Posted - 2008-11-03 : 16:11:56

Hi, I have tables with historical data. I want generate rows in my empty references tables. The foreign keys are defined so I want loop historical data tables and for each of them generate the primary keys on reference tables ( avoiding duplicates ).
invoice 10234
Payment Terms ( FK )- 30
Payment Type ( FK ) - ch

invoice 10235
Payment Terms ( FK )- 60
Payment Type ( FK ) - tr

My 2 references tables must have after loop:

Pter Ref Table
PK
30
60

Ptyp Ref Table
ch
tr

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-03 : 16:25:17

How is it possible to have foreign keys defined with no entries in the referenced tables?
Maybe I'm not understandig...

But this is maybe what you want:


insert [Pter Ref Table](
PK_Column
)
select distinct [Payment Terms] from YourSourceTable

insert [Ptyp Ref Table](
PK_Column
)
select distinct [Payment Type] from YourSourceTable

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

novreis
Starting Member

5 Posts

Posted - 2008-11-03 : 17:29:53

Hi webfred,
Well, I had the data before I have indexes, constraints, and so on. I ran the scripts to create indexes and constraints( I am migrating one Oracle DB to Sql Server ) and I have several errors. I assume that on the cases where tables have data the foreign keys were not enabled but somewhere are registered on a System Table. Your solution is good for one table but I have a lot of tables. My idea was a Loop that reads the System Table with the tables relationships and that answer to these questions 1) table has foreign keys? 2 ) if yes, has data? if yes, select distinct ... insert in the reference table 4) go next table and so on. My english is not good but i hope you understand.
Thanks anyway

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-03 : 22:22:13

SELECT Table_Name as [Table], Column_Name as [Column],
Constraint_Name as [Constraint], Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ORDER BY [Table], [Column]

novreis
Starting Member

5 Posts

Posted - 2008-11-04 : 07:56:32

Sodeep, your solution is good but does not give the reference table. Meanwhile on a further research I found a solution in the link
http://microsoft.apress.com/asptodayarchive/72995/using-sql-server-system-tables-metadata

select sysobjects.Name as TableName,
syscolumns.Name as ColumnName,
syscolumns.isNullable,
syscolumns.Length,
systypes.Name as Type,
OBJECT_NAME(sysforeignkeys.rkeyid) as FKTable
FROM syscolumns, sysobjects, systypes, sysforeignkeys
WHERE syscolumns.ID = sysobjects.ID
AND syscolumns.xusertype = systypes.xusertype
and syscolumns.id = sysforeignkeys.fkeyid
and syscolumns.colid = sysforeignkeys.fkey
and sysobjects.status >= 0
and sysobjects.type = 'U'

ORDER BY sysobjects.Name, syscolumns.Name

Thanks anyway

novreis
Starting Member

5 Posts

Posted - 2008-11-04 : 08:47:18

Ok, the most dificult part is done, but how I make the Loop ( in italic )

select sysobjects.Name as TableName,
syscolumns.Name as ColumnName,
syscolumns.isNullable,
syscolumns.Length,
systypes.Name as Type,
OBJECT_NAME(sysforeignkeys.rkeyid) as FKTable
FROM syscolumns, sysobjects, systypes, sysforeignkeys
WHERE syscolumns.ID = sysobjects.ID
AND syscolumns.xusertype = systypes.xusertype
and syscolumns.id = sysforeignkeys.fkeyid
and syscolumns.colid = sysforeignkeys.fkey
and sysobjects.status >= 0
and sysobjects.type = 'U'

WHILE "TableName" IS NOT NULL
BEGIN
insert into FKTable("ColumnName")
select distinct ["ColumnName"] from "TableName"

END

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-11-04 : 09:19:13

another way to get foreign key relationships

SELECT OBJECT_NAME(PARENT_OBJECT_ID) TABLE_NAME,
PT.NAME FIELD_NAME,
OBJECT_NAME(REFERENCED_OBJECT_ID) REFTABLE_NAME,
FT.NAME REFFIELD_NAME
FROM SYS.FOREIGN_KEY_COLUMNS FKC
JOIN SYS.COLUMNS PT
ON FKC.PARENT_OBJECT_ID = PT.OBJECT_ID
AND FKC.PARENT_COLUMN_ID = PT.COLUMN_ID
JOIN SYS.COLUMNS FT
ON FKC.REFERENCED_OBJECT_ID = FT.OBJECT_ID
AND FKC.REFERENCED_COLUMN_ID = FT.COLUMN_ID

since its just a one time thing. you can load output of this query to a cursor, then start reading this cursor and create a dynamic SQL statement to insert missing values in the table which is supposed to have the primary keys.

novreis
Starting Member

5 Posts

Posted - 2008-11-04 : 14:28:27

rohitkumar, this is the most appropriate for me but now I must go to the Sql Server Basic Forum because I am not an expert in Sql Server ( I tried to use the Cursor but I was not able to do that even with google search ). thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources