I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code FROM dbo.ADSCHL AD INNER JOIN TESTCEP FD ON AD.SCHOOL_NAME = FD.School_Name
For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table. -------Employees table-------- EmployeeID . . . . . . . . . . int Name . . . . . . . . . . . nvarchar(50) ManagerID . . . . . . . . . . . int
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager. How can that be achieved?
I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.
Here is a basic breakdown of what I am trying to do:
Table1 (the table where the rows need to be deleted from) Column_x (Holds the id that is unique to the various rows of data - User ID)
Table2 (Holds the user information & has the associated ID) Column_z (holds the User ID)
I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.
Code:
SELECT Column_z FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z <> Column_x
This did not seem to do what I needed, it did not delete any rows at all.
I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2
Then I tried another scenerio that I also needed to do:
Code:
SELECT Column_z, Column_a FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z = Column_x AND Column_a='0'
'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.
Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.
CREATE TABLE Folder ( iD int NOT NULL IDENTITY (1, 1) PRIMARY KEY, folderName varchar(50) NOT NULL, parentFolderID int NULL FOREIGN KEY REFERENCES Folder (iD) ) GO
if I add an ON DELETE CASCADE to the foreign key, then i get an error... which is annoying. If a folder is deleted, then all its sub-folders should also be automatically deleted.
The error is: 'Introducing FOREIGN KEY constraint 'FK__Folder__parentFo__7D78A4E7' on table 'Folder' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'
Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong...
Using T-sql!!!
update import set import.gone = from import inner join stat ON stat.id = import.id CASE WHEN stat.A = import.field2 THEN import.gone = sec.A WHEN stat.B = import.field2 THEN import.gone = sec.B WHEN stat.C = import.field2 THEN import.gone = sec.C WHEN stat.D = import.field2 THEN import.gone = sec.D WHEN stat.E = import.field2 THEN import.gone = sec.E WHEN stat.F = import.field2 THEN import.gone = sec.F ELSE import.gone = null END
I have a table that holds a ParentID and the RecordID. There is a column called IsEnabled which is a bit field indicating if a folder can be displayed or not. 0 = NO, 1 = YESThe table is for a directory structure which is virtual and displays folders on a web page.Root----- 1---------- 1-1---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4---------- 1- 3---------- 1- 4I need a query that will not select any children that are under a Parent that is disabled. So if ' 1- 2 ' is disabled then:---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4SHOULD NOT SHOW.Can anyone give me a query that will overcome this problem i have.-J
I have an Employee table that has EmployeeID (PK) SupervisorID (which is really just another EmployeeID) ..random junk...
Now that part makes sense, everyone gets one and only one boss.
Their boss can change, and therefore the SupervisorID would be updated.
Now I have an EmployeeEvals table that has quarterly evaluation data.
I want to relate these two tables.
Eval table has EvalID (PK) ReviewedEmployeeID (the one being evaluated) SupervisorID (the one doing the evaluation)
Now I need to link this back to the employee table (at least I think I do).
So I would want to relate it by the ReviewedEmployeeID going back to EmployeeID in the employee table and I also want the SupervisorID to do the same...
But of course that won't work because that would seem to indicate that a single record on the Employees table (say EmployeeID 55) should have a matching (or could) record in the Eval table that would look like EvalID: 12345 ReviewedEmployeeID: 55 SupervisorID: 55
which of course wouldn't happen as an employee wouldn't evaluate themself.
How do I handle the relationships for this properly?
Do I just not link the SupervisorID back to anything?
I've recently finished an application for a small company with perhaps two hundred employees. Each employee was set up in a Users table in the database, against which application logins were processed.
For just about every other table in the database, other than pure lookup tables, we created columns to indicate the user who created the entry, and the user who last modified the entry. This was done using FK references back to the Users table. Each table contains two references back to the Users table, and there are over 150 tables now that follow this scheme. At first I was not concerned, other than the fact that it makes a visual picture of the data model look very confusing (almost every table has a pair of links back to the Users table), until I encountered an issue where I could no longer delete from the Users table. Upon surpassing 253 FK references to Users, I can no longer delete users, as the Query Optimizer can't complete the query.
Now, all of that so far is really not a big deal. Deleting users was never my intent anyway. The only real question I have is whether this is the standard way of maintaining history for table records. Have others used this method? Is there a better way?
ID INT PK Name VARCHAR RefID FK ID Name RefID 1 Test1 <NULL> 2 Test1a 1 3 Test1b 1 4 Test1b1 3 5 Test1b1a 4
CREATE TRIGGER Update_ID ON TableA INSTEAD OF UPDATE AS IF UPDATE(ID) BEGIN DECLARE @old_ID INT DECLARE @new_ID INT
SELECT @old_ID = ID FROM DELETED SELECT @new_ID = ID FROM INSERTED
UPDATE TableA SET ID = @new_ID WHERE ID = @old_ID
UPDATE TableA SET RefID = @new_ID WHERE RefID = @old_ID END
SPROC: UPDATE TableA SET ID=6 WHERE ID=2
RefID is the FK to ID ID is also the PK to another table and has the relationship use cascading Delete and Update.
Problem: "The UPDATE statement conflicted with the SAME TABLE REFERENCE constraint" This is referring to the first UPDATE of the Trigger. It was my understanding that Instead Of checks the constraints after it is all done yet it errs with the first Trigger UPDATE. I was expecting to overcome the self-reference constraint issue by using Instead Of and with the first Trigger UPDATE, change the ID(PK) and then change the RefID(FK) with the second Trigger UPDATE. Once that was done, it should have not had any constraint problems. Thoughts?
We are in the conversion process of making the database ints.This is a change from a guid PK to an integer based PK that uses an int Identity. The program still uses the guid, and we are trying to map that guid to the databases int.We insert using TVPs passed from code. Since the identity is being set upon insert I have three things to accomplish:
1) Insert all the data into the dbo table 2) Update the parent Id in the table 3) Pass the SetsId guid, Sets_Id int, ParentSets_Id int back to the program
This is a high transaction table that will have a lot of records (millions).
--Sample table creation. There is a FK between Sets_Id to ParentSets_Id, Clustered PK on the Sets_Id IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JSets]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[JSets]( [SetsID] [uniqueidentifier] NOT NULL, [Sets_Id] bigint Identity (1,1) NOT NULL,
Hi everybody, I know that it is possible for one column to reference two different parent tables by defining two foreign keys on a same column, I have done it and SQL Server does give any error. But I want to know that is it advisable to define multiple foreign key on a column referncing two differnt parent table's primary key in differnt case... ? Means If Case 1 then It should reference to column1 or table1 otherwise it should reference to table2? How is it practicle...?
An example of what I am talking about is the employee table in the Adventureworks database. This has employeeID and then ManagerID, ManagerID just being the EmployeeID of the person whom the original reports to.
I know the queries for querying this type of data and even making recursive common table expressions. What I cannot seem to find is how one goes about BUILDING said table. I see all sorts of examples where people are just doing INSERT table VALUES () manually to load the table. The problem is, I need to create a table that has potentially thousands of records.
It will essentially be a dimensional map. Don't even get me started as to they why, I will just suffice to say that is what the client and project want . I have a process that will do this now, but it is not very dynamic and very hard coded. To me, there seems like there should be some sort of standardized methodology for handling this.
I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.
declare @THeader TABLE ( TransactionHeaderID [int] NOT NULL , PatientID [int] NOT NULL , TransactionAllocationAmount [money] NOT NULL , ApplyAmt [money] NULL ) - create table variable
insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable
UPDATE @THeader set TransactionAllocationAmount = (SELECT isnull(Sum(mtblTransactionAllocation.Amount),0) FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable
Update @THeader set ApplyAmt = (SELECT mtblTransactionAllocation.Amount FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable
- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt
UPDATE mtblTransactionHeader SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount, mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt FROM @THeader, mtblTransactionHeader WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table
I'm creating a new Integration Services Project that copies data out of a SQL 7 server, transforms it, and places the data on a SQL 2005 (SP 2) Server. When defining a lookup transformation, if I specify an OLE DB Connection to my server running SQL 7 as the reference table, as soon as I click on the Colums tab, Visual Studio closes / crashes and dumps me to windows. I don't get an error message. If however I specify a connection to a server running SQL 8, or SQL 2005, no problems.
Is this supposed to happen?
My workstation is running Windows XP Pro SP2, Visual Studio 2005 Pro.
Microsoft SQL Server Integration Services Designer Version 9.00.1399.00
The server that doesn't work for a reference table is running Windows 2000 Server SP4 SQL 7.00.623
I am looking for a Sql query to verify the inserted values from one table(in CSV file) with another table(in sql database)
For example: I have below Values column that is present in once CSV file, after my data migration the values get stored in Results table under Message column.
I need to very whether values(1X,1Y) are inserted in Message record "successfully inserted value 1X"
Values (CSV) 1X 1Y
Results Table(SQL) CreatedDate Message 2015-08-04 08:45:29.203 successfully inserted value 1X 2015-08-04 08:44:29.103 TEst pass 2015-08-04 08:43:29.103 successfully inserted value 1X 2015-08-04 08:42:29.203 test point 2015-08-04 08:35:29.203 successfully inserted value 1Y 2015-08-04 08:30:29.203 Test Pass 2015-08-04 08:28:29.203 successfully inserted value 1Y
If all values are inserted:
Output: All values from values table are inserted successfully Total count of values inserted: 2 If only few values are inserted, example only 1X from Values table is inserted in Message
Example: Results Table CreatedDate Message 2015-08-04 08:45:29.203 successfully inserted value 1X 2015-08-04 08:44:29.103 TEst pass 2015-08-04 08:43:29.103 successfully inserted value 1X 2015-08-04 08:42:29.203 test point
Output: All values from values are not inserted successfully in result table. Total count of values inserted: 1 Missing Values not inserted in results table are: 1Y
convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent Type_1 is 2nd Parent Type_2 is 3rd Parent Type_3 is 4ed Parent
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:- GO ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details] @Class_Id int AS BEGIN
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).
Table1 columns are: table1Id; Name. Table2 columns include Table2.table1Id which is the foreign key to Table1.
What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
I have a table in different databases with the same name. My goal is to compare the two tables, and insert a record into the second table where there is not a match in the first table. So far, my query looks like the following:
SELECT [metal] FROM [ProductionDatabase].[dbo].[Metalurgy] EXCEPT SELECT [metal] FROM [TestDatabase].[dbo].[Metalurgy]
This gives me a list of records from [Production].[dbo].[Metalurgy] which do not reside in [TestDatabase].[dbo].[Metalurgy]. Now, I need to use that list to insert missing records into [TestDatabase].[dbo].[Metalurgy]. How can I modify the above code to INSERT the missing records?
I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.
Update TableName SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)' Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range
