Putting Data Into Table B From Table A Without Using A Cursor
Aug 24, 2006
on SQL Server 2005, no SP.
Currently using a cursor in a stored procedure to retrieve data from one table and to put it into another table. like so:
declare @HTR money
declare @Uniqueid varchar(15)
declare cur_HTR cursor for
select uniqueid, sum(hours_to_resolve) as thtr from com_trail_helpdesk_module group by uniqueid
open cur_htr
fetch next from cur_HTR into @Uniqueid, @HTR
while @@fetch_status = 0 begin
update com_hpd_helpdesk_history
set total_hours_to_resolve = @HTR
where case_id_ = @Uniqueid
and dateasat = @dateasat
fetch next from cur_HTR into @Uniqueid, @HTR
end
close cur_htr
deallocate cur_htr
...
This is taking about 45 minutes each time to do 21k records. Is there a faster, better way to do this?
Hello, I created this DataTable, add rows of data to it, and then display the data on to a form via a repeater. Dim ds As DataSet = New DataSetDim dtTableName As DataTable = New DataTable("dtTableName") dtTableName.Columns.Add("Description")dtTableName.Columns.Add("ItemNumber")dtTableName.Columns.Add("Quantity")dtTableName.Columns.Add("Price")ds.Tables.Add(dtTableName) What I need to do now is create a table in SQL Server and update the database with the data I've collect in my dataset. How do I bind and update this data to a sql server table? Thanks!James
I need to analyze the results from Table 1, count how many records there are where one field matches, and then put some of Table 1's information into Table 2 along with the count of the records that match. This is built to interface with another system, so a lot of the information, names, and relationships can't always be changed.
Table 1: tblResourceAllocation Fields (Description):ID (Unique)LastName (Unique alphanumeric string for each employee, but one employee can have multiple records in this table)Project (Unique name, can have multiple entries in this table)Owner (Unique alphanumeric string for each owner, but one owner can have multiple records in this table)ResourceStartDate (datetime)ResourceEndDate (datetime)ServiceID [I](this is what I want to aggregate)Status_FC [I]
Table 2: tblGRFM ID (unique)Pm (the tblResourceAllocation.Owner's actual full name, which I'll look up from an EmployeeData table and concatenate the FirstName and LastName)IdClient (from tblResourceAllocation.Project I will look up the Customer from the Projects table, then look up the Customer ID from the Customers table)US_State_Id (look up State from Projects table then ID from tblStateCodes)Project_name (same as tblResourceAllocation.Project)Status (same as tblResourceAllocation.Status_FC)Id_operlst (same as tblResourceAllocation.ServiceID)Start (earliest start date of relevant entries)end (latest end date of relevant entries)Nb_ress (total number of ResourceAllocation entries that match Id_operlst)Cadence (explanation below)
I need to calculate how many of each serviceID's are in table 1, and then store that calculation and the ServiceID (Id_operlst) in table 2I need to separate those calculations by Owner, IdClient, US_State_Id, and Project_Name I.E. If there are 5 entries in table 1 with ServiceId = X, and Owner A is listed on 2 of them and Owner B is listed on 3, I want it displayed as [Owner A, X, 2] and [Owner B, X, 3] in table 2.
Same goes for the other fields, which obviously adds more complexityWithin those calculations I also need to calculate the Start and end date for table 2. Using the last example, if one entry of Owner A's service X has the early start date and the other entry has the later end date, those dates need to be displayed in the Start and end for table 2 (should I use Rank here?)
I need to create a string of numbers separated by semicolons that shows how many resources are working on a given week after the start date
Example:I have 3 resources on a projectResource 1: 12/31/2012-1/27/2013 (Week 1-4 of our calendar)Resource 2: 1/7/2013-1/27/2013 (Week 2-4)Resource 3: 1/7/2013-1/27/2013 (Week 2-4)tblGRFM.Start will equal 12/31/2012 and tblGRFM.end will equal 1/27/2013Cadence should be equal to "1;3;3;3" to indicate that one resource will be working the project the first week, and three resources will be working the project for the next three weeks.
Hi, I am trying to incorporate a cursor into a table function so that i can use the function to insert values inot a table. Everytime i add the INSERT INTO @MyTable syntax then the cursor seems to start an endless loop.
Does anyone have any ideas for me?
Code Block ALTER PROC csp_ICASTransaction_AskDoc @ClientID INT AS DECLARE @ClientID INT SET @ClientID = 1 DECLARE @cClientID INT DECLARE @cMonth VARCHAR(20) DECLARE @cOccurance INT DECLARE @Counter INT DECLARE @AskDoc CURSOR SET @AskDoc = Cursor FOR select @ClientID, Month_Year, SUM(AskDocs) FROM zzenrolled$ WHERE ICASClientID = @ClientID GROUP BY Month_Year OPEN @AskDoc FETCH NEXT FROM @AskDoc INTO @cClientID, @cMonth, @cOccurance /* SELECT @@Cursor_Rows PRINT @cClientID PRINT @cMonth PRINT @cOccurance */ DECLARE @MyTable TABLE (ClientID INT, Date DATETIME, Occurance INT) WHILE (@@FETCH_Status = 0) BEGIN SET @Counter = 0 INSERT INTO @MyTable VALUES (@cClientID, CASE @cMonth WHEN 'Jan-03' THEN '20030101' WHEN 'Feb-03' THEN '20030201' WHEN 'Mar-03' THEN '20030301' WHEN 'Apr-03' THEN '20030401' WHEN 'May-03' THEN '20030501' WHEN 'Jun-03' THEN '20030601' WHEN 'Jul-03' THEN '20030701' WHEN 'Aug-03' THEN '20030801' WHEN 'Sep-03' THEN '20030901' WHEN 'Oct-03' THEN '20031001' WHEN 'Nov-03' THEN '20031101' WHEN 'Dec-03' THEN '20031201' WHEN 'Jan-04' THEN '20040101' WHEN 'Feb-04' THEN '20040201' WHEN 'Mar-04' THEN '20040301' WHEN 'Apr-04' THEN '20040401' WHEN 'May-04' THEN '20040501' WHEN 'Jun-04' THEN '20040601' WHEN 'Jul-04' THEN '20040701' WHEN 'Aug-04' THEN '20040801' WHEN 'Sep-04' THEN '20040901' WHEN 'Oct-04' THEN '20041001' WHEN 'Nov-04' THEN '20041101' WHEN 'Dec-04' THEN '20041201' WHEN 'Jan-05' THEN '20050101' WHEN 'Feb-05' THEN '20050201' WHEN 'Mar-05' THEN '20050301' WHEN 'Apr-05' THEN '20050401' WHEN 'May-05' THEN '20050501' WHEN 'Jun-05' THEN '20050601' WHEN 'Jul-05' THEN '20050701' WHEN 'Aug-05' THEN '20050801' WHEN 'Sep-05' THEN '20050901' WHEN 'Oct-05' THEN '20051001' WHEN 'Nov-05' THEN '20051101' WHEN 'Dec-05' THEN '20051201' WHEN 'Jan-06' THEN '20060101' WHEN 'Feb-06' THEN '20060201' WHEN 'Mar-06' THEN '20060301' WHEN 'Apr-06' THEN '20060401' WHEN 'May-06' THEN '20060501' WHEN 'Jun-06' THEN '20060601' WHEN 'Jul-06' THEN '20060701' WHEN 'Aug-06' THEN '20060801' WHEN 'Sep-06' THEN '20060901' WHEN 'Oct-06' THEN '20061001' WHEN 'Nov-06' THEN '20061101' WHEN 'Dec-06' THEN '20061201' WHEN 'Jan-07' THEN '20070101' WHEN 'Feb-07' THEN '20070201' WHEN 'Mar-07' THEN '20070301' WHEN 'Apr-07' THEN '20070401' WHEN 'May-07' THEN '20070501' WHEN 'Jun-07' THEN '20070601' WHEN 'Jul-07' THEN '20070701' WHEN 'Aug-07' THEN '20070801' WHEN 'Sep-07' THEN '20070901' WHEN 'Oct-07' THEN '20071001' WHEN 'Nov-07' THEN '20071101' WHEN 'Dec-07' THEN '20071201' END ,'3' )
SET @Counter = @Counter + 1 IF @cOccurance > @Counter FETCH NEXT FROM @AskDoc INTO @cClientID, @cMonth, @cOccurance END CLOSE @AskDoc --INSERT INTO [Transactional$New] (ClientID, [ Date], [ Occurance]) SELECT * FROM @MyTable DEALLOCATE @AskDoc
I want the Cursor to insert the values into the table that i commented out, but it doesn't seem to insert the values, it just loops.
We have a table that needs to be populated with a Foreign Key and an image file that matches it. The image file is named the the person's SSN which matches the FK.
Is there a way I can use the name of the file (123456789.jpg) to import it into the database or is there a better way?
I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.
I have a temp table named getInvoice where I have five important columns
1. invoice number 2.group 3.invoice status 4. Invoice Expiration date 5. Creation date time
and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.
An example is below :
InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime
My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.
The query I wrote is below
declare myData cursor for select distinct invoiceNumber from #getInvoice declare @invoiceNumber varchar(30) open myData fetch next from myData into @invoiceNumber while @@FETCH_STATUS = 0
I have nearly 1000 table. I need all the table structure in word document. Is it possible to take table structure using Stored Procedure. If it is How to do that.
DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur DEALLOCATE DBCur
Part 2
SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is, how to join the part 1 n part 2? is there posibility?
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
I have a stored procedure that contains a cursor. Right now, the table name for the cursor is "hard-coded". I would like to be able to "soft-code" the table name, because the table name may vary (although the fields would always be the same).
Is this even possible?
The only workaround I can think of is to use an EXEC on a real-time built SQL statement into a temp table and then cursor through the temp table.
I have a task of rewriting all the old procs which were build on cursors, the management does want me to remove the cursor completely since that would mean rewriting everthing.. they want to use a temp table and run a cursor on the temp table. Since there are many cursors in each proc. Any suggestions on to go about it. I am against the use of cursor any everywhere in it , even if it calls for rewriting the whole db again. I did some tests on running the old proc and the semi new proc , which runs a cursor from the temp table for the cursor query. And it even bad than the cursor only. what does this mean? Is it really a very bad idea to run cursor on a temp table?
I want to write a stored procedure in which I will update the all calendar week values (stored in a table). And to achieve this I will have to loop through all the records present in calendar table.
I am just wondering that should I use Cursor or TEMP table (caz of performance issues)?
As it will be part of data warehouse so all the processing will be carried out at SERVER ( means no client)
Can any one tell me that which would be the best solution ( Cursor or Temp table) in my case and WHY?
Hi, I need a small help. In my stored procedure, i create a table variable and fill it with records based on my query. The ID field within the table is not continous and can have any value in increasing order .e.g. The ID sequence may be like 20, 33, 34, 59, 78, 79... I want to iterate through each record within the table but without using a Cursor. I want to use a loop for this purpose. There are many articles pointing out how to iterate through records in a table variable but that requires the IDs to be continous which is not possible in my case. Can anyone help me solve this problem... Any help is appreciated...
Open a cursor , Fetch the record , during this kind of operation , will the specific table be locked and fail to be updated or select by another session ?
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'.
if @sstartzoneid<> 0 DECLARE Tablecursor FOR <-------> ( error here ) select status, sum(cost) FROM "breaktable " + @sstartzoneid where breakdate=DATEDIFF(day,'08/12/1960','03/29/2003') group by status
DECLARE @id VARCHAR(10) DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR SELECT [ServersList] AS 'ID' FROM dbo.Servers
[code]...
How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.
We use a lot of virtual machines. I have a base VM with SQL 2005 installed. I rename this VM (i have multiple copies running) and run an old application (VB code, iterates through recordsets, etc.).
I get: Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server '2K3VM-DG' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
This makes sense, and I can fix it with sp_dropserver / sp_addserver [local]. Good.
The next error is puzzling though:
[Microsoft][ODBC SQL Server Driver][SQL Server]The cursor does not include the table being modified or the table is not updatable through the cursor.
DECLARE @AssessBenefitID INT DECLARE @ActualQuantity INT DECLARE @ExpectedQuantity INT DECLARE @ActualQuality VARCHAR(2000) DECLARE @ExpectedQuality VARCHAR(2000) DECLARE @AssessFlag CHAR DECLARE CUR_BENEFIT CURSOR FOR SELECT AssessBenefitID,ProjectBenefitID,AssessFlag FROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID
OPEN CUR_BENEFIT
FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @ActualQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ExpectedQuantity=Quantity FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ActualQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='A' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID SELECT @ExpectedQuality=Quality FROM dbo.PMPT_AssessBenefit WHERE AssessFlag='E' AND AssessBenefitID=@AssessBenefitID AND ProjectBenefitID=@ProjectBenefitID
I have searched the net for an answer but could not find one. When I declare a table variable and then try to insert fetched row into the table variable like:
Code Snippet declare @table table (col1 nvarchar(50), col2 nvarchar(50)) declare curs for select * from sometable open curs fetch next from curs into @table
I would like to 'one table' record to separate 'two or three tables' . I just know use the DTS , try to import and export again and agian. So trouble.
Could you give me some suggestions for me? For example , 'Cursor' write in new table . But I try to SQL Server Books Online which is not suitable for me solving problems. One table separate two or three tables. Can you wirte the detail example for me? Thx a lot.
I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out.
I need to call a stored proc for each of the IDs above. Our existing code which has a cursor to loop through the table and call the proc for each value is proving to be a performance nightmare. Is there an alternate method that I can use to avoid cursor and make it more efficient?
