Find Table Reference Levels

Oct 3, 2006

The script below can be used to determine the reference levels of all tables in a database in order to be able to create a script to load tables in the correct order to prevent Foreign Key violations.

This script returns 3 result sets. The first shows the tables in order by level and table name. The second shows tables and tables that reference it in order by table and referencing table. The third shows tables and tables it references in order by table and referenced table.

Tables at level 0 have no related tables, except self-references. Tables at level 1 reference no other table, but are referenced by other tables. Tables at levels 2 and above are tables which reference lower level tables and may be referenced by higher levels. Tables with a level of NULL may indicate a circular reference (example: TableA references TableB and TableB references TableA).

Tables at levels 0 and 1 can be loaded first without FK violations, and then the tables at higher levels can be loaded in order by level from lower to higher to prevent FK violations. All tables at the same level can be loaded at the same time without FK violations.

Tested on SQL 2000 only. Please post any errors found.

Edit 2006/10/10:
Fixed bug with tables that have multiple references, and moved tables that have only self-references to level 1 from level 0.


-- Start of Script - Find_Table_Reference_Levels.sql
/*
Find Table Reference Levels

This script finds table references and ranks them by level in order
to be able to load tables with FK references in the correct order.
Tables can then be loaded one level at a time from lower to higher.
This script also shows all the relationships for each table
by tables it references and by tables that reference it.

Level 0 is tables which have no FK relationships.

Level 1 is tables which reference no other tables, except
themselves, and are only referenced by higher level tables
or themselves.

Levels 2 and above are tables which reference lower levels
and may be referenced by higher levels or themselves.

*/

declare @r table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))

declare @rs table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))

declare @t table (
REF_LEVEL int,
TABLE_NAME nvarchar(200) not null primary key clustered )

declare @table table (
TABLE_NAME nvarchar(200) not null primary key clustered )
set nocount off

print 'Load tables for database '+db_name()

insert into @table
select
TABLE_NAME = a.TABLE_SCHEMA+'.'+a.TABLE_NAME
from
INFORMATION_SCHEMA.TABLES a
where
a.TABLE_TYPE = 'BASE TABLE'and
a.TABLE_SCHEMA+'.'+a.TABLE_NAME <> 'dbo.dtproperties'
order by
1

print 'Load PK/FK references'
insert into @r
selectdistinct
PK_TABLE =
b.TABLE_SCHEMA+'.'+b.TABLE_NAME,
FK_TABLE =
c.TABLE_SCHEMA+'.'+c.TABLE_NAME
from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on
a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
on
a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and
a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
order by
1,2

print 'Make copy of PK/FK references'
insert into @rs
select
*
from
@r
order by
1,2

print 'Load un-referenced tables as level 0'
insert into @t
select
REF_LEVEL = 0,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
(
select PK_TABLE from @r union all
select FK_TABLE from @r
)
order by
1

-- select * from @r
print 'Remove self references'
delete from @r
where
PK_TABLE = FK_TABLE

declare @level int
set @level = 0

while @level < 100
begin
set @level = @level + 1

print 'Delete lower level references'
delete from @r
where
PK_TABLE in
( select TABLE_NAME from @t )
or
FK_TABLE in
( select TABLE_NAME from @t )

print 'Load level '+convert(varchar(20),@level)+' tables'

insert into @t
select
REF_LEVEL =@level,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
( select FK_TABLE from @r )
and
a.TABLE_NAME not in
( select TABLE_NAME from @t )
order by
1

if not exists (select * from @r )
begin
print 'Done loading table levels'
print ''
break
end

end


print 'Count of Tables by level'
print ''

select
REF_LEVEL,
TABLE_COUNT = count(*)
from
@t
group by
REF_LEVEL
order by
REF_LEVEL

print 'Tables in order by level and table name'
print 'Note: Null REF_LEVEL nay indicate possible circular reference'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
order by
b.REF_LEVEL,
a.TABLE_NAME

