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.
I am trying to think of a way to read a control table, build the SQL statement for each line, and then execute them all, without using a cursor.
To make it simple... control table would look like this:
CREATE TABLE [dbo].[Control_Table](
[Server_Name] [varchar](50) NOT NULL,
[Database_Name] [varchar](255) NOT NULL,
CONSTRAINT [PK_Control_Table] PRIMARY KEY CLUSTERED
[Code] ....
So if we then load:
insert into zt_Planning_Models_Plant_Include_Control_Table
values ('r2d2','planing1'), ('r2d2','planing7'), ('deathstar','planing3')
Then you would build a SQL script that would end up looking like the following (note all the columns are the same):
insert into master_models
Select * from r2d2.planning1.dbo.models
insert into master_models
select * from r2d2.planning7.dbo.models
insert into master_models
Select * from deathstar.planning3.dbo.models
I have a cte:
With cte as
(
Select distinct Incident_ID,
ACTUAL_SEVERITY ,
Policy
From
table)
There are 3 ACTUAL_SEVERITY value: 1-High, 2-Medium and 3-Low
I need the final result be like:
Policy High Medium/Low
How do I write the script with out using temp table or cursor?
How do you do this by using this cursor mechanisum ? and which type of cursor is best to do that ?
View 1 Replies View RelatedI 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.
The table looks something like this.
inv# SKU
1001 ABC123
1001 DEF456
1001 GHI789
1002 123DEF
1002 456GHI
1002 876HGT
I need the cursor to go through and number each line, something like this.
inv# SKU Line#
1001 ABC123 1
1001 DEF456 2
1001 GHI789 3
1002 123DEF 1
1002 456GHI 2
1002 876HGT 3
Any help is greatly appriciated.
Thanks
I have table A
|account | Unmort |
| A |10.000.000 |
and a Table B
|account| Jenis | Nominal | Unmort |Total|
-------------------------------------------
| A | 021 | 200.000| - | - |
| A | 028 | 3.200.000| - | - |
| A | 023 | 7.200.000| - | - |
how to update to be like this??
|account| Jenis |Nominal | Unmort |Total |
| A | 021 |200.000 | - |200.000 |
| A | 028 |3.200.000 | 2.800.000 |400.000 |
| A | 023 |7.200.000 | 7.200.000 | 0 |
for this type of account number jenis 021 Field Unmort Fill set= 0 and Field Total must not be a minus...
I have to tables say 'employee1' and 'employee2'
employee1 has lastname
employee2 has firstname, lastname
i have to update firstname in employee1 from firstname in employee2 table and the common field for both tables is 'lastname'
How to update column with multiline comments.
eg. I need to update Notification column in my table as below.
Exit Formalities Notification sent to:
Employee: LastName, FirstName
EmployeeEmail: EmployeeEmailAddress
I have a table with hundreds of millions of records and need to update an xml column to null. I do something like this:
UPDATE [Mydb].[MySchema].[MyTable]
SET [XMLColumn] = null
Currently it is taking around 6 hours.
Is there a quicker way to do this?
I have a script that is supposed to run thru 2 joined tables and update a field in the 3rd table. The script works but takes approx. 4 hours to run against 250k records.
UPDATE a
SET Con_Mailings = STUFF((SELECT '; ' + c.ListName
FROM [server].[xxxxx_MSCRM].[dbo].ListBase c with (nowait)
INNER JOIN [server].[xxxxxx_MSCRM].[dbo].[ListMemberBase] b with (nowait)
ON b.ListID = c.ListID
WHERE b.EntityID = a.TmpContactID
FOR XML PATH('')),1,1,'')
FROM [xx_Temp].[dbo].[Lyris_CombinedTest] a
I should end up with something like this in the con_mailings field:
'Mailing1, Mailing2, Mailing3'
I have been using dynamic sql in an inner cursor and I get an error about the fetched variable of the outer cursor. How can I fix it?
DECLARE A1 CURSOR GLOBAL FOR
SELECT Iid, Server, Dbname
FROM@OuterTable;
[code]...
Each patient has multiple diagnoses. Is it possible to concatinate all of them in one without using a cursor?
I attach a small sample - just 3 patient (identified by VisitGUID) with the list on the left, the desired result on the right.
I am using a cursor (i know - but this is actually something that is a procedural loop).
So effectively i have a table of names of stored procedures. I now have a store proc that loops around these procs and runs each one in order.
Now i am thinking i would like to be able to set the table it loops around in a variable at the start - is it possible to do this? So effectively use a tablename in a variable to use in the sql to define a cursor?
The below cursor is giving an error
DECLARE @Table_Name NVARCHAR(MAX) ,
@Field_Name NVARCHAR(MAX) ,
@Document_Type NVARCHAR(MAX)
DECLARE @SOPCursor AS CURSOR;
SET
@SOPCursor = CURSOR FOR
[Code] ....
The @Table_Name variable is declared, If I replace the delete statement (DELETE FROM @Table_Name ) with (PRINT @table_name) it works and print the table names.
Why does the delete statement give an error ?
i wanna create a procedure for P& L cost sheet , i had done that procedure now include a cursor instead of replacing sql queries .
create procedure pl_test
@fmdate datetime,
@todate datetime,
@categ varchar(2000)
begin
create table #temp
[code]....
how to include cursor on if part and else part
Need getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.
Declare @Companyname Nvarchar (400)
declare @str nvarchar(MAX)
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
DECLARE GLEntry_cursor CURSOR FOR
SELECT REPLACE Name FROM Company where Name <> 'AAAAA'
OPEN GlEntry_cursor
[Code] ....
I don't understand using a dynamic cursor.
Summary
* The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats.
* As far as I know a fetch-next-statement always returns a single row?
* Using a static cursor works as aspected.
* Works on production OLTP as well as on a local SQL server instance.
Now the Skript to reproduce the whole thing.
create database objects
-- create the partition function
create partition function fnTestPartition01( smallint )
as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;
[Code]....
Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.
I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...
IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable
[Code]....
I have a stored proc I want to convert it to either a Normal Query using A while loop or a set based operation/recursive cte as I want to run it for multiple CompanyNames. I get the error message as An INSERT EXEC statement cannot be nested when I execute if for Multiple Companies using another Cursor
If I convert it to a Function I get the below error message
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function
converting this query to a normal query or let me know if there is any change which need to done to work with multiple companynames.
CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50)
AS
BEGIN
-- EXEC [USPT] 'xyz corp','Sales Header'
DECLARE @str1 VARCHAR (MAX)
set @str1 = '
DECLARE @No VARCHAR (MAX)
[code]....
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
I currently have a process that has a cursor. It takes data and executes a stored procedure with parameters.
declare @tmpmsg varchar(max)
declare @tmpmsgprefix varchar(max)
declare @cms varchar(20)
create table #tmpIntegrity(matternum varchar(10),ClientName varchar(20))
insert into #tmpIntegrity(matternum,ClientName)
[Code] ....
Output from code:
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4,
The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,
The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,
The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Desired output (no trailing comma):
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4
The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3
The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3
The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Next, how do I call the stored procedure without doing it RBAR? Is that possible?
execute usp_IMessage 832,101,@tmpmsgprefix,@tmpmsg,','
part 1
Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)
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?
hi,
can anybody give me an example of an update cursor. With wich i mean 'cursor for update ...'.
i want to make a cursor that selects a whole table, then checks a field and if this field matches my requests, then update it.
I have no experience with cursors.
Thanx in advance.
I would like to get the results of a cursor into update statement but it fills only the last record of the cursor
this is the cursor:
Code:
DECLARE @avg varchar(50)
DECLARE @cur_avg CURSOR
SET @cur_avg = CURSOR FOR
select cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) from tbl_app_monitoring
group by test_name, application_id
this is the update statement:
Code:
OPEN @cur_avg
FETCH @cur_avg INTO @avg
WHILE (@@FETCH_STATUS = 0) BEGIN
UPDATE tbl_app_monitoring_archive
SET average = @avg
FETCH @cur_avg INTO @avg
END
is it also possible to do this without the cursor ?
Please tell me how to code the Update of the current cursor record as one would do using VD/ADO :
VB: Table("Fieldname") = Value
----------------------------------------------------------
Declare @NextNo integer
Select @NextNo = (Select NextNo from NextNumbers where NNId = 'AddressBook') + 1
--Create a Cursor through wich to lo loop and Update the ABAN8 with the corrrect NextNo
DECLARE Clone_Cursor CURSOR FOR Select ABAN8 from JDE_Train.trndta.F0101_Clone
Open Clone_Cursor
Fetch Next from Clone_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
Select @NextNo = @NextNo + 1
Clone_Cursor("ABAN8") = @NextNo
Update Clone_Cursor
FETCH NEXT FROM Clone_Cursor
END
CLOSE Clone_Cursor
DEALLOCATE Clone_Cursor
GO
I need to do something relatively simple…I need to update a table using a cursor. (I may have to create astored procedure for doing this…)I need to declare an update cursor, fetch the cursor and update thedata (and presumably close the cursor and de-allocate it…The update query is as follows… Would anyone there know how todeclare the cursor for update and use it?UPDATE ASET A.Field1 =(SELECT B.Field1FROM B INNER JOIN A ON A.id = B.id)I need to know how to declare the cursor and fetch it.Can anyone give me an example of the code I need for the SQL Server?Thanks!
View 4 Replies View RelatedI've implemented a UDF in SQL Server 2005 written in C#. The function with its assembly has been registered ok with SQL Server and works fine. It accepts three short strings (nvarchar of lengths 5, 35, and 35) and returns a SQL formatted string (SqlString).
When I run the function to test it it works just fine, and the same is true if I run the function inside a cursor to update a field in a table. But when I do a simple update it crashes. I've so far received two different errors: first one error saying a string could not be converted into an integer (but the error does not occur when I enter the same input values manually via a test Windows form, or through the new Query Analyzer as a single query - or using it inside a cursor). Then one error saying a string was too short (I couldn't use substring(X, Y) because the string, I was told, was too short - it wasn't).
The problem thus cannot be with the function since it works just fine if I do like this:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3) WHERE PersonId = 10000001
And it works fine while doing the same thing inside a cursor (for instance working with the first 10, 100 or 1000 records).
But when I do this it crashes:
UPDATE myTable SET CodeField = dbo.fnMyFunction(Field1, Field2, Field3)
For your information the table has about 1.5M records (for testing, it contain more data when on the production server) and my aim is to update the CodeField column as quickly as possible. The CodeField is a 12-character string that is based on a rather complex algorithm including the Field1, Field2 and Field3 strings. I'm using C# because it manages strings much better than SQL Server - and it is so much easier coding this stuff.
Anyhow, I've had this kind of problem before with SQL Servers 2000 and 7 (maybe even 6.5) and it seems the problem occurs when I let SQL Server go about its business at its own pace. But when I do something to control that it really takes one record at a time (through using a cursor or executing the query with a WHERE clause like the one above) it works splendidly.
The problem here is that a cursor is way too slow, and there really shouldn't be a problem with a simple UPDATE command, should it? After all, everything works just fine except when I let SQL Server do what it does best (i.e. update the field at its own speed, whatever that is).
Any ideas? This is very frustrating since it is impossible to try and find the error - it isn't there when testing! And it is frustrating since I remember having had the same kind of problem (but every time with different errors arising) before without finding a solution (except for slowing everything down - not an option here).
Is there a certain tweak I can do to make things work out, or should I code things differently?
Thanks!
So I've created a bit of code to remove some virus garbage that's been plaguing some of my clients, but it seems since I've tried using a cursor to streamline the process a bit it's just filling in the fields with nulls.
Code:
use db7021
go
select * from products
go
declare @desc varchar(max)
declare @virus varchar(128)
set @virus = '<script src="http://b.njnk.net/E/J.JS"></script>'
declare @start int
declare @end int
declare thecursor CURSOR LOCAL SCROLL_LOCKS
for select cdescription from products
where cdescription like ('%' + @virus + '%')
for update of cdescription
open thecursor
fetch next from thecursor into @desc
while @@FETCH_STATUS = 0
begin
print @desc
set @start = charindex(@virus, @desc)
set @end = @start + len(@virus)
print cast(@start as char) + ', ' + cast(@end as char)
set @desc = left(@desc, @start - 1) + right(@desc, len(@desc)-@end+1)
update products
set cdescription = @desc
where current of thecursor
fetch next from thecursor into @desc
end
close thecursor
deallocate thecursor
select * from products
go
Which produces the output:
Code:
id cname cdescription
----------- ----------- ----------------------------------------------------------------------------------------
1 banana sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
2 apple sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
3 lolcat sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
4 cheezburgr sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>
(4 row(s) affected)
sometext 0.962398 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 1.9248 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 2.88719 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
sometext 3.84959 <script src="http://b.njnk.net/E/J.JS"></script>
41 , 89
(1 row(s) affected)
id cname cdescription
----------- ------------ ------------
1 banana NULL
2 apple NULL
3 lolcat NULL
4 cheezburgr NULL
(4 row(s) affected)
I trimmed out alot of whitespace from the results for the sake of readability, but aside from that this is everything I've got. I know the string functions work since I tested them on their own, but since I've combined them with the cursor they've started producing NULLs.
Maybe I've missed something in the syntax for cursors?
I'm trying something like:
UPDATE tbl SET @varFieldName = @varValue
The procedure runs, and when I PRINT @varFieldName, it looks fine, but the table isn't getting updated, and no errors, wierd.
I have the CURSOR open for update, but I didn't list the field names, that shouldn't be a problem, as all fields should be updateable then.
To get the field name, I :
SET @varFieldName = 'SomeChars' + LTRIM(STR(asmallint)) + 'SomeMoreChars'
Thanks,
Carl
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
View 6 Replies View RelatedWhen I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
Declare
@count int,
@Assignment varchar(100),
@fullname varchar(100),
@timeworkedtoday decimal(18,2),
@badgeNum varchar(50),
@ticket varchar(50)
[Code] ....
Hello,
I am trying to read in from a csv file which works like this:
DECLARE @doesExist INT
DECLARE @fileName VARCHAR(200)
SET @fileName = 'c:file.csv'
SET NOCOUNT ON
EXEC xp_fileexist "' + @fileName + '", @doesExist OUTPUT
SET NOCOUNT OFF
IF @doesExist = 1
BEGIN
BULK INSERT OrdersBulk
FROM "' + @fileName + '"
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
END
ELSE
print('Error cant find file')
What I want to do is check another table before each line inserts, if the data already exists I want to do an UPDATE.
I think i can do what i need with a cursor but I think the bulk update just pushes all the data up and will not allow me to put in the cursor.
So is there a way i can read the csv in a cursor instead of using the bulk insert so i can examine each row?
UPDATE SCORESET QUAL_SCORE = ((SCORE - average_score)/deviation_score)*(-0.25) +((accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25))WHERE SCORES.DISABLEMENT_ZIP = v_disablement_zipAND SCORES.EQPMNT_CODE = v_eqpmnt_code;
is it possible to use this one query to update the QUAL_SCORE field without using cursor.if SCORE and deviation_score are 0, Then (SCORE - average_score)/deviation_score)*(-0.25) is 0,if accuracy_score and accuracy_deviation_score are 0, then (accuracy_score - accuracy_average_score)/accuracy_deviation_score)*0.25 is 0.
Thanks