|
I am sorry, perhaps it is me, but I still do not fully understand your problem.
What are the rules for when a record from table X should update the record in table Y. Is it simply when Column 3 is different?
It would also help to see the code/SP or whatever, that you are currently using.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Hi Henry,
My Y table is like a log table.When i move values from X table to Y table i want to set the value in column 3 based on the col2 value if col2 value changes from the previous record then i need to set column 3 value to 1, otherwise 0 (default).
|
|
|
|
|
Hi Avinash,
If I have understood your problem correctly, initially you are having two tables say tblX, tblY.
Initially, tblX and tblY will have the same values , with the exception of the Col4(exclusively for tblY)
i.e.
Col1 Col2 Col3 Col4
1 1234 4781258 0
2 1234 4781258 0
3 1234 4781258 0
4 1234 4781258 0
5 1234 4781258 0
6 1234 4781258 0
7 1234 4781258 0
8 5487 5124873 0
9 5487 5124873 0
10 5487 5124873 0
Now if we change any value of Col3 for tblX, then next time when we want to update tblY, Col4 of tblY will change to 1 and ofcourse Col3(of tblY).
i.e. if I change the 3rd , 5th and 10th row's Col3 values to say 3,5,10
respectively of tblX, then in tblY the values will be
Col1 Col2 Col3 Col4
1 1234 4781258 0
2 1234 4781258 0
3 1234 3 1
4 1234 4781258 0
5 1234 5 1
6 1234 4781258 0
7 1234 4781258 0
8 5487 5124873 0
9 5487 5124873 0
10 5487 10 1
Whereas, Col1 and Col2 will always be the same for both the tables.
If this assumption is correct, then this is the stored procedure I wrote for you
ALTER PROCEDURE dbo.UpdateTableY
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @MYCURSOR CURSOR
DECLARE @ROWID INT
DECLARE @COL2_X INT
DECLARE @COL3_X INT
DECLARE @COL2_Y INT
DECLARE @COL3_Y INT
SET @MYCURSOR = CURSOR FOR
SELECT A.ROWID,A.COL2,A.COL3
FROM (SELECT
ROW_NUMBER() OVER (ORDER BY COL1) AS ROWID,
COL2 , COL3 FROM tblX) A
OPEN @MYCURSOR
FETCH NEXT
FROM @MYCURSOR INTO @ROWID,@COL2_X,@COL3_X
WHILE @@FETCH_STATUS = 0
BEGIN
-- GET THE COL2 VALUES BASED ON ROW ID'S FROM tblY
SELECT @COL2_Y = COL2
FROM tblY
WHERE COL1 = @ROWID
--MATCH THE COL2 RECORDS OF BOTH TABLES
IF(@COL2_Y = @COL2_X)
BEGIN
-- GET THE COL3 VALUES BASED ON ROW ID'S FROM tblY
SELECT @COL3_Y = COL3
FROM tblY
WHERE COL1 = @ROWID
--IF A MISMATCH BETWEEN THE COL3 RECORDS OF BOTH TABLES
-- THEN UPDATE COL4 OF TABLE Y
IF(@COL3_Y <> @COL3_X)
BEGIN
UPDATE tblY
SET COL3 = @COL3_X,
COL4 = 1
WHERE COL1 = @ROWID
END
END
FETCH NEXT
FROM @MYCURSOR INTO @ROWID,@COL2_X,@COL3_X
END
CLOSE @MYCURSOR
DEALLOCATE @MYCURSOR
END
GO
It will work as per the logic I depicted to you earlier.
Hope this helps.
Niladri Biswas
|
|
|
|
|
Hi,
I have few rows in which the data reads as
rolename roledesc roletype roleuser
----------------------------------------
Team Leader (required) xyzxyz Primary Mike
Team Leader (required) xyzxyz Backup Cary
Process Focal abcabc Primary Jeff
Process Focal abcabc Backup Dave
Now, instead of having 2 rows for the the primay and backup... i should have this
rolename roledesc primary Backup
------------------------------------------
Team Leader (required) xyzxyz Mike Cary
Process Focal abcabc Jeff Dave
please help me in doing this
Thank you
Ramm
|
|
|
|
|
Hi, here is the solution
SELECT
P.ROLENAME,
P.ROLEDESC,
P.ROLEUSER AS 'PRIMARY',
B.ROLEUSER AS 'BACKUP'
FROM (
SELECT ROLENAME,ROLEDESC,ROLETYPE ,ROLEUSER
FROM TBLROLE
WHERE ROLETYPE = 'PRIMARY') P
INNER JOIN (
SELECT ROLEUSER ,ROLEDESC
FROM TBLROLE
WHERE ROLETYPE = 'BACKUP')B
ON P.ROLEDESC = B.ROLEDESC
Output:
ROLENAME ROLEDESC PRIMARY BACKUP
-------------------------------------------------------------
Team Leader (required) xyzxyz Mike Cary
Process Focal abcabc Jeff Dave
Hope this helps.
Niladri Biswas
|
|
|
|
|
Hi Biswas,
Thanks for the help,
I achieved this using PIVOT concept. Now I am able to get the data I need
Thanks a lot for the help.
Thanks
Ramm
|
|
|
|
|
Hi experts
i am using sql server 2005.
How to use like clause of sql server to search Pharse of Word
e.g
select * from abc where name like '%this is my book%'
but not working properly.pls help me
Thanku
Dinesh
|
|
|
|
|
Try this one;
SELECT *
FROM sysobjects
WHERE NAME LIKE ('%name%')
I are troll
|
|
|
|
|
There is nothing wrong with the syntax in the example you have given. When you say it is not working properly, what is happening? Is it giving you an error, or not returning any data, or returning unexpected data?
|
|
|
|
|
Hi man,
First of all I agree with David Skelly.
There is nothing wrong in the syntax.
However, I doubt regarding certain things which you need to check from your end.
For example I have a string like "Project Manager needed in this project (mandatory)"
Case 1:
If I give a SELECT QUERY like this
SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%Project Manager needed in this project (mandatory)%')
-OR-
SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%Project Manager %')
-OR-
SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%(mandatory)%')
I get the correct result.
Case 2:
Now if I give the query like this
SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%ProjectManager needed in this project (mandatory)%')
-OR-
SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('% (mandatory) %')
I am not getting any result.
The reason is that, in the first case there is no space between Project & Manager while the same holds good for (mandatory)
But the query behaved in the manner it is supposed to.
What I inferred from your statement, is that, may be you also ran through the same problem.
Have a look into that.
Let me know in case of any concern.
Niladri Biswas
|
|
|
|
|
|
Niladri Biswas
|
|
|
|
|
I have a few questions to ask. Before that let me tell you my problem definition.
I want to create some tables and insert them with some default data when ever a new database is created in my sql server 2005. So i was hoping if there whould be any way to add a trigger on sys.databases table.
So these are the questions I have:
1. How to list and view the system tables like sys.databases, sysobjects, ...etc in the SQL 2005 management studio?
We can query these tables but they are not visible under any database(master or msdb).
2. How to add a trigger on sys.databases or sysobjects table ?
3. How to list all triggers for a particular table?
s.a.w.
|
|
|
|
|
Hi Sunit
I am presenting the answers of your questions
1) a)How to list and view the system tables like sys.databases, sysobjects, ...etc in the SQL 2005 management studio?<br />
Ans:
SELECT * FROM SYS.DATABASES
SELECT * FROM SYS.OBJECTS
b)We can query these tables but they are not visible under any database(master or msdb).
Ans:SQL SERVER stores its system objects in a read-only database call RESOURCE DATABASE. However,they appear logically as SYS schema for every database. It can only be modified by Microsoft Customer Support Services (CSS) specialist.
For more information, please visit http://msdn.microsoft.com/en-us/library/ms178028.aspx[^]
However, SYSOBJECTS is just a view (the following query bears the proof) . So you cannot modify the underlying system table.
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));
Output:
<code>CREATE VIEW sys.objects</code> AS SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published FROM sys.objects$
2) How to add a trigger on sys.databases or sysobjects table ?
Ans:
If I query the SYSOBJECT / SYS.DATABASE, I will get all the SYSTEM TABLES + USER TABLES / SYSTEM DATABASES + USER DATABASES respectively.
So I can easily write a trigger on them.
e.g.In the MASTER database, there is a system table call spt_fallback_dev.
I have fired the following trigger statement & it worked
CREATE TRIGGER SOME_TRIGGER ON spt_fallback_dev
FOR UPDATE AS
SELECT * FROM spt_fallback_dev
The same holds good for any user table too.
For more information , you can look into this http://www.sql-server-performance.com/faq/trigger_system_table_p1.aspx[^]
3) How to list all triggers for a particular table?
Ans:Lets do a practical
Create a table like this
CREATE TABLE SAMPLE_TABLE
(
EID INT,
ENAME VARCHAR (12)
);
Add 2 triggers like the following
Trigger1
CREATE TRIGGER SAMPLE_TRIGGER_A ON SAMPLE_TABLE
FOR UPDATE AS
SELECT * FROM SAMPLE_TABLE
Trigger2
CREATE TRIGGER SAMPLE_TRIGGER_B ON SAMPLE_TABLE
FOR UPDATE AS
SELECT * FROM SAMPLE_TABLE
Now execute the following query
SELECT NAME
FROM SYSOBJECTS
WHERE DELTRIG = (
SELECT ID
FROM SYSOBJECTS
WHERE NAME = 'SAMPLE_TABLE')
OUTPUT:
NAME
------------------
SAMPLE_TRIGGER_A
SAMPLE_TRIGGER_B
Hope this helps.
Niladri Biswas
modified on Saturday, July 4, 2009 12:17 AM
|
|
|
|
|
|
Hi Guys,
I am using Datareader in my C# code to get data from db and dumping it into excel sheets. I am able to do it for everyrow correctly (based on the data from the sql view).
I have a problem in one of the sql views.
rolename roledesc roletype roleuser
----------------------------------------------------------------------
Team Leader (required) xyzxyz Primary Mike
Team Leader (required) xyzxyz Backup Cary
Process Focal abcabc Primary Jeff
Process Focal abcabc Backup Dave
As, when i read each row of this using reader i get,
(Team Leader (required) xyzxyz Primary Mike) AND (Team Leader (required) xyzxyz Backup Cary )) in 2 separate rows.
But when i have to dump this data to excel sheet, i have to only dump the Team Leader (required) once and the roleuser should be Mike and next to him Cary.
I mean,
cell1 cell2 cell3 cell4
----------------------------------------------------------------------
Team Leader (required) xyzxyz Mike Cary
Process Focal abcabc Jeff Dave
Please help me in modifying the sql view for this.
Tables: rolename, roledesc comes from roledescription table, roletype comes from roletypedescription table, roleuser from username table,
I can explain if the above statements are not clear
Thanks Ramm
|
|
|
|
|
My SP reads as
ALTER PROCEDURE [dbo].[tblRoleSummary_sel_all_sp]
SELECT txtRoleName, txtRoleDescription, txtRoleTypeDescription, txtUserName FROM tblRoleSummary_View
Do I need to change the SP and display the data as per my requiremnt or view itself can be changed for this/?
Thank YOu
Ramm
|
|
|
|
|
Do a self-join on the table, something like:
select p.rolename, p.roledesc, p.roleuser, b.roleuser
from RoleTable p, RoleTable b
where p.roletype = 'Primary'
and p.rolename = b.rolename
and b.roletype = 'Backup'
I think that's right, off the top of my head.
This assumes that every Primary has a Backup. If not, you will need an outer join.
|
|
|
|
|
Hi,
Its not compulsory, for every Primary, a backup will be available,
My query is
Hi, This is my SQL Query
SELECT dbo.tblRoleDescription.txtRoleName, dbo.tblRoleDescription.txtRoleDescription, dbo.tblRoleType.txtRoleTypeDescription, dbo.tblUserInfo.txtUserName, dbo.tblRoleAssignment.idSubProject, dbo.tblRoleAssignment.idLaunch FROM dbo.tblLaunchInfo INNER JOIN dbo.tblRoleAssignment ON dbo.tblLaunchInfo.idLaunch = dbo.tblRoleAssignment.idLaunch INNER JOIN dbo.tblProjectUsers ON dbo.tblRoleAssignment.idSubProject = dbo.tblProjectUsers.idSubProject AND dbo.tblRoleAssignment.idUser = dbo.tblProjectUsers.idUser INNER JOIN dbo.tblUserInfo ON dbo.tblProjectUsers.idUser = dbo.tblUserInfo.idUser INNER JOIN dbo.tblRoleDescription ON dbo.tblRoleAssignment.idRole = dbo.tblRoleDescription.idRole INNER JOIN dbo.tblRoleType ON dbo.tblRoleAssignment.idRoleType = dbo.tblRoleType.idRoleType
tblroleassignment -> idSubProject , idUser , idRoleType, idRole , idLaunch tblroledescription -> idRole(PK), txtRoleName, txtRoleDescription tblRoleType -> idRoleType (PK) , txtRoleTypeDescription tbluserinfo -> iduser, txtusername tbllaunchinfo -> idlaunch tblprojusers -> idsubproject, iduser
the sql query and the tables are given above, Please help me
Thank you,
Ramm
|
|
|
|
|
To be completely honest, I don't have time to write your query for you. I have my own work to do.
I've given you an idea of how to do it: a self-join on the table to link the Primary user to the associated Backup user will give you what you want.
If you want to simplify things for yourself you could create a view that only includes Primary users. Then create another view that only includes Backup users. Then join them together on role id. That would make the query easier to work with.
|
|
|
|
|
Hi Experts
I am using sql server 2005 express edition
i am not able to run the full text search.
when i create the catalog or any query realted to Full Text Search
it give me message
Full-Text Search is not installed, or a full-text component cannot be loaded.
plz help me how the full text search is runing
Thank u
Dinesh
|
|
|
|
|
|
Hello,
I have two columns, a category and a value. Below is the data:
Category | | Total | Devices | | 38111 | Plant | | 6063 | Improvements | | 2266 |
I want to add two more columns: Zero and Sum
So the columns would be Category, Zero, Total, Sum
Is there any way to take the addition of Zero and Total and have the result in Sum, THEN take the result of Sum, and make it the answer in the next row of Zero, then repeat by adding zero and Total?
The reason why I want to do this is b/c I am trying to create a waterfall graph and this is the only way I can think to do it with the data that I have. Thanks for your time.
JM
I would be forever indebt to you if you could help out. Thanks!
|
|
|
|
|
After thinking about this for a bit, I think I would do this by creating a TEMP table and cursor to loop through the main table.
The TEMP table would be something like this:
Category Zero Total Sum
Devices 0 38111 38111
Plan 38111 6063 44147
Improvements 44174 2266 4640
Use the following code snipet to get you going with the logic ...
DECLARE myCURSOR CURSOR FOR
SELECT CATEGORY, TOTAL
FROM myTABLE
OPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL
SET @PBAL = 0.0
WHILE(@@FETCH_STATUS=0) BEGIN
SET @GTOTAL = @PBAL + @XTOTAL
SET @MYSQL = 'INSERT INTO #MYTEMP'
SET @MYSQL = @MYSQL + ' VALUES(' + CHAR(39) + @XCATEGORY+ CHAR(39) + ',' + @PBAL, + ',' + @XTOTAL + ','
SET @MYSQL = @MYSQL + @GTOTAL + ')'
PRINT @MYSQL
EXEC(@MYSQL)
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL
END
CLOSE myCURSOR
DEALLOCATE myCURSOR
GO
Hope this helps you !
|
|
|
|
|
I will give that a shot, thank you for your help!
|
|
|
|
|