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


ADVERTISEMENT

How Do I Reference A Table In One Database From A SQL Script Running In Another Database?

Jan 5, 2007

I am construvcting a number of databases, some of which containsensitive data and most of which do not. I am attempting to handle thesecurity issues involved in protecting sensitive data in part byputting it in its own database. If the sensitive data is in a databasecalled d_SensitiveData, and in that database there is a table called't_A' (I know, not very informative, but this is only a triviallysimple example :-), and I have a script running in a database'NotSensitiveData' (i.e. there is a statement at the beginning of thescript "USE NotSensitiveData"), how do I referenece the primary key intable 'A'?I tried a variety of things like:ALTER TABLE t_nsXADD CONSTRAINT FK_PersonID FOREIGN KEY (p_idPerson)REFERENCES SensitiveData.t_A (p_idPerson);The above is, in fact, my latest attempt. Everything I tried hasfailed precisely at the point where I specify a table in a differentdatabase. So what is the trick to refering to a table in one databasewhen using another database?This will be used in a ASP.NET v3 application where one of the things Iwant to do is have the authentication provider be a different databasefrom the one used for the main application data.Does anyone know of an example I can download from the web that doesthe same sort of thing I want to try, with some discussion of securityissues involved (i.e. what I can do to harden the application and dataserver)?NB: I am an application developer, not a DBA nor a systemadministrator.ThanksTed

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

Transact SQL :: Database Constraint Check On Table With Reference Key

Jun 11, 2015

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.

View 7 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

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

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

Any Way To Parameterize A Database Reference??

Mar 13, 2004

I need to refer to a second database from within the database that I login to. Can any one help me with how to abstract this reference? I would like to be able to change a parameter somewhere (i don't really care where) and have it effect any Stored Porcedure which references the second database.

View 1 Replies View Related

How To Make Database For Reference

Jun 9, 2014

I am making a project where I have to add city, agentname, rankofagent, referenceagent name the problem is that who can I make a table where suppose I add member a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p like that

b is joined by a
c is joined by b
d by c, e by d and so on,

What I want here is that how can I get the result suppose i want to see that chain of p. I want result to be printed as a,b,c,d,e,f,g,h,i,j,k,l,m,n,o either result should be in horizontal form or vertical. with or without comma. I am doing this in SQL....

View 2 Replies View Related

How To Reference Items From Different Database?

Jul 20, 2005

Hi all,I have two databases DB_External and DB_Internal.I am writing some stored procedures (in DB_Internal) that reads fromthe tables in DB_External. I execute my stored procedures fromDB_Internal.Everytime I want to read the tables in DB_External, I have to refer tothe table as DB_External.dbo.tableName.Is there a better way of declaring the database DB_External up frontin the stored procedure so I don't have to pre-fix "DB_External.dbo."in all the table names?Thanks in advance,June Moore.

View 1 Replies View Related

ForeignKey Reference With Another Database

Apr 29, 2008



Hi All,

I have a table [Vendor] in first database SAMPLEDB. And i have another table [Contract] in another database TESTDB. I need a foreign key in the [Contract] table (which is in TESTDB database) is reference to the other table [Vendor] which is present in the SAMPLEDB.
When i try to creating them through SQL, i received the following error message :

Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key 'SAMPLEDB..VENDOR'.


Is that possible to refer foreign key in external database or not?
If possible, please give solution for this. If not possible, please suggest some alternate way.


Thanks in Advance

View 8 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

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

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 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

Invalid Object Name When Trying To Reference A Database

Mar 19, 2006

Hi There
Let me start by explaining what I am trying to achieve as there may be a better way to perform this. I have two databases and I want to refer to four tables - one in one database and three in the other in a sql select statement.
When I use the query builder in VWD I can only see tables for the database that is defined in the connection string - so therefore how do I add tables from another database? After reading a few posts I realized that all I have to do is use the syntax: database.owner.table (or [database].[owner].[table]). Unfortunately this didn't work and came up with the error "Invalid Object Name".
Assuming that I must have got the owner incorrect I used the Query Tool in SQL Server Express Edition and it worked perfectly! So my sql must be correct, but why will it not work inside VWD?
The two databases are named assessment and aspnetdb - the assessment database contains the table reviews and the aspnetdb database contains the tables TutorDetails, EmployerDetails and StudentDetails.
Here is the connection string and sql:
Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Assessment.mdf;Integrated Security=True;User Instance=True
SELECT Reviews.Programme, aspnetdb.dbo.TutorDetails.Tutor_DisplayName AS [Tutor Name],
aspnetdb.dbo.EmployerDetails.DisplayName AS [Employer Name], aspnetdb.dbo.StudentDetails.Student_DisplayName AS [Student Name]
FROM Reviews INNER JOIN
aspnetdb.dbo.StudentDetails ON Reviews.StudentID = aspnetdb.dbo.StudentDetails.Username INNER JOIN
aspnetdb.dbo.EmployerDetails ON Reviews.EmployerID = aspnetdb.dbo.EmployerDetails.UserName INNER JOIN
aspnetdb.dbo.TutorDetails ON Reviews.TutorID = aspnetdb.dbo.TutorDetails.UserName
WHERE (Reviews.ReviewID = 15)
Disregard the value of '15' in the WHERE statement as this was just put in to ensure I got some valid data back, this will be replaced with a variable eventually.
I know the sql statement works properly, I think the problem may be something to do with either the connection string - any ideas would be much appreciated.
Regards
Stuart

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 :: 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 :: 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

Trying To Reference Another Database In Update Command On Same SQL Server

Aug 15, 2006

I'm trying to update a table in MyDB1 with a value in MyDB2 on the same SQL server (2000)

     UPDATE MyDB1.dbo.Users
     SET MyDB1.dbo.Users.InstantASP_UserID = MyDB2.dbo.InstantASP_Users.UserID
     FROM MyDB2
     INNER JOIN dbo.aspnet_Membership a ON a.UserId = MyDB1.dbo.Users.UserID
     WHERE MyDB2.dbo.InstantASP_Users.EmailAddress = a.Email

I don't want to have to create a linked server.  It's on the same box.

I'm getting Invalid object name 'MyDB2'.

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







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