print 'Tables and Referencing Tables'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
REFERENCING_TABLE =convert(varchar(40),c.FK_TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.PK_TABLE
order by
a.TABLE_NAME,
c.FK_TABLE


print 'Tables and Tables Referenced'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
TABLE_REFERENCED =convert(varchar(40),c.PK_TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.FK_TABLE
order by
a.TABLE_NAME,
c.PK_TABLE


-- End of Script



Results from Northwind database:

Load tables for database Northwind

(13 row(s) affected)

Load PK/FK references

(13 row(s) affected)

Make copy of PK/FK references

(13 row(s) affected)

Load un-referenced tables as level 0

(0 row(s) affected)

Remove self references

(1 row(s) affected)

Delete lower level references

(0 row(s) affected)

Load level 1 tables

(7 row(s) affected)

Delete lower level references

(9 row(s) affected)

Load level 2 tables

(4 row(s) affected)

Delete lower level references

(3 row(s) affected)

Load level 3 tables

(2 row(s) affected)

Done loading table levels

Count of Tables by level

REF_LEVEL TABLE_COUNT
----------- -----------
1 7
2 4
3 2

(3 row(s) affected)

Tables in order by level and table name
Note: Null REF_LEVEL nay indicate possible circular reference

REF_LEVEL TABLE_NAME
----------- ----------------------------------------
1 dbo.Categories
1 dbo.CustomerDemographics
1 dbo.Customers
1 dbo.Employees
1 dbo.Region
1 dbo.Shippers
1 dbo.Suppliers
2 dbo.CustomerCustomerDemo
2 dbo.Orders
2 dbo.Products
2 dbo.Territories
3 dbo.EmployeeTerritories
3 dbo.Order Details

(13 row(s) affected)

Tables and Referencing Tables

REF_LEVEL TABLE_NAME REFERENCING_TABLE
----------- ---------------------------------------- ----------------------------------------
1 dbo.Categories dbo.Products
2 dbo.CustomerCustomerDemo NULL
1 dbo.CustomerDemographics dbo.CustomerCustomerDemo
1 dbo.Customers dbo.CustomerCustomerDemo
1 dbo.Customers dbo.Orders
1 dbo.Employees dbo.Employees
1 dbo.Employees dbo.EmployeeTerritories
1 dbo.Employees dbo.Orders
3 dbo.EmployeeTerritories NULL
3 dbo.Order Details NULL
2 dbo.Orders dbo.Order Details
2 dbo.Products dbo.Order Details
1 dbo.Region dbo.Territories
1 dbo.Shippers dbo.Orders
1 dbo.Suppliers dbo.Products
2 dbo.Territories dbo.EmployeeTerritories

(16 row(s) affected)

Tables and Tables Referenced

REF_LEVEL TABLE_NAME TABLE_REFERENCED
----------- ---------------------------------------- ----------------------------------------
1 dbo.Categories NULL
2 dbo.CustomerCustomerDemo dbo.CustomerDemographics
2 dbo.CustomerCustomerDemo dbo.Customers
1 dbo.CustomerDemographics NULL
1 dbo.Customers NULL
1 dbo.Employees dbo.Employees
3 dbo.EmployeeTerritories dbo.Employees
3 dbo.EmployeeTerritories dbo.Territories
3 dbo.Order Details dbo.Orders
3 dbo.Order Details dbo.Products
2 dbo.Orders dbo.Customers
2 dbo.Orders dbo.Employees
2 dbo.Orders dbo.Shippers
2 dbo.Products dbo.Categories
2 dbo.Products dbo.Suppliers
1 dbo.Region NULL
1 dbo.Shippers NULL
1 dbo.Suppliers NULL
2 dbo.Territories dbo.Region

(19 row(s) affected)







CODO ERGO SUM

View 20 Replies


ADVERTISEMENT

Issue With MDX Query Builder In Producing Results For Measures That Reference To Levels In Dimension

Feb 20, 2008

Folks,
I have an issue in producing output for the following query in MDX Query builder avaialable in BIDS. the error message I get is:
The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)




Code Snippet
WITH MEMBER [Measures].[Annualized Vs Target] AS
'[Measures].[Revenue]-[Measures].[Target Revenue]'
SELECT
NON EMPTY {
([Measures].[Balance],[Year].[Year - Year].&[2006]) ,
([Measures].[Balance], [Year].[Year - Year].&[2007]),
([Measures].[Revenue], [Year].[Year - Year].&[2006]),
([Measures].[Target Revenue], [Year].[Year - Year].&[2007]),
([Measures].[Revenue], [Year].[Year - Year].&[2007]),
([Measures].[Annualized Vs Target], [Year].[Year - Year].&[2007])
}
ON COLUMNS ,
NON EMPTY { ([Client - Coverage Region].[Region Name].[Region Name].ALLMEMBERS *
[Client].[Client Name].[Client Name].ALLMEMBERS)} ON ROWS
FROM [MIS POC FINAL]





Do Anybody has a solution for this? I require the output to be in the following format :

Region_Name(RegDim) Client_Name(ClientDim) Balance_2006 Balance_2007 Revenue_2007 TargetRevenue_2007
USA A 20000 30000000 40000000 5000000000

B 60000 70000000 80000000 9000000000

Note: We can execute the above query in SSMS-MDX Query window without any issues.

Many Thanks in Advance for any assistance.
Subhash Subramanyam, Suraj Magdum

View 6 Replies View Related

Transact SQL :: Find Circular Reference In The Table

Jun 1, 2015

Create table #tblActvity
(
activityIDvarchar (50),
activityParentIDvarchar(50)
)
Insert into #tblActvity
SElect '1',Null

[Code] ...

--If I pass activityId 3 or 2 or 4 it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....

I need a sql qry which will require to find a circular reference in it.....

As in above sample of data ,If I pass activityId 3 or 2 or 4 to qry it should return 0 as none of the activity is circular but If I pass 5, 6  or 7 it should return 1 as they have circular reference....

View 4 Replies View Related

Transact SQL :: Find All Stored Procedures That Reference Oracle Table Name Within Server OPENQUERY Statement

Aug 10, 2015

One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?

View 2 Replies View Related

Get The Names For Different Levels In A Table

Aug 20, 2007

hi

I've a table with coln names

ID
Name
ParentID
Level


I've list with different levels

say

ex.

the Data is:-

ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4



How to write the query for getting the Names for different levels for corresponding ParentID....

Output should be like:-
Leaf Branch Trunk Root
Stem Branch Trunk Root

View 1 Replies View Related

Find The Coumns In The Reference

May 8, 2006

Given a table A, I need to find all the tables that are in PK-FK with A and the columns in the reference. I can get the tables that have the FK relation through sysreferences or sysconstraints or sysforeignkeys but I have not been able to find out how to identify the specific column that is in the relation. Any one has any idea?

View 2 Replies View Related

T-SQL (SS2K8) :: Different Levels To Make Table Flat

May 6, 2015

I am trying to fill my table in different levels (hierarchy). The different levels are not properly processed. All layers now have the same value. It is intended that the layers are beaten flat. This implies multiple joins in order to be able to get the different levels.

'Ledgertableinterval' could be used to determine the hierarchy, but I did not come all the way out ...

So my suggestion is to fill with 1 character level 1, level 2 with 2 character and so on. If a level is not available anymore than filling up with higher level. For this you can use the replace function.

The DIM that I use;

USE [NJ]
GO

/****** Object: Table [DIM].[FA] Script Date: 05/06/2015 09:57:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [DIM].[FA](

[Code] .....

View 1 Replies View Related

How To Find Which Stored Procs And UDFs Reference A Column

Jun 12, 2006

How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.

Thanks.

View 14 Replies View Related

DB Design :: Table Partitioning Using Reference Table Data Column

Oct 7, 2015

I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster? 

I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)

View 3 Replies View Related

Multiple Columns In Table That Reference 1 Lookup Table

May 4, 2006

Hello,I have a query that I need help with.there are two tables...Product- ProductId- Property1- Property2- Property3PropertyType- PropertyTypeId- PropertyTypeThere many columns in (Product) that reverence 1 lookup table (PropertyType)In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeIdHow do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3ProductId  |  Property1  |  Property2  |  Property3  | PropertyType1  | PropertyType2  |  PropertyType3 PropertyType(1) = PropertyType for Property1PropertyType(2) = PropertyType for Property2PropertyType(3) = PropertyType for Property3I hope this makes sence.Thanks in advance.

View 3 Replies View Related

Problem: Find All Table Names In Db And Then Find

Jun 12, 2008

I have 3 database say
db1
db2
db3

I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)

View 5 Replies View Related

SQL 2012 :: DTSX Giving Errors - Object Reference Not Set To Instance Reference

Sep 10, 2014

I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:

Error system.NullReferenceException: Object refrence not set to an instance reference.

In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine

[code]...

is there something which i did not install or what can be the error?

View 0 Replies View Related

UDT - Get Reference To Table

Jan 12, 2007

Hello everyone !

Given a UDT, is there any way to get a reference to the table where the specific instance is running ?

IE: Let's suppose we have defined a UDT named UDTPoint; now we define two tables: ATable & BTable, wich both have one column that is defined as UDTPoint.

When an insert/update/delete operation on ATable or BTable occurs, the UDTPoint class needs to verify in which context it is running (ATable or BTable) before doing operations on data.

Is there any way to achieve that ?

Thanks



Giovanni

View 1 Replies View Related

Best Way To Reference A Table With A Composite Key?

Nov 24, 2006

The table above is my users table. It allows for a user to be at multiple sites or multiple locations within a single site or multiple sites. Would it be wise to use a auto incrementing primary key instead of the 3 column composite key? The reason I ask is because if I am referencing this SU table (which I will be a lot), a lot more data would be replicated to the tables which have the foreign key to this table, right? But if I used a single incrementing column as the primary key, only a small integer would be used as the foreign key, saving space?Does this make sense?   

View 1 Replies View Related

How To Reference A Table In A Different Database?

Apr 5, 2006

I have 2 databases in sql server. let us say database1 = db1 and database2 = db2
Now both the databases have a same table called table1 with the same fields.

IF data in db1.table1 is updated then data in db2.table2 should be updated automatically.

There are many ways we can do this. one way is to create a INSERT trigger on db1.table1.

But i would like to avoid trigger
Is there something in SQL server where I can just link table1 of db1 to db2 and delete the table1 in db2. That means db2 is using the same table that of db1.

Thanks

View 2 Replies View Related

Command To Reference Another Table

Mar 27, 2006

Hi all,

I am new to this site and I hope anyone out there can help me. I was tasked to change the constraints of my existing table. Lets call it table1. This table has an attribute that needs to take the value of another attribute of another table ( let's call it tables2) and that attribute must satisfy a certain expression ( I suppose I can isolate it by using the select statement ).

Anyone know how to get this done.

Please advice.

Thanks and appreciated.

View 4 Replies View Related

Table Reference To Another DB Instance?

Apr 13, 2008

Hello everyone,

this would be my first posting.

I would like to know if it's possible in MS SQL to redirect a table into another DB instance? I have no access into the source code and I have been wondering if it is possible to make a redirection/hard link in MS SQL side.

I would have a table in my base DB and when a query is made into this table, the MS SQL would redirect it to another DB or table. Is this possible in MS SQL ? Something along the lines of a hard-link in linux.
Thank you very much.

View 3 Replies View Related

Using The 'Like' Statement To Reference Another Table

Nov 4, 2007

Hi

Is it possible to to create a 'Like' statement that refernces all the data in a column from another table?
Thanks

View 1 Replies View Related

Cross Reference Or Pivot Table

Aug 23, 2007

Hi All,
The problem is about cross reference.
1. I have a third party cross reference store procedure SimpleXTab
CREATE       PROCEDURE [dbo].[SimpleXTab2] @XField varChar(50), @XTable varChar(100),@XWhereString varChar(250), @XFunction varChar(10), @XFunctionField varChar(50), @XRow varchar(300),@ResultTable varchar(100) ASDeclare @SqlStr nvarchar(4000)Declare @tempsql nvarchar(4000)Declare @SqlStrCur nvarchar(4000)Declare @col nvarchar(100)
set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + ']  ' + @XWhereString + ' Group By [' + @XField + ']'
/* select @sqlstrcur */exec sp_executesql @sqlstrcur
 
 declare xcursor Cursor  for  Select * from ##temptbl_Cursor
 open xcursor
 Fetch next from  xcursor  into @Col  
While @@Fetch_Status = 0Begin  set @Sqlstr = @Sqlstr + ", "  set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' + @XField + " = '" +@Col +                           "' then [" + @XFunctionField + "] Else 0 End) As [" +  @Col + "]" ,'')  set @Sqlstr = @tempsql  Fetch next from xcursor into @Col End
 /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */
 set @tempsql = 'Select  '  + @XRow + ', ' + @Sqlstr + 'into ' +@ResultTable+' From ' + @XTable +                             @XWhereString +  ' Group by ' + @XRowprint @tempsql set @Sqlstr = @tempsql
 Close xcursor Deallocate xcursor 
  set @tempsql = N'Drop Table ##temptbl_Cursor'  exec sp_executesql @tempsqlprint @tempsql /*  Select @Sqlstr as [mk], len(@sqlstr) as [leng] */print @sqlstr   exec sp_executesql @Sqlstr
if @@rowcount = 0  select 'No Records found'GO
2. I've use this store procedure for many cross reference successfully. But this time my cross reference value (resultcode) is a varchar which cannot be convert to int or decimal in sql, Probably, you've noticed that the fourth parameter is a function.    how can i modify SimpleXtab to avoid using math function but still can generate cross reference.
   exec simplextab2 'Sequence','##tbltempreport',' ','sum','resultcode','Parameter' ,'dbo.resultcodetable'
 
Many Thanks!
 
 

View 2 Replies View Related

SQL: Working With Multiple Reference Table

Dec 12, 2005

I need to write a query that requires respective fields referencing from multiple tables.
For example, here are the tables:
Main Table:
InfoID  Team1 Player1 Team1 
Table: Player_ref
Player   Team_Player_ref  Player1  John doh 
Table: Team_ref
Team   Team_Player_ref  Team1  My Team
 
Ideal result Table from query:
InfoID     Count John Doh     1 My Team      2
 
Any suggestion to creat the Ideal Results table from query?
Normally, I could do it if it only referenced from 1 table, I would do an inner join, however, since there are 2 referenece table, doing inner join wouldn't work. A proposed suggestion would certainly be nice. Thanks in advance.
--daydreamstuck at the current problem

View 6 Replies View Related

How Do You Reference A Table In Another (SQL) Database In TSQL

Jan 9, 2001

Good afternoon one and all,

I am trying to write a query that will use tables in more than one SQL database (on the same server). How do refer to the table? I have tried dbasename.tablename.fieldname but I don't think that's working.

TIA for any and all help

Gurmi

View 3 Replies View Related

Can't Reference Logical Table Within Cursor

Aug 3, 2004

Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this...

DECLARE check_contact_fields CURSOR
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
set @SQL = 'select ' + @column_name + ' into ##DeletedData from deleted'
exec sp_executesql @SQL
set @SQL = 'select ' + @column_name + ' into ##InsertedData from inserted'
exec sp_executesql @SQL
if (select * from ##DeletedData) <> (select * from ##InsertedData)
select * from ##InsertedData
FETCH NEXT FROM check_contact_fields INTO @field_id, @column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

drop table ##DeletedData
drop table ##InsertedData


Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'deleted'.


TIA

View 14 Replies View Related

T-SQL Create Self-reference In Depending Table

Jul 23, 2005

Hello all,I have two tables - Projects and ProjectStructTable Projects contains master records of the projects, ProjectStructallows to define a project herarchie and contains the fieldsPrjStructId, ProjectId, PrjStructName, ..., ParentIdPrjStructParent contains a reference to the parent or to itselves ifrecord is top-level-record for a project.I try to create a trigger on table Projects (INSERT) whichautomatically creates the top-level-entry in ProjectStruct but Ididn't succed.Tried to use (several variations similar to)INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))FROM INSERTEDbut this inserts a reference to the last inserted record. Why thishappens is pretty clear to me, but I found no way to get the referenceto the identity column of the record currently inserted.Is there a way to do this?

View 7 Replies View Related

Reference Table Fields Via A Subscript

Nov 6, 2007

I need a table of seasonally adjusted forecasts. Each row contains 52 weekly values to been named as needed (e.g. Week01, Week02, etc.). At any point in the year, I want to be able to determine how many weeks I can cover with the product on hand.

I have not been able to envision a set based, normalized solution given there are nearly a million items to be evaluated daily. If I normalize, I'm faced with 52 million rows. It seems to me I'm better off bringing a wide row containing all 52 week buckets into memory and working with them in a stored procedure. And if that's the best solution, how do I reference these "virtual" buckets? I'm thinking of some kind of While() loop that counts the weeks until the inventory is exhaused but I need a subscript based technique to reference each week.

Any thoughts?

View 5 Replies View Related

Doing A Lookup On A Reference Table From Script?

Jun 28, 2006

Hi,

I have a common requirement (when I'm processing data rows from an input file) to perform some data manipulation in script then look up a value from a reference table and perform some further data manipulation depending on whether a matching value was found in the lookup table or not.

For example, say I'm processing Customer data rows and the first "word" (/token) of the FullName column might be a title or the title could be missing and it might be a forename or initial instead. I want to check the first word of this FullName column to see if it matches any valid title values in a ReferenceTitles lookup table. If I find a match I want to set my Title column to the value from the ReferenceTitles lookup table, otherwise I want to set it to, say, an empty string. Then I want to process the rest of the FullName column tokens differently depending on whether or not a match was found.

It seems very messy to start coding a script transformation and then
have to use a lookup transformation combined with a script
transformation on the error output followed by a union and a sort
and finally a further script transformation (especially as I would like to be able to use variables from the first script in the later processing)...



So what I'm wondering is: Is there an easy/clean way to perform a database lookup (using cached values) from a script so that I can achieve all the above from within a single script component?



Thanks in advance,

Lawrie

View 8 Replies View Related

Table Variables/Table Reference - Is This Possible?

Feb 28, 2005

Is it possible to do something like this in SQL:

DECLARE @TABLE table

if @GOOD = 1 BEGIN
Set @Table = Table1
END ELSE BEGIN
Set @Table = OtherTable
END

SELECT * FROM @TABLE

View 1 Replies View Related

Need To Delete A Table Which Has A Reference To Another Table

Dec 12, 2007

Hi Friends...

In general we cannot delete a table if it refers another table. But in the process of backup and restore, we need to delete all the tables, so we have return an asp.net function...

Dim Datatable As String
Dim ds As New DataSet
ds = gDatabase.ExecuteQuery(" select Table_Name from Information_schema.Tables where Table_type='TABLE'")
If ds.Tables.Count > 0 Then
If ds.Tables(0).Rows.Count > 0 Then
Dim dr As DataRow
Dim da As SqlCeDataAdapter
Dim Cmd As String
For Each dr In ds.Tables(0).Rows
Datatable = dr.Item("Table_name")
Cmd = "Delete from [" & Datatable & "]"
gDatabase.ExecuteNonQuery(Cmd)
Next
End If
End If

we got an error like

"Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Electricalworks' because it is being referenced by a FOREIGN KEY constraint.
"

how to override these errors, if we have to delete all the tables. We have about 200 tables.

View 7 Replies View Related

Reference A Flat File (as A Table) In A Sql Statemtent

Apr 5, 2004

Several years ago, I am sure that I had a query that either read from or wrote to a text file. There was some way to:
select * into 'c:\out.txt' from SomeTable
Does anyone know if this functionality exists? If so, what is the syntax?

TIA

View 8 Replies View Related

Transact SQL :: Replace A String Using Reference Table

Jul 21, 2015

I would like to create a function that will replace a string using a reference table

I have a table : reference

ID String ReplaceWith

1 ≈ &
2 < <
3 > >

If I pass a string into a function,  function needs to replace a string with replace with string column in reference table

For example, if I pass    a string : car $ap; fjld

The function should return car & fjld

How can i create a function like this so that i can call it in Stored procedure....

View 12 Replies View Related

Can I Add A Foriegn Key On A Indexed View To Reference Other Table

Nov 8, 2006

Is there anybody familiar with indexed view here?

Can I add a foriegn key on a indexed view to reference other table?

Thanks!

View 1 Replies View Related

Cascading Delete And Finding Table Reference Level

Feb 16, 2008

This function will generate all DELETE statements in correct order to perform a CASCADING delete.
For self-joined tables, it will generate the T-SQL code to "unwind" the table, also in correct order!CREATE FUNCTION dbo.fnCascadingDelete
(
@Schema NVARCHAR(128) = NULL,
@Table NVARCHAR(128) = NULL
)
RETURNS@Return TABLE
(
RowID INT PRIMARY KEY CLUSTERED,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL,
[SQL] NVARCHAR(4000) NOT NULL
)
AS
BEGIN
DECLARE@Constraints TABLE
(
RowID INT NOT NULL,
Indent SMALLINT NOT NULL,
[Catalog] NVARCHAR(128) NOT NULL,
[Schema] NVARCHAR(128) NOT NULL,
[Table] NVARCHAR(128) NOT NULL,
[Column] NVARCHAR(128),
pkCatalog NVARCHAR(128),
pkSchema NVARCHAR(128),
pkTable NVARCHAR(128),
pkColumn NVARCHAR(128),
pkType NVARCHAR(128),
pkSize INT,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL
)

INSERT@Constraints
(
RowID,
Indent,
[Catalog],
[Schema],
[Table],
[Column],
pkCatalog,
pkSchema,
pkTable,
pkColumn,
pkType,
pkSize,
IsSelfJoin,
HasPk
)
SELECTRowID,
Indent,
[Catalog],
[Schema],
[Table],
[Column],
pkCatalog,
pkSchema,
pkTable,
pkColumn,
pkType,
pkSize,
SelfJoin,
CASE
WHEN [Column] IS NULL THEN 0
ELSE 1
END
FROMdbo.fnTableTree(@Schema, @Table)

IF @@ROWCOUNT = 0
RETURN

DECLARE@SQL TABLE
(
ID INT IDENTITY(1, 1),
RowID INT PRIMARY KEY CLUSTERED,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL,
[SQL] NVARCHAR(4000) NOT NULL
)

DECLARE@Indent SMALLINT,
@RowID INT,
@ID INT,
@TSQL NVARCHAR(4000),
@RowSQL NVARCHAR(4000),
@EndSQL NVARCHAR(4000),
@pkColumn NVARCHAR(128),
@IsSelfJoin TINYINT,
@HasPk TINYINT

DECLARE@Unwind TABLE
(
RowID INT NOT NULL,
StepID INT IDENTITY(0, 1) PRIMARY KEY NONCLUSTERED,
[SQL] NVARCHAR(4000)
)

WHILE NOT EXISTS (SELECT * FROM @SQL WHERE RowID = 1)
BEGIN
SELECT TOP 1@RowID = c.RowID,
@ID = c.RowID,
@Indent = c.Indent,
@TSQL = N'',
@EndSQL = N'',
@IsSelfJoin = c.IsSelfjoin,
@HasPk = c.HasPk
FROM@Constraints AS c
LEFT JOIN@SQL AS s ON s.RowID = c.RowID
WHEREs.RowID IS NULL
ORDER BYc.Indent DESC,
c.RowID DESC

WHILE @ID > 0
BEGIN
IF @Indent = 0
SELECT@RowSQL = N'DELETE t' + CAST(@RowID AS NVARCHAR(12)),
@RowSQL = @RowSQL + N' FROM ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]) + N' AS t' + CAST(@ID AS NVARCHAR(12)),
@EndSQL = N' WHERE t' + CAST(@ID AS NVARCHAR(12)) + '.' + QUOTENAME(COALESCE(c.[Column], '%0')) + N' = ''%1''',
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID = @ID
ELSE
SELECT@RowSQL = N' INNER JOIN ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]),
@RowSQL = @RowSQL + N' AS t' + CAST(@ID AS NVARCHAR(12)) + N' ON t' + CAST(@ID AS NVARCHAR(12)) + N'.' + QUOTENAME(c.[Column]),
@pkColumn = QUOTENAME(c.pkColumn),
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID = @ID

SELECT TOP 1@ID = c.RowID,
@Indent = c.Indent,
@RowSQL = @RowSQL + N' = t' + CAST(c.RowID AS NVARCHAR(12)) + N'.' + @pkColumn,
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID < @ID
AND c.Indent < @Indent
ORDER BYc.Indent DESC,
c.RowID DESC

IF @@ROWCOUNT = 0
SET@ID = 0

SET@TSQL = @RowSQL + @TSQL
END

INSERT@SQL
(
RowID,
IsSelfJoin,
HasPk,
[SQL]
)
VALUES(
@RowID,
@IsSelfJoin,
@HasPk,
@TSQL + @EndSQL
)

IF @IsSelfJoin = 1
BEGIN
DECLARE@Yak NVARCHAR(160),
@Catalog NVARCHAR(128),
@Column NVARCHAR(128)

SELECT@Yak = pkType + COALESCE('(' + CAST(pkSize AS NVARCHAR(12)) + ')', ''),
@Catalog = [Catalog],
@Schema = [Schema],
@Table = [Table],
@Column = [Column],
@Catalog = [Catalog],
@Table = [Table],
@pkColumn = pkColumn
FROM@Constraints
WHERERowID = @RowID

SET@RowSQL = 'DECLARE@Lvl INT
SET@Lvl = 0
DECLARE@Stage TABLE (RowID INT IDENTITY(0, 1), Lvl INT, RowKey ' + @Yak + ')
INSERT @Stage (Lvl, RowKey) '
+ REPLACE(@TSQL + @EndSQL, 'DELETE t' + CAST(@RowID AS NVARCHAR(12)) + '', 'SELECT 0, t' + CAST(@RowID AS NVARCHAR(12)) + '.' + QUOTENAME(@Column) + '')
+ ' WHILE @@ROWCOUNT > 0
BEGIN
SET@Lvl = @Lvl + 1

INSERT@Stage (Lvl, RowKey)
SELECT@Lvl,
t.' + QUOTENAME(@pkColumn) + '
FROM' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' AS t
INNER JOIN@Stage AS s ON s.RowKey = t.' + QUOTENAME(@Column) + '
AND s.Lvl = @Lvl - 1
LEFT JOIN@Stage AS cr ON cr.RowKey = t.' + QUOTENAME(@pkColumn) + '
WHEREcr.RowKey IS NULL
END
SELECT ''DELETE FROM ' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' WHERE ' + QUOTENAME(@pkColumn) + ' = '' + QUOTENAME(RowKey, '''''''')
FROM @Stage
WHERE RowID > 0
ORDER BY RowID DESC'

INSERT@Unwind
(
RowID,
[SQL]
)
VALUES(
@RowID,
@RowSQL
)
END
END

INSERT@Return
(
RowID,
IsSelfJoin,
HasPk,
[SQL]
)
SELECTs.ID,
s.IsSelfJoin,
s.HasPk,
CASE
WHEN u.RowID IS NULL THEN s.[SQL]
ELSE u.[SQL]
END
FROM@SQL AS s
LEFT JOIN@Unwind AS u ON u.RowID = s.RowID
ORDER BYs.ID,
u.StepID

RETURN
ENDE 12°55'05.25"
N 56°04'39.16"

View 16 Replies View Related

Lookup Finds Match On Empty Reference Table

Jul 6, 2006

Hi all,

In BOL it says: "The Lookup transformation performs an equi-join between values in the transformation input and values in the reference dataset. Using an equi-join means that each row in the transformation input must match at least one row from the reference dataset. If there is no matching entry in the reference dataset, no join occurs and no values are returned from the reference dataset. This is an error, and the transformation fails, unless it is configured to ignore errors or redirect error rows to the error output. "

I have a lookup transformation which is supposed to find a match on two fields in the reference dataset (a table in my case) but strangely, when I execute my package and the reference table is empty the lookup still finds match for each row of my input dataset.

Does anyone have an idea why? I could'nt find anything about that in BOL.

Sébastien.

View 4 Replies View Related

Application Role How Query A View Whose Reference Table Is In Another Db?

Aug 23, 2007

Hi everybody.
I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1
I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).
With the following query
SELECT USER_NAME()

I see that the approle is being used.
Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:


The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.
What should I do to make it work?

The table in DB2 has the same schema of the view in DB1 which refers to it.
I put the DB1 TrustWorthy and both the database have the db_chaining option activated.

Any idea on how to solve the problem would be widely appreciated.
Thank you very much.
Vania

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved