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
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
-- 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.
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
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'.
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
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
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
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
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.
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.
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
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)
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?
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!
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
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
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 )
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
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
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) :
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?
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 )
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
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)?
