Transact SQL :: Nested Cursors To Update Table?
May 15, 2015
I have been wrestling with the code all day to accomplish the following: I need to update a table based on values from another table. So far, I have been able to do the below:
DECLARE @LookUpTerm VARCHAR(25)
, @SearchCol VARCHAR(255)
, @LogonIDToProcess VARCHAR(50)
, @Matched CHAR
, @Cycle INT = 1
IF OBJECT_ID('tempdb..#Glossary','U') IS NOT NULL DROP TABLE #Glossary
IF OBJECT_ID('tempdb..#Employees','U') IS NOT NULL DROP TABLE #Employees
[code]...
View 7 Replies
ADVERTISEMENT
Sep 20, 2007
Problem:
Two tables t1 and t2 have the same schema but exist on two different servers. Which is the better technique for updating t2 and why?
/****** Object: Table [dbo].[t1] Script Date: 9/6/2007 9:55:21 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t1]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[t1] (
k [int] IDENTITY (1, 1) NOT NULL ,
a [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
b [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
c [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
x [int] NULL ,
y [int] NULL ,
amt [money] NULL
) ON [PRIMARY]
END
GO
/****** Object: Table [dbo].[t2] Script Date: 9/6/2007 9:55:44 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t2]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[t2] (
k [int] IDENTITY (1, 1) NOT NULL ,
a [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
b [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
c [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
x [int] NULL ,
y [int] NULL ,
amt [money] NULL
) ON [PRIMARY]
END
GO
-- Technique 1:
set identity_insert t2 on
insert into t2 (k,a,b,c,x,y,amt)
select k,a,b,c,x,y,amt from t1
where not exists (select k from t2 where t1.k = t2.k)
set identity_insert t2 off
update t2
set a = t1.a,
b = t1.b,
c = t1.c,
x = t1.x,
y = t1.y,
amt = t1.amt
from t1
where t1.k = t2.k
-- Technique 2:
set identity_insert t2 on
declare t1_cur cursor for
select k,a,b,c,x,y,amt from t1
for read only
open t1_cur
declare @k int
declare @a char(10)
declare @b char(10)
declare @c char(10)
declare @x int
declare @y int
declare @amt money
fetch next from t1_cur into @k,@a,@b,@c,@x,@y,@amt
while(@@FETCH_STATUS = 0)
begin
if exists(select k from t2 where k = @k)
begin
update t2
set a = @a, b = @b, c = @c, x = @x, y = @y, amt = @amt
where (k = @k)
end
else
begin
insert into t2 (k,a,b,c,x,y,amt) values(@k,@a,@b,@c,@x,@y,@amt)
end
fetch next from t1_cur into @k,@a,@b,@c,@x,@y,@amt
end
close t1_cur
deallocate t1_cur
set identity_insert t2 off
Thanks,
Joel K
Database Adminstration/Application Development
View 1 Replies
View Related
Sep 26, 2005
Hi all,
I have a temp guy who is doing some work for us (seriously, I never wrote the query).
Now this is the scenario (hold tight). So we have a stored procedure as follows...
ALTER PROCEDURE createSegmentPoints AS
DECLARE @fLat INTEGER
DECLARE @fLon INTEGER
DECLARE @segId INTEGER
-- declare cursor to return the from lat and lon for all segments that do not have address point 109.
DECLARE c1 CURSOR FOR (SELECT From_Latitude, From_Longitude, id AS segment_id FROM Segments WHERE SegmentType != 109)
OPEN c1
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
WHILE @@FETCH_STATUS = 0
BEGIN
-- insert into table the segId, from lat, from lon and returned segment id from function.
INSERT INTO test VALUES (@segId,@fLat,@fLon,dbo.points_test(@fLat,@fLon))
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
END
CLOSE c1
DEALLOCATE c1
As you can see here I am using a Cursor, which in turn calls a function
with each row that is processed in the recordset. The function that is
called is as follows...
ALTER FUNCTION points_test(@x INTEGER, @y INTEGER)
RETURNS INTEGER
AS
BEGIN
-- function to find the closed segment point with address point 109 to the segment specified in procedure.
DECLARE @tempDistance FLOAT(4)
SET @tempDistance = 1000000
DECLARE @id, @seg, lat, lon INTEGER
DECLARE @distance, @xd, @yd FLOAT
DECLARE c1 CURSOR FOR (SELECT from_latitude, from_longitude, id FROM segments WHERE segmenttype = 109)
OPEN c1
FETCH NEXT FROM c1 INTO @lat, @lon, @id
WHILE @@FETCH_STATUS = 0
BEGIN
-- calucations to get distance.
SET @xd = (@lat-@x)
SET @yd = (@lon-@y)
SET @distance = SQRT((@xd*@xd) + (@yd*@yd))
-- test if you have shortest distance.
IF (@distance < @tempDistance)
BEGIN
SET @tempDistance = @distance
SET @seg = @id
END
FETCH NEXT FROM c1 INTO @lat,@lon, @id
END
CLOSE c1;
DEALLOCATE c1;
RETURN @seg
END
(This function works out an equation to get the shortest distance from
two parameters passed to the function calculated with data from each
row returned within the cursor)
As you can see here, this function contains ANOTHER cursor!! Ouch. The
fact that their is an SQL query in a function is a killer, but having
another embedded cursor there is also a killer - this has virtually
killer the application.
So, how best is it for me to correct this. Should I turn the function
into a stored procudure? But even if I do this, the nested cursor still
remains. I was thinking maybe to have the SQRT equations within the
SELECT expression and then wrapped in a MIN() to maybe get the lowest
value.
Any ideas would be of great help.
Thanks
Tryst
View 2 Replies
View Related
Jun 3, 2004
Morning everyone,
I have a sp that I've created that is to show me everyone table name and column name using nested cursors. However when I execute the procedure it doesn't show me the names, it just tells me the command completed successfully. Here is the code:
CREATE PROCEDURE uspSeeAllViews
AS
SET NOCOUNT ON
DECLARE @strMessageVARCHAR(100)
DECLARE @strColumnVARCHAR(100)
DECLARE @strViewVARCHAR(100)
DECLARE @strCommandVARCHAR(250)
DECLARE crsViews CURSOR FOR
SELECT
name AS strView
FROM
sysobjects
WHERE
type = 'U'
OPEN crsViews
FETCH NEXT FROM crsViews INTO @strView
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE crsColumns CURSOR FOR
SELECT
name AS strColumn
FROM
syscolumns
WHERE
name = @strView
OPEN crsColumns
FETCH NEXT FROM crsColumns INTO @strColumn
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @strView + ':' + @strColumn
FETCH NEXT FROM crsColumns INTO @strColumn
END
CLOSE crsColumns
DEALLOCATE crsColumns
FETCH NEXT FROM crsViews INTO @strView
END
CLOSE crsViews
DEALLOCATE crsViews
Thanks for looking, any ideas??
View 7 Replies
View Related
Apr 4, 2008
I have a table like:
Converted_Mortgage_Number Activity_Date Activity_Time Sequence_Number, History_Text
100 2006-01-20 84936 1 Sent a reminder letter
105 2006-01-23 134502 1 Called, but no reply
100 2006 01-24 104532 1 Asked to call again later
100 2006-01-24 104532 2 when it is more convenient
100 2006-01-24 104532 3 and the person I require is available
I wish to concantenate the History_text fields together for records with the same Converted_Mortgage_Number, Activity_Date and Activity_Time so:
100 2006-10-24 104532 Asked to call again later when it is moreconvenient and the person I require is available.
To do this I amusing nested cursors so:
USE ar
GO
DECLARE @mortgage INT,
@date datetime,
@time INT,
@getMortgage CURSOR,
@notes varchar(MAX),
@notesComplete varchar(MAX),
@getDetail CURSOR
SET @getMortgage = CURSOR FOR
SELECT Converted_Mortgage_Number, Activity_Date, Activity_Time
FROM format_history
GROUP BY Converted_Mortgage_Number, Activity_Date, Activity_Time
ORDER BY Converted_Mortgage_Number ASC
OPEN @getMortgage
FETCH NEXT
FROM @getMortgage INTO @mortgage, @date, @time
WHILE @@FETCH_STATUS = 0
BEGIN
SET @notes = ''
SET @getDetail = CURSOR FOR
SELECT History_Text
FROM format_history
WHERE Converted_Mortgage_Number = @mortgage AND Activity_Date = @date AND Activity_Time = @time
ORDER BY Sequence_Number
OPEN @getDetail
SET @notesComplete = ''
FETCH NEXT FROM @getDetail INTO @notes
WHILE (@@FETCH_STATUS = 0)
BEGIN
@notesComplete = @notesComplete + @notes + ' '
FETCH NEXT FROM @getDetail INTO @notes
END
PRINT @notesComplete
CLOSE @getDetail
DEALLOCATE @getDetail
FETCH NEXT
FROM @getMortgage INTO @mortgage, @date, @time
END
CLOSE @getMortgage
DEALLOCATE @getMortgage
GO
But I am getting the error: 'Incorrect syntax near the keyword CLOSE, incorrect syntax near @notesComplete'.
Can anyone please see where I am going wrong?
View 4 Replies
View Related
Aug 24, 2007
What is the best way to nest cursors?
This code does not seem to be returning me all of the data.
Code Snippet
DECLARE element_Cursor CURSOR FOR
SELECT ElementTypeRecNo
FROM dbo.tblTemplateElementType
where TemplateRecno = @TemplateRecNo
OPEN element_cursor
FETCH NEXT FROM Element_Cursor into @ElementTypeRecno
--delete from tblElementCPO
WHILE @@FETCH_STATUS = 0
BEGIN
select @Count = count (*)
from tblProjTypeSet
where ProjRecno = @ProjRecNo
if @Count > 0
begin
select @ProjTypeRecno = ProjTypeRecno
from tblProjTypeSet
where ProjRecno = @ProjRecNo
select @Count = count (*)
FROM dbo.tblElementTypeDep
where TemplateRecno = @TemplateRecNo
and ProjTypeRecno = @ProjTypeRecNo
if @Count > 0
begin
DECLARE ElementTypeDep_Cursor CURSOR FOR
SELECT ElementTypeDepRecNo, PreElementTypeRecNo,
PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,
Description
FROM tblElementTypeDep
WHERE (TemplateRecNo = @TemplateRecNo)
AND (ProjTypeRecNo = @ProjTypeRecno)
AND (PreElementTypeRecNo = @ElementTypeRecno)
OPEN ElementTypeDep_cursor
FETCH NEXT FROM ElementTypeDep_Cursor
into @ElementTypeDepRecno, @PreElementTypeRecNo,
@PostElementTypeRecno, @ElapsedTimeDueDates, @ElapsedTimePlanDates,
@Description
WHILE @@FETCH_STATUS = 0
BEGIN
select @PreElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @ProjRecNo
and IssueRecno = @IssueRecNo
and ElementTypeRecno = @PreElementTypeRecno
if @PreElementRecno is not null
begin
select @PostElementRecNo = ElementRecno
from tblElementCPO
where ProjRecNo = @ProjRecNo
and IssueRecno = @IssueRecNo
and ElementTypeRecno = @PostElementTypeRecno
if @PostElementRecno is not null
begin
select @Count = count (*)
from tblElementDepCPO
where ElementTypeDepRecno = @ElementTypeDepRecno
and PreElementRecNo = @PreElementRecNo
and PostElementRecno = @PostElementRecno
if @Count = 0
begin
INSERT INTO tblElementDepCPO
(ElementTypeDepRecNo, PreElementRecNo,
PostElementRecNo, ElapsedTimeDueDates,
ElapsedTimePlanDates, Description,
ChangeDate, ChangePerson)
VALUES (@ElementTypeDepRecno, @PreElementRecNo,
@PostElementRecno, @ElapsedTimeDueDates,
@ElapsedTimePlanDates, @Description,
GETDATE(), CURRENT_USER)
end
select @Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @PostElementRecNo
if @Count = 0
begin
select @Count = count (*)
from tblElementAttemptCPO
where ElementRecNo = @PostElementRecNo
if @Count = 0
begin
select @NextPlanDate = ProjectedCompletionDate,
@NextDueDate = RequiredCompletionDate
from tblElementAttemptCPO
where ElementRecno = @PreElementRecNo
end
else
begin
select @NextPlanDate = @StartDate
select @NextDueDate = @StartDate
end
select @NextPlanDate =
dbo.fncAddBusinessDays (@NextPlanDate, @ElapsedTimePlanDates)
select @NextDueDate =
dbo.fncAddBusinessDays (@NextDueDate, @ElapsedTimePlanDates)
insert into tblElementAttemptCPO (ElementRecno,
ProjectedCompletionDate, RequiredCompletionDate,
ProjectedStartDate, RequiredStartDate,
ActualStartDate, ActualCompletionDate, AttemptNum,
IsCompleted, IsStarted, ResponsibleRoleTypeRecno,
ChangeDate, ChangePerson)
values (@PostElementRecno,
@NextPlanDate, @NextDueDate,
'1/11/1900', '1/11/1900',
'1/11/1900', '1/11/1900', 0,
0, 0, 0,
GETDATE(), CURRENT_USER)
end
end
end
FETCH NEXT
FROM ElementTypeDep_Cursor
into @ElementTypeDepRecno, @PreElementTypeRecNo,
@PostElementTypeRecno, @ElapsedTimeDueDates, @ElapsedTimePlanDates,
@Description
END
CLOSE elementTypeDep_Cursor
DEALLOCATE elementTypeDep_Cursor
end
FETCH NEXT FROM element_Cursor into @ElementTypeRecno
END
CLOSE element_Cursor
DEALLOCATE element_Cursor
end
View 2 Replies
View Related
Jul 1, 2004
I am not getting any error message. It just hangs...
Any idea what I am missing....
******************
ALTER PROCEDURE TrigSendPreNewIMAlertP2
@REID int
AS
Declare @RRID int
Declare @ITID int
Declare @FS1 int
Declare @FS2 int
Declare crReqRec cursor local for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec into @RRID
set @FS1 = @@Fetch_Status
while @FS1 = 0
Begin
Declare crImpGrp cursor local for
select ITID from RequestRecords where RRID = @RRID
open crImpGrp
fetch next from crImgGrp into @ITID
set @FS2 = @@Fetch_Status
while @FS2 =0
Begin
EXEC TrigSendNewIMAlertP2 @ITID
FETCH next from crImpGrp into @ITID
end
FETCH next from crReqRec into @RRID
end
close crImpGrp
deallocate crImpGrp
close crReqRec
deallocate crReqRec
View 1 Replies
View Related
Dec 11, 2000
All,
I have a question on a nested Cursor statement, I am having problem getting the @@Fetch_Status to work correctly...
I can get the first cursor to fetch and the the second cursor to fetch all of it rows but when it reaches the end. It should fetch the first cursors next row and @@fetch_Status should be 0.
Is there a way around this???
Thanks,
~Lee
Alter Procedure Summary As
Declare @DateID nvarchar(50)
Declare @ShortDate nvarchar(30)
Declare @SourceIPID nvarchar(50)
Declare @SourceIP nvarchar(30)
Declare @Packets int
Declare lead_cur CURSOR FOR
SELECT tbl_Date.DateID, tbl_Date.ShortDate FROM Tbl_Date WHERE NOT tbl_date.shortdate = convert(char(11),GetDate())
FOR READ ONLY
Declare lead_cur2 CURSOR FOR
SELECT SourceIPID, SourceIP FROM tbl_SrcIP
FOR READ ONLY
Open lead_cur
Open lead_cur2
FETCH NEXT FROM lead_cur INTO @DateID,@ShortDate
While @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM lead_cur2 INTO @SourceIPID, @SourceIP
While @@FETCH_STATUS = 0
BEGIN
Set @Packets = (Select ISNULL(Sum(CAST(strNumPackets As Int)),0)
FROM tbl_All_SysDATA
Where DateID = @DateID and SourceIPID = @SourceIPID)
If @packets > 0
Insert tbl_SysIPSummary (DateID,IPID,Packets) VALUES (@DateID,@SourceIPID,@Packets)
FETCH NEXT FROM lead_cur2 INTO @SourceIPID, @SourceIP
END
FETCH NEXT FROM lead_cur INTO @DateID,@ShortDate
END
Close lead_cur
Close lead_cur2
DEALLOCATE lead_Cur
DEALLOCATE lead_Cur2
GO
View 1 Replies
View Related
Dec 4, 2007
I have a Master/Detail table setup - let's call the master "Account" and the detail "Amount". I also have a "black box" stored procedure (BlackBox_sp) which carries out a lot of complex processing.
What I need to do is, for each Account, I need to iterate thtough it's Amount records and call the black box each time. Once I've finished going through all the Amount records, I need to call the black box again once for the Account. This must be done with the Account & Amount rows in a specific order.
So I have something along the lines of
Code Block
DECLARE Total int
DECLARE Account_cur
OPEN Account_cur
FETCH NEXT FROM Account_cur
WHILE FETCH_STATUS = 0
BEGIN
SET Total = 0
DECLARE Amount_cur
OPEN Amount_cur
FETCH NEXT FROM Amount_cur
WHILE FETCH_STATUS = 0
BEGIN
SET Total = Total + Amount
EXEC BlackBox_sp (Amount)
END
CLOSE Amount_cur
EXEC BlackBox_sp (Total)
END
CLOSE Account_cur
Any tips on another approach would be appreciated given the contraints I have.
Greg.
View 1 Replies
View Related
Sep 24, 2015
Is there any way to convert a bind variable, which is a list of integer or string values, to nested table in MS SQL Server. I am looking for something like
CAST in Oracle, which converts a varray type column into a nested table. Do we have something like this in SQL Server.
in Oracle:
SELECT CAST(s.addresses AS address_book_t)
FROM states s
WHERE s.state_id = 111;
I have read about Table valued Parameter, but I am looking for another solution.
View 4 Replies
View Related
Apr 18, 2008
I need to use a nested cursor in SQL server 2005. I have never done this before. The problem here is that I need to use "@@FETCH_STATUS' twice.Once for my inner loop and once for my outer loop. If there is a way to use two different @@FETCH_STATUS variables at once, I would like to know how.
Here is my code. The inner loop is currently pseudo code and I just need to figure out how to code it.
DECLARE @mycur1 CURSORDECLARE @mycur2 CURSORDECLARE @InMarketId INTSET @InMarketId=575 -- Hard code for now. Just for testing.DECLARE @test VARCHAR(10)SET @mycur1 = CURSORSET FORSELECT SubDivisionId FROM SubDivision WHERE MarketId=@InMarketIdOPEN @mycur1FETCH NEXT FROM @mycur1 INTO @testWHILE @@FETCH_STATUS = 0 BEGIN
PRINT @test-- Begin Inner Nested Loop-- FOR EACH SubDivisionId in SubDivisionSubMarket SDSM delete from SubMarket SM Where SDSM.SubMarketId=SM.SubMarketId
-- End of Inner nested Loop
FETCH NEXT FROM @mycur1 INTO @test ENDDEALLOCATE @mycur1------------------------------------------Here are my three tables I am scripting agains and a description of each.
SubDivision-----------SubDivisionIdMarketIdNameDescriptionCreatedBy
SubDivisionSubMarket--------------------SubDivisionIdSubMarketId
SubMarket--------------------SubMarketIdCodeDescriptionLastUpdateDate
View 2 Replies
View Related
Oct 8, 2012
I have added some SQL to an Access form which updates the dbo_BM_Map table when the user hits the Apply button. There is a temp table with various fields, two being "Chapter_No" and "Initial_Mapping_Complete" which the update is based on.
I want this update to only apply to chapters that only have one name in the "Initial_Mapping_Complete" column. If a chapter has more than one then the update should ignore it. The attached screengrab shows you. The update should ignore chapter 19 as there are two people (Jim and James) in the Initial_Mapping_Complete field. Here is my code.
pdate dbo_BM_Map inner Join Temp_Progression_Populate
on dbo_BM_Map.Product_ID = Temp_Progression_Populate.Product_ID
Set dbo_BM_Map.Initial_Mapping_Complete = Temp_Progression_Populate.Initial_Mapping_Complete
Where dbo_BM_Map.Chapter_No = Temp_Progression_Populate.Chapter_No
And Temp_Progression_Populate.Initial_Mapping_Complete in
(Select count(Initial_Mapping_Complete), Chapter_No
from Temp_Progression_Populate
Group by Chapter_No
Having Count(Initial_Mapping_Complete) = 1)
View 2 Replies
View Related
Jul 8, 2015
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
View 7 Replies
View Related
Aug 19, 1999
What I am trying to do is iterate through a set of records from a table, and setting one field to a common value on each record, and setting another to an incremented value. Here it is:
Table contains columns A, B, and C.
A is a unique ID.
B is an ID.
C is another value.
I want to, for all records where field B = "AAA", Set B to "AAB", and set A on each record to the next unique ID that I get from somewhere else. Here's my SQL Stored Proc:
DECLARE MYCURSOR SCROLL Cursor FOR SELECT A, B FROM MyTable where B = 1 FOR UPDATE.
Loop:
FETCH NEXT INTO @vcA, @vcB FROM MYCURSOR.
UPDATE MYTABLE SET A = @nctr, B = "AAB" WHERE CURRENT OF MYCURSOR
SELECT @nctr = @nctr + 1
If @@FETCH_STATUS = 0 goto Loop:
I hit an error as soon as I get to the Update -
"Cursorfetch: Number of variables declared in the INTO list must match that of selected columns"
Go Figure. I really need an answer on this ASAP! Help!
View 1 Replies
View Related
Oct 25, 2015
I have a temp table like this
CREATE TABLE #Temp
(
ID int,
Source varchar(50),
Date datetime,
CID varchar(50),
Segments int,
Air_Date datetime,
[code]....
Getting Error
Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.
View 4 Replies
View Related
Dec 2, 2015
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
Note: ROW is Partition By ColumnA,ColumnB
TableName: PromotionList
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 0 NULL
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 0 NULL
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
Expected outcome after performing update on the table
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 1 Overlap with row ID 4
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 Overlap with row ID 5
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
View 4 Replies
View Related
Aug 4, 2015
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
View 10 Replies
View Related
Oct 20, 2015
[URL] ...
So What I am trying to accomplish is sum up overlapping time ranges while also keeping Unique data rows within the table. So If a data row is unique meaning it is NOT within a overlapping group of data rows then I want to just insert it into a "final table", but if the data rows are overlapping then I want to grab the min(timestart) and the max(timestop) and the PKID of the data row with the max(timestop) within the overlapping group.
I accomplish this task using nested cursors, however When I place my method into a trigger which runs on INSERT, then my trigger never runs and nothing happens. Is there a way to accomplish this without using cursors. I have placed my cursor method into the sql fiddle to be inspected.
create table temp1
( pkid int,
line int,
dateentry date,
timestart time,
timestop time
)
[Code] ....
DESIRED RESULTS:
1060
1
2015-10-01
16:30:00
17:00:00
30
NULL
[Code] .....
View 11 Replies
View Related
May 20, 2015
Is it possible to check/notify if something (record) has changed in the linked table (from linked server) and automatically update table in local one?
View 4 Replies
View Related
Nov 17, 2015
I have a daily record table (has field Date1, field2Update) and have another table(has field Date2, Flag (value: 0 and non 0 values)
I want to update table1 and set field2Update as somevalue where table2.Flag <> 0
View 4 Replies
View Related
Jun 10, 2015
Matrix table has ID column and data below.
ID Flag TestDate Value Comment
111 2 12/15/2014 7.5 null
222 2 Null 10 received
Matrix_Current table could have 1 or multiple rows as below.
ID Flag TestDate Value Comment
111 2 01/26/2015 7.9
111 2 02/23/2015 7.9
111 2 04/07/2015 6.8
222 1 null 8 test comment 1
222 3 null 9 test comment 2
When I run below update
UPDATE AM
SET M.Flag = MC.Flag, M.TestDate = MC.TestDate,
M.Value = MC.Value, M.comment = MC.Comment
FROM dbo.Matrix M inner join dbo.Matrix_Current MC on M.ID = MC.ID
Matrix table has value below:
ID Flag TestDate Value Comment
111 2 01/26/2015 7.9
222 1 Null 8 test comment 1
I want to update Matrix table from all row from Matrix_Current, final table would like below:
ID Flag TestDate Value Comment
111 2 04/07/2015 6.8
222 3 Null 9 test comment 2
View 3 Replies
View Related
Apr 6, 2004
Original Table
Col_1 Col_2
A
A
B
A
C
B
D
B
I want to obtain this
Col_1 Col_2
A 1
A 1
B 2
A 1
C 3
B 2
D 4
B 2
Is possible without use cursors ?
View 3 Replies
View Related
Sep 5, 2015
How to update tblA with records from tblB (see below expected results) when they have common column DrawingNo?
View 4 Replies
View Related
Mar 14, 2008
I have 2 tables which with one-many relation. Table B has three child records based on Table A. I want to update value in Table from one of the records in table B.
Right now I am thinking of creating 2 temp table importing values there but does not seem to work. Can someone please help thanks. Armoghan
View 4 Replies
View Related
Dec 2, 2015
I am trying to do a simple update in one sql table from another where a certain condition in both tables are met.I am missing something basic and have tried numerous posted examples, without success.Here is my code (that does not update) :
opdragaliasnaaminsit.Connection = konneksie
opdragaliasnaaminsit.CommandText = "UPDATE vyfjaarontledings " & _
"SET aliasnaam = T2.aliasnaam" & _
" FROM vyfjaarontledings T1" & _
" INNER " & _
"JOIN blokke T2 " & _
" ON T1.plaasno = T2.plaasno " & _
"WHERE T1.plaasno = T2.plaasno"
opdragaliasnaaminsit.ExecuteNonQuery()
I am trying to update aliasnaam in vyfjaarontledings from blokke.
View 10 Replies
View Related
Nov 15, 2015
I have two tables i have to update table2 using table1 without using while loop.
example given below.
Table1
rid
id
amt
firdate
lastdate
1
1
500
[code]....
View 7 Replies
View Related
Sep 28, 2015
I have following T-SQL statement:-
insert into [dbo].[tbl_FG_Alert_Count_All_Report] ([Date] ,[Count] ,[Rule Type])
SELECT TOP 10 [Date]
,[Count]
,[Rule Type]
FROM [dbo].[tbl_FG_Alert_Count_All] where Count <>'0' and DATEDIFF(dy,date,GETDATE()) = 1 order by Date desc
When I ran this T-SQL statement in SSMS; I don't get any error and as expected, I can see new data in [dbo].
[tbl_FG_Alert_Count_All_Report] table.
Now I created one job with same T-SQL Statement. Job completes successfully with out giving any error message; But unfortunately I don't see any new data in [dbo].[tbl_FG_Alert_Count_All_Report] table. What would be the reason that I don't see new data when job completes successfully but I can see new data after executing same T-SQL statement in SSMS?
View 3 Replies
View Related
Oct 22, 2015
Every day the timestamp is changed on all rows in one of the table(table has 1 mio records).
What is the best way to find out which query is doing this?
Could be also query which updates only one record(or couple of them) but is executed for each record once.
I have been looking sys.dm_exec_query_stats and sys.dm_exec_sql_text for past but couldn't find.
I can't use event notifications since there is service broker disabled.
View 5 Replies
View Related
Nov 30, 2015
I am having challenge to update the redemption table from the multiple card activation table. I want to update the redemption table with the activation date closest to the redeem date.
For example: Redeem date 20071223, I need to update the top row Date, Year, Period fields from the Card activation table.
Redeem date 20071228, I want to refer to the second row in the Card activation table date 20071228.
Redeem date 20080316 or later, I want to use the last row in the card activation table date 20080316.
How to modify the update query to select the right activation row accordingly?
Below is my partial code I used but it always pick the 20071223 date to update my redemption table.
CREATE TABLE #Card
(
[CardNumber] varchar(20)
,[ Date] int
,[ Year] int
,[ Month] int
,[ Period] int
)
[Code] ....
View 13 Replies
View Related
Nov 10, 2015
I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.
Txt_Temp - Table
I tried this code with no succes and only one row is updating
update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber
OR Add this code in view
Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code
from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber
Vin
123456
123123
123789
Resrve_Temp1 - Table
asddfghjklk123654
asddfghjklk123456
asddfghjklk321564
asddfghjklk123123
asddfghjklk123789
asddfghjklk654655
asddfghjklk456465
My Result can be in Txt_Temp table or new table or with one or two columns
asddfghjklk123456 123456
asddfghjklk123123 123123
asddfghjklk123789 123789
View 10 Replies
View Related
Sep 7, 2015
I have table A with colums ID and Product. I have table B with ID (same as table A) and revenue of product.
I need to update the field Product of table A with the revenue of table B.
I'm confuse with the update joining both tables. I tried this, but obviously has an error:
Update A set Product=B.Revenue where A.ID=B.ID
View 6 Replies
View Related
Jul 23, 2015
I need to update more than one record at once. I have ~ 100 records that I have to update and don't want to execute query 100 times.
My query looks like this:
Update Table1
Set Table1.field1 = ( select Table2.field1 from Table2 where Table2.field2 IN ('a', 'b', 'c')
where Table1.field2 IN ( 'a', 'b', 'c')
It obviously failed because subquery returned more than one value and error message stated that I can't use '=' operator in this case.
My question: how could I update the same column from many records in one execution?
View 10 Replies
View Related
Aug 11, 2015
I have a table with the following fields:
ID (int, identity)
Name (nvarchar(255))
Block (nvarchar(50))
Street (nvarchar(255))
Floor (nvarchar(50))
Unit (nvarchar(50))
Address1 (nvarchar(255))
Address2 (nvarchar(255))
I want to iterate through the table and populate Address1 as [Block] [Street] #[Floor]-[Unit].If the 'Floor' field contain a number < 10 (e.g., '8'), I want to add a '0' before it (e.g., '08'). Same for Unit.How would I do this using cursors (or other recommended method)?
View 4 Replies
View Related
