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
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?)
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 RelatedHow do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
I am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin
declare @liter as decimal(28,6)
[Code] ....
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.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
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
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 RelatedI 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
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.
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.
Hi
Is it possible to to create a 'Like' statement that refernces all the data in a column from another table?
Thanks
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!
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
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
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
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
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 RelatedI 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?
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
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
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....
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....
Is there anybody familiar with indexed view here?
Can I add a foriegn key on a indexed view to reference other table?
Thanks!
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"
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.
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
CREATE TABLE PRODUCT_SALES
(
Salesmanid BIGINT,
Productid BIGINT
)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,1)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,3)
SELECT * FROM PRODUCT_SALES
/* SalesmanID is reference key from Sales Master and ProductID is reference key from Product Master. How should i restrict user through DB with any constraint check, if user tries to enter
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2),
It should throw error , if possible user defined message would be more useful.
I am trying to reference a package level variable in a script component (in the Code) and am unable to do so successfully. I have it listed as a ReadOnlyVariables in the custom properties of the script component, however unable to reference it in the code.
Any help will be appreciated.
Thanks,
Andy.
I'm trying to create a local copy of a popular CRM database calledSalesforce.com. Many of the tables in the DB have FOREIGN KEYreferences that I want to preserve, but I've run into a chicken and eggproblem. Table "A" has a reference to table "B," and table "B" has areference to table "A." So I can't CREATE one until the other exists.Is there a way to disable these checks until I've created all theschema?Here's what I see (error first, then SQL that caused it):Server: Msg 1767, Level 16, State 1, Line 1Foreign key 'FK__UserRole__LastMo__48CFD27E' references invalid table'User'.Server: Msg 1750, Level 16, State 1, Line 1Could not create constraint. See previous errors.CREATE TABLE salesforce3.dbo."UserRole" ("Id" varchar(18) PRIMARY KEY ,"Name" varchar(40), "ParentRoleId" varchar(18) REFERENCES"UserRole"(Id), "RollupDescription" varchar(80),"OpportunityAccessForAccountOwner" varchar(40),"CaseAccessForAccountOwner" varchar(40), "LastModifiedDate" datetime,"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"datetime);CREATE TABLE salesforce3.dbo."User" ("Id" varchar(18) PRIMARY KEY ,"Username" varchar(80), "LastName" varchar(80), "FirstName"varchar(40), "CompanyName" varchar(80), "Division" varchar(80),"Department" varchar(80), "Title" varchar(80), "Street" text, "City"varchar(40), "State" varchar(20), "PostalCode" varchar(20), "Country"varchar(40), "Email" varchar(80), "Phone" varchar(40), "Fax"varchar(40), "MobilePhone" varchar(40), "Alias" varchar(8), "IsActive"bit, "TimeZoneSidKey" varchar(40), "UserRoleId" varchar(18) REFERENCES"UserRole"(Id), "LocaleSidKey" varchar(40), "ReceivesInfoEmails" bit,"ReceivesAdminInfoEmails" bit, "EmailEncodingKey" varchar(40),"ProfileId" varchar(18) REFERENCES "Profile"(Id), "LanguageLocaleKey"varchar(40), "EmployeeNumber" varchar(20), "WirelessEmail" varchar(80),"LastLoginDate" datetime, "CreatedDate" datetime, "CreatedById"varchar(18) REFERENCES "User"(Id), "LastModifiedDate" datetime,"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"datetime, "UserPermissionsMarketingUser" bit,"UserPermissionsOfflineUser" bit, "UserPermissionsWirelessUser" bit,"UserPermissionsSuperCssUser" bit, "UserPermissionsAvantgoUser" bit);
View 2 Replies View RelatedI am not sure if this is necessarily a simple question, but I'm somewhat new to SQL so I thought maybe there's an obvious answer I just don't know about.
The problem is that I have one "master" table, and a child table that has two foreign key references back to that master table. Both of these foreign key constraints are marked as "on delete cascade" with the intention that should a row from the master table be deleted, any rows that reference that object in EITHER foreign key field should be deleted.
I am wondering why this causes a cycle. It seems logical enough to me, it just involves two passes of the table, one for each affected column.
Thanks,
Logan