Cursor From Diff Table -challenge
Apr 20, 2004
I have 17 tables with names as Breaktable1, Breaktable2,.... till
Breaktable17
i make a query like this
set nocount on
Declare @sEventtable varchar(20)
Declare @sstartzoneid varchar(20)
Declare @ssttopzoneid varchar(20)
Select @sstartzoneid ='1'
Select @ssttopzoneid = '17'
select @sstartzoneid = convert(smallint,@sstartzoneid)
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
CLOSE tableCursor
DEALLOCATE tableCursor
can somebody help ????
View 14 Replies
ADVERTISEMENT
Feb 12, 1999
I need to write a 'select' statement to fetch data from different tables, which are located on different servers.
Can any one help in writing this 'select' statement with out moving the tables on to same server.
Thanks in Advance.
Murali Raparla.
View 2 Replies
View Related
May 1, 2008
Hello I have a Source database and a Target database.
I want to join one table from the source to the other table in the target.
Please can some one write a sql query for this.
i gues its something like
select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination
Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.
View 4 Replies
View Related
Sep 24, 2007
Hi,
I have two tables as follows
Table TempTab
{
CatId varchar(20),
lastupdate Datetime
}
Table MainTab
{
CatId varchar(20),
lastupdate Datetime
}
and the data in those tables are as follows
Table TempTab
{
CatId LastUpdate
------------------------------
Cat1 D1
Cat2 D2
Cat3 D3
Cat4 D4
}
Table MainTab
{
CatId LastUpdate
------------------------------
Cat1 D1
Cat3 D3
Cat5 D5
}
I need a query to insert the differences into the MinTab, i mean to say the fincal MainTab should look like as follows
Table MainTab
{
CatId LastUpdate
------------------------------
Cat1 D1
Cat2 D2
Cat3 D3
Cat4 D4
Cat5 D5
}
can any one please let me know the query
Thanks alot
~Mohan
View 3 Replies
View Related
Sep 25, 2007
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?
View 1 Replies
View Related
Sep 25, 2014
I have two tables table1 and table2 and having a common column "col1"
When i ran the following query
UPDATE table1, table2 SET col1=FALSE WHERE id = 1;
getting the following error
Error Code: 1052
Column 'col1' in field list is ambiguous
id column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.
View 3 Replies
View Related
Dec 10, 2007
Hi Madhu,
my table does not have primary key so i created a seperate index on each of the table.
I used the recommended tablediff utility and it works successfully. But its only show the difference of records in each table and does not copy rows from source to destination and destination to source table. I was expecting database1.dbo.table1 contains same records as in database1.dbo.table2.
C:Program FilesMicrosoft SQL Server90COM>tablediff /sourceserver kashif-pcs
qlexpress /sourcedatabase AB /sourcetable table1 /destinationserver kashif-pcsq
lexpress /destinationdatabase CD /destinationtable table2
Microsoft (R) SQL Server Replication Diff Tool
Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values:
/sourceserver kashif-pcsqlexpress
/sourcedatabase AB
/sourcetable table1
/destinationserver kashif-pcsqlexpress
/destinationdatabase CD
/destinationtable table2
Table [AB].[dbo].[table1] on kashif-pcsqlexpress and Table [CD].[dbo].[table2]
on kashif-pcsqlexpress have 5 differences.
Err Sno
Src. Only 101
Src. Only 102
Dest. Only 103
Dest. Only 104
Dest. Only 105
The requested operation took 0.466767 seconds.
Can you write a short script for my problem, just like comparison of database1.dbo.table1 compares in database2.dbo.table2 and which ever records not present it should copy those and vice-versa.
It means Database1.dbo.table1 contains 5 records
Database2.dbo.table2 contains 5 records
Regards
Kashif Chotu
View 3 Replies
View Related
Mar 22, 2007
Hello,
I've been able to startup a transactional replication between 2 database for some tables that only have different table names. Now there are still some tables that not only have different names but also different column names, can this be done in the wizzard ??
Thx
View 3 Replies
View Related
Nov 14, 2007
Hi,
I have Table A . we already have 80 columns . we have to add 65 more columns.
we are populating this table from oracle .and we need to populate those 65 columns again from the same table.
Is it a better idea to add those new 65 columns to the same table or new table.
If we go for the same table then loading time will be double, If I go for new table and If i am able to run both the packages which loads table data from same oracle server to difffrent sql tables then we should be good. But if we run in to temp space issues on oracle server . Then i have to load the two tables separately which consumes the same time as earlier one.
I was thinking if there is a way in SSIS where I can pull data from same oracle table in to two diff sql tables at same time?
View 1 Replies
View Related
Jul 24, 2002
Hi, I have 2 databases, located on two different servers. One server is running sql 7 server, the other is running sql sever 2000.
When data is inserted in one table of the sql server 7 DB, I would like to automatically insert this new data into the table of the sql server 2000 db. Can I do this with a trigger and I how do I do this? Pleas help Thanks
Eric
View 3 Replies
View Related
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?
View 10 Replies
View Related
Mar 27, 2006
I am using MS-SQL Server 2K....
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.
Is there a better way to do this?
Thanks...
View 2 Replies
View Related
Aug 21, 2000
can some on tell me which is better to use. the new TABLE data type in 2000 or cursors ????
thanks
View 1 Replies
View Related
Sep 16, 2004
I just want to know which one is more efficient cursor or temp table for looping through a record set?
I am basically looking for better performance and server utilization.
View 6 Replies
View Related
May 18, 2007
Hi all,
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?
Necessity is the mother of all inventions!
View 4 Replies
View Related
Aug 2, 2006
Hi Chaps,
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?
Note: I am using SQL Server 2005 standard edition
waiting for quick reply.
regards,
Anas
View 12 Replies
View Related
Mar 11, 2008
How do you do this by using this cursor mechanisum ? and which type of cursor is best to do that ?
View 1 Replies
View Related
Aug 14, 2007
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...
View 4 Replies
View Related
Sep 8, 2003
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 ?
View 7 Replies
View Related
Aug 3, 2004
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'.
TIA
View 14 Replies
View Related
Jan 27, 2004
How do I declare a cursor on a table like #TempPerson
when thhs table is only created when I do :
Select Name, Age Into #TempPerson From Person
View 7 Replies
View Related
Oct 1, 2013
I consider CURSOR and TABLE as data types but some one has argued that these are database objects.
View 7 Replies
View Related
May 6, 2015
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.
View 1 Replies
View Related
Sep 4, 2007
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.
Any suggestions on how to fix this?
View 2 Replies
View Related
Mar 20, 2008
Following sp gives wrong result whats wrong with following cursor?
ALTER PROCEDURE [dbo].[spPMPT_GetProjectBenefitDetailsForAssess]
@ProjectBenefitID INT
AS
SET NOCOUNT ON
DECLARE @ErrorMsgID INT
DECLARE@ErrorMsg VARCHAR(200)
DECLARE @TEMP_BENEFIT
TABLE (ActualQuantity INT,
ExpectedQuantity INT,
ActualQulity VARCHAR(2000),
ExpectedQulity VARCHAR(2000) )
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
INSERT INTO @TEMP_BENEFIT (ActualQuantity ,
ExpectedQuantity ,
ActualQulity ,
ExpectedQulity )VALUES(@ActualQuantity,@ExpectedQuantity,@ActualQuality,@ExpectedQuality)
FETCH NEXT FROM CUR_BENEFIT INTO @AssessBenefitID,@ProjectBenefitID,@AssessFlag
END
CLOSE CUR_BENEFIT
DEALLOCATE CUR_BENEFIT
SELECT * FROM @TEMP_BENEFIT
View 3 Replies
View Related
Jan 28, 2008
Hello,
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
it does not work. any help would be great.
thnx
View 6 Replies
View Related
Aug 12, 2015
In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll
while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1
close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
View 3 Replies
View Related
Jul 20, 2005
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View 1 Replies
View Related
Sep 20, 2007
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.
here is the stored procedure:
Alter PROCEDURE [dbo].[sp_MSforeachsp]
@command1 nvarchar(2000)
, @replacechar nchar(1) = N'?'
, @command2 nvarchar(2000) = null
, @command3 nvarchar(2000) = null
, @whereand nvarchar(2000) = null
, @precommand nvarchar(2000) = null
, @postcommand nvarchar(2000) = null
AS
/* This procedure belongs in the "master" database so it is acessible to all databases */
/* This proc returns one or more rows for each stored procedure */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
declare @retval int
if (@precommand is not null) EXECUTE(@precommand)
/* Create the select */
EXECUTE(N'declare hCForEachTable cursor global for
SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ''PROCEDURE''
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '
+ @whereand)
select @retval = @@error
if (@retval = 0)
EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0
if (@retval = 0 and @postcommand is not null)
EXECUTE(@postcommand)
RETURN @retval
GO
example useage:
EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"
GO
View 7 Replies
View Related
Jan 28, 2005
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.
View 1 Replies
View Related
Jul 6, 2000
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.
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
View 1 Replies
View Related
Feb 2, 2002
Hi, I'm newbie in SQL, could somebody tell me how do I walk through a table one record at a time without using a cursor please.
Greatly appreciated.
Ann
View 4 Replies
View Related
Jul 15, 2014
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...
View 3 Replies
View Related