Commit After 1000 Recs
Aug 22, 2002
if l want to commit the transactions after every thousand how would l build it into the script?
Begin Transaction
Select a.AccountNo,
a.TransactionNo,
a.TransactionAmount,
a.TransactionDate
Into dbo.test1
From Trans_May_14Aug2002 a,Reds_JuL_Trans_08Jul2002 b
Where ltrim(rtrim(left(a.AccountNo,20)))=ltrim(rtrim(lef t(b.AccountNo,20)))
AND
ltrim(rtrim(left(a.TransactionNo,20)))=ltrim(rtrim (left(b.TransactionNo,20)))
AND
a.TransactionAmount=b.TransactionAmount
AND
a.TransactionDate =b.TransactionDate
AND
ltrim(rtrim(left(a.Product,20))) IN ('PR060','PR061','PR091',
'PR096','PR111','PR121',
'PR122')
AND ltrim(rtrim(left(a.Transactiontype,20))) IN
('TR001','TR003','TR011',
'TR013','TR027','TR028',
'TR042','TR043','TR044',
'TR045','TR998','TR999')
AND ltrim(rtrim(left(a.journaltype,20))) NOT IN
('JT000','JT720','JT721',
'JT722','JT723','JT725',
'JT726','JT729','JT730',
'JT737','JT738','JT739',
'JT740','JT743','JT746',
'JT751')
OR ltrim(rtrim(left(a.JournalType,20))) IS NULL
AND a.TransactionDate > '2002-04-30'AND b.transactionDate < '2002-07-01'
Commit
View 1 Replies
ADVERTISEMENT
May 21, 2004
Need to find a way to do this. I have two tables, One with codes i.e. S,R,T and other with transactions looks like this:
Transaction table
Emp Trans Tot...
X55677 S 8
X55677 R 2
C22887 S 4
C22887 T 3
F66889 S 9
F66889 R 4
F66889 T 3
Code table
S
R
T
Not sure if the code table even helps. What I want to do is show all three transactions for each employee even if they don't have any (would be zero) like so...
Emp Trans Tot...
X55677 S 8
X55677 R 2
X55677 T 0
C22887 S 4
C22887 T 3
C22887 R 0
F66889 S 9
F66889 R 4
F66889 T 3
How can I get to this. I'm sure there must be a solution already posted for something like this but I'm not sure what to search for.
Thanks for your thoughts.
View 2 Replies
View Related
Feb 20, 2002
l've written a cursor to koop through a table and then insert the last 100 records into a table.Reason why l want the last 100 records is to monitor and log the last 100 trans avery hr or so.
-- Declare the variables to store the values returned by FETCH.
SET ROWCOUNT 100
DECLARE
@customer_No char(15),
@loan_No char(12),
@date_Issued datetime ,
@maturity_Date datetime ,
@status int
DECLARE loan_cursor CURSOR FOR
SELECT customer_No,
loan_No,
date_Issued,
maturity_Date,
status
FROM loan
OPEN loan_cursor
-- Perform the first fetch and store the values in variables.
FETCH NEXT FROM loan_cursor
INTO @customer_No,
@loan_No,
@date_Issued ,
@maturity_Date,
@status,
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM loan_cursor
INTO @customer_No ,
@loan_No ,
@date_Issued ,
@maturity_Date ,
@status
END
CLOSE loan_cursor
DEALLOCATE loan_cursor;
insert into Loan1
(customer_No,
loan_No,
date_Issued ,
maturity_Date ,
status
)
select @customer_No,
@loan_No,
@date_Issued,
@maturity_Date,
@status
FROM loan
ORDER BY date_Issued desc;
View 3 Replies
View Related
Jan 15, 2004
Hi All,
I'm trying to export around 115,000 rows from ms sql 2000 into Excel 2000, using the manual process. I just need this one time dump.
I am able to successfully export around +- 65,000 rows, but the operation fails after that.
I need to be able to get all the rows out, so using TOP obviously doesn't work.
Is there some "version" or modified way to use TOP to get...say, rows 65,000 to 90,000 then 90,001 to 115,000 ?
This would'nt be an issue if the db I'm working with was MySQL...I'd just use the LIMIT function and pull out 3 different chucks. Is there anything similar to LIMIT...or something converse to TOP in MS SQL? Or perhaps another way to dump the table then export it all (or in portions) into Excel?
Thanks!
View 7 Replies
View Related
Sep 11, 2000
Hi,
I'm using SQL Server 7.0. I have a table which has had some records double loaded into it and some triple loaded. I need to delete all instances of a record except for 1. The records are identical with no unique identifier. Here is what I'm talking about.
Currently in the table is:
ID DATE STATUS
1 07/07/2000 D
1 07/07/2000 D
1 07/07/2000 D
2 07/07/2000 A
2 07/07/2000 A
What I need in the table is:
ID DATE STATUS
1 07/07/2000 D
2 07/07/2000 A
Does anyone know how I might be able to do this?
Thanks in advance,
Darrin
View 2 Replies
View Related
Feb 19, 2004
SQL Server 2000
Help! Surely this has happened to others before me. A new customer wants to send updates in a fixed-width txt file in which master and detail rows alternate.
How do you do this?
Do you:
1) Painfully muck around with 100,000 rows in the .txt file first--split it into 2 files, one for master records and one for detail records and then process? If so, what do you use? I'd probably hack at it with a VB Script module in a DTS package.
or
2) Is there a way to feed it into 2 tables where rows starting with x go to one table and rows starting with y go to another?
The plan was to use a fast and dirty DTS package to shove this stuff into a table (probably 2 but we might just toss the stuff we don't need and put in in one) but I'd like some advice on how to proceed.
Thanks for any suggestions!
View 14 Replies
View Related
Mar 27, 2008
I need to count the records in a table with a datetime field equal to system datetime. It looks like it is trying to match time also since time is in field too. I just want to match date only. My sql is below. Does anyone have some suggestions on how to handle this?
Date from code
DateTime todaydt = DateTime.Now;
Table Data format
[cal_str_tm] [datetime] not null,
Data in cal_str_tm field - 3/27/2008 9:43:16 PM
Thanks,
Ron
ALTER PROCEDURE SP_DpCount
(
@todaydt datetime
)
AS
SELECT @total = COUNT(cal_int_id)
from dpcalldtl
where (cal_callstat != 'COMPLETED' and cal_str_tm = @todaydt)
RETURN
View 5 Replies
View Related
Feb 28, 2007
I use a redirect row method for error of OLE DB Destination For sqlServer2005.
For some resone even though only 1 record has error and should be redirect, all the record in the current batch (I think depending on the "maximum insert commit size") are redirect. the only way for me to get the exact bad record is to set the above parameter to 1, but then it takes hours to run the package.
also I always get the same error in the errorCode column - " -1071607685"
why???
View 8 Replies
View Related
Mar 28, 2008
Microsoft SQL Server Management Studio Express
select @@trancount
begin tran
select @@trancount
use ProdNetPerfMon
select @@trancount
update Nodes set Caption = 'xxxx' where Vendor = 'yyyy'
select @@trancount
commit tran
select @@trancount
Executes as expected including trancount without errors.
Nodes.Caption is updated but reverts after a few minutes.
sa privileges
What am I missing?
View 1 Replies
View Related
Aug 25, 2004
Does anyone know how to convert the number 1000 to appear as 1,000 in a SQL Statement?
View 4 Replies
View Related
Oct 17, 2005
I have been given a Product table whoes all column types are varchar(8000)
One of the column is Price and other is DecimalPosition. Price column includes price without any decimal place and the data in DecimlaPosition column determins where the decimal should be placed.
So for instance, if the Price column includes '1000' and DecimalPosision includes '2' >> then it means that the actual price for this product is '10.00' and NOT '1000'. Similarly, if the DecimalPosision includes '3' >> then it means that the actual price for this product is '1.000' and NOT '1000'My question is that when I am getting the price for a product from this table, how can I get the price in the correct format, e..g like '10.00' and not '1000'Should I use SQL statements to convert 1000 into 10.00 or should I use some sort of programming logic to convert 1000 into 10.00.kind regards
View 2 Replies
View Related
Oct 15, 2001
How to select the first 1000 rows from the tbale in sql server 6.5..?
View 1 Replies
View Related
May 22, 2001
I have started to install a 3rd party web-based product for our clients that uses SQL 2000 as its backend
However every time that they create a new 'topic' within the web app, it creates a new database, with a single table in it!! - There could/will be 1000's of these 'topics' created
I have told the company we buy this from that this is not acceptable - they have asked why!!
Can anyone point me in the direction of preferably a Microsoft document that I can send to them, as just saying 'you just don't do it that way' isn't working and I can't find anything easily myself
Many thanks
View 2 Replies
View Related
Feb 15, 2008
I was wondering if it is possible to have a DB table with 1000 columns?
The other way is of course to break these columns into 1000 rows and an ID which tells what exactly does it relate to.
I want to know the pros and cons of having 1000 columns/rows for one set of related data.
The reason to need 1000 columns in the first place is that there are about 1000 questions in a set whose answers need to be saved for one session (hence all should go together).
Can anybody shed some light on it? Has anybody tried something so crazy before?
View 1 Replies
View Related
Sep 25, 2006
Ok some company has handed me this .xls file containing a 1000+ users -- their emails (which are to be their user names), and their passwords. Both are in plain text format. I want to add these users to the ASPNET_DB, with the condition that the passwords and userids are encrypted, as they are in the table.
How should I do this?
Thanks very much.
View 3 Replies
View Related
Jul 20, 2005
[crossposted]Hi, I wonder if anyone might lend me a brain.I have a stock database to build that covers over 1000 products, whichmight be said to exist in around 50 product families.Obviously, just to be awkward all the types of stock will havedifferent attributes. So one product might be a tube withinside/outside diameter and length and another a T shaped cable joint.All I can come up with is a separate table for each stock type familyand store the table name and product code in the main stock table, so:Tables:ProdAProdBProdCStockStock attributes:ProdIdProdTableAmountDateetc..ProdA attribute:ProdIdAttributeXAttributeYAttributeZetc..Then use code to parse the table and product ID to select the correctquery to get the product details. BUT This seems awefuly inelegant andpotentially wrong so I'm loathe to continue down this route.Can anyone tell me the "right" way to do this, I feel sure it must bea classic db design exercise, but unfortunatly one they didn't teachus at University -- or maybe I was asleep...Thanks!
View 4 Replies
View Related
Jul 20, 2005
Is there a way to change the Open table - Return Top... -1000 defaultto something like 10. It should return only 10 by default? Any registrykeys?
View 1 Replies
View Related
Jan 14, 2008
Ok, so I have a primary table that contains the count of a linked table. Since I can let the identity update itself, I should be able to insert the same values into the linked child table.
I need to put the same record in the child table 1000 (that's an arbitrary number, this will be programatically determined by the user) times.
I understand that I can do this:
INSERT INTO SomeTable (Cols) VALUES (vals)
GO 1000
However, I can't make this work when doing an ADO.NET ExecuteCmd. It doesn't like the Go 1000 part of it.
Does anyone have an idea how I can do this VERY QUICKLY without having to execute a separate insert for every item (or batch them)?
The number could be as high as 250,000 in the child table.
Thanks!
View 16 Replies
View Related
Nov 5, 2003
Enterprise SQL Projects
---------------------------
When Design is replaced with an Architectural Plan
The following post is intended as a starting point of some main concepts to consider when dealing with ent. sql projects. While it is not a direct question of any kind, it would interest people that are/or was involved in ent. projects and therefore have been troubled with similar problems.
Here is a quick overview of a couple main concepts when you have to deal with a Ent. Projects with 1000+ stored procedures.
DOCUMENTATION:
It is an absolute must to include 100% explanatory code on top of the sps.
FUNCTIONS:
Use functions to the maximum extent to reduce overal stored procedure complexity
a rule of thumb is to have 1 to 10, functions to sps ratio or simmilar.
TRIGGERS:
A lot to say about them that cannot be covered in this context
NAMING CONVENSION:
Your naming convension should be 100% pre-thought and designed, no mistakes allowed in this context as it will cause all stored procedures to be extremely difficult/impossible to browse.
a quick template could look as this:
sp
module name
underscore(_)
action (lower case)
noun (proper case)
For example:
spOrders_putOrderDetail
spMaintainUsers_deactivateUser
spReports_getZeroInventory
(quoted by: tmorton)
my addition to this would be something like:
sp< as a prefix is surtently an overkill when dealing with 1000+ sps and is not needed.
however a lot more complex naming strategies can be used, that will cause the project to be a lot more easy to maintain.
View 4 Replies
View Related
Mar 27, 2001
I am trying to transfer 90 million records/250 bytes row length from oracle 8i to sqlserver 2000
using DTS and it is taking 2 seconds to transfer 1000 records. Is there any way I can transfer 90 million records fast at all. This will take more than 10 hours to transfer it.
Thanks,
Ranjan
View 7 Replies
View Related
Mar 9, 2013
Using the Log file viewer in sql auditing I can see only 1000 record....How can we see more than 1000 records or earlier data...
View 6 Replies
View Related
Aug 27, 2013
I have two queries that generate two different datasets. One is a count of memebers, and the other is count of admits. I need to generate a calculated field from the two data sets called admits per 1000, which is essential the count of admits/counts of members *12000 I was able to calculte admits per 1000 easily in excel, however I need some insight on how to do is SQL.
Below are my queries from the two datasets.
MemberMonths dataset:
Select
factMembership.BusinessUnitCode,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership
[Code] ....
Admits dataset:
SELECT
Factadmissions.BusinessUnitCode,
factAdmissions.AdmitCCYYMM,
ISNULL(Count(AdmitNum),0)As [Count of Admits]
FROM factAdmissions
[Code] ...
View 6 Replies
View Related
May 11, 2007
Hello,
It seems that there is some kind of limitation in SQL Server agent job log. For any job there is not more than 1000 rows of history. This can be seen with the following query:
SELECT j.name, count(*)
FROM sysjobhistory jh,
sysjobs j
WHERE jh.job_id = j.job_id
GROUP BY j.name
HAVING count(*) >= 1000;
How to modify the limitation? We really need more than 1000 rows.
r,
J
View 5 Replies
View Related
Jul 13, 2012
We have upgraded our SQL 2008 server to 2012 last month. i've noted since then that many tables that have auto increment primary key bigint field increases by 1 like it should, then for some reason it jumps up by 1000 or even 10000? i have seed adn auto increment set to 1 but doesnt effect it. Is there anything that could be causing the next value to jump that much?
View 18 Replies
View Related
May 8, 2006
we are using
select distinguishedname, employeeid, sn, middlename, givenname, displayname, samaccountname, mail, cn, telephonenumber
from OpenQuery( ADSI, 'SELECT telephonenumber, distinguishedname, employeeid, sn, middlename, givenname, displayname, samaccountname, mail, cn, telephonenumber
FROM ''LDAP://DC=domain,DC=xxx,DC=xxx''
WHERE objectCategory = ''Person'' AND objectClass= ''user''
But it only returns 1000 rows which i read all around that is the default.
How do you set this to be higher.
Thanks
View 1 Replies
View Related
Mar 16, 2007
Hi
I have an PL/SQL procedure @ Oracle database which extracts 10000 rows from a table and Now I have load all the 1000 rows into SQL 2005 tables.
I have extracted the data from oracle into DataAdapter/dataset , Now I want to load all the rows to SQL 2005 tables. Please help how I can load..
If I use insert statement everytime , it makes server busy and takes much time for 10000 inserts to complete(even using Procedure goes heavy since for every insert have to call this).
Is there any possibility that i can pass the REF Cursor / Dataset/dataAdapter into SQL stored so that inserts will have happen all together ??
Thanks in advance for ur help.
Regards:
Nanjappa
View 3 Replies
View Related
Jul 20, 2005
Hi,Currently we're a building a metadatadriven datawarehouse in SQLServer 2000. We're investigating the possibility of the updatingtables with enormeous number of updates and insert and the use ofcheckpoints (for simple recovery and Backup Log for full recovery).On several website people speak about full transaction log and thepace of growing can't keep up with the update. Therefore we want tocreate a script which flushes the dirty pages to the disk. It's notquite clear to me how it works. Questions we have is:* How does the process of updating, insert and deleting works with SQLServer 2000 with respect to log cache, log file, buffer cache, commit,checkpoint, etc?What happens when?* As far as i can see now: i'm thinking of creating chunks of data of1000 records with a checkpoint after the Query. SQL server has thedefault of implicit transactions and so it will not need a commit.Something like this?* How do i create chunks of 1000 records automatically withoutcreating a identity field or something. Is there something like SELECTNEXT 1000?Greetz,Hennie
View 6 Replies
View Related
May 8, 2015
How to write a query that can insert over 1000,0000 dummy records in the fastest way? My current query is
DECLARE @d DateTIme = GETDATE()
DECLARE @c INT = 1
WHILE @c <= 5 BEGIN
INSERT INTO MyTable VALUES (NEWID(),'PREFIX' + RIGHT('0000000000'+ (CAST(@c AS VARCHAR)), 10), DATEADD(MINUTE,@c,@d), FLOOR(RAND()*3), FLOOR(RAND()*2), 'INFO')
SET @c = @c + 1
END
View 7 Replies
View Related
Jun 8, 2015
I am getting this massage in error log .
"Database XYZ has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files."
I am using sql server 2008r2.
View 5 Replies
View Related
Jan 25, 2001
Iam attempting to generate files containing more than 1000 characters per line by outputting the results of a stored procedure via osql to a flat file. Osql (and isql) appear to force a newline after 1000 characters, even when specifiying a -w2000 parameter.
I have also tried to output the results of the stored procedure via DTS and this appears to do the same thing!
Does anybody know how to prevent osql (or isql) from forcing the newline?
View 1 Replies
View Related
Feb 10, 2014
I have a table set of records. Its contains some customerID,SportsGoods,Price in different datetime. I want to add customer spent. If crossed 1000 means i have to show purchase time when it is crossed 1000. I need query without while and looping.
Example:
Customer NameGoodsPriceDatePurchased
ABat2501/31/2014
ABall221/31/2014
BCarrom Board4752/2/2014
CTennis Ball502/1/2014
AFootball1502/2/2014
DBat2501/31/2014
BBall221/31/2014
AHockey Bat1252/4/2014
CChess552/4/2014
AVolley Ball552/4/2014
View 9 Replies
View Related
Jul 23, 2005
Hi.We need to create a view of our active directory users (we have 2500).I found out that there is max page size of 1000, so we cannot get moredata.Anyone found a solution to that problem?Thanks
View 1 Replies
View Related
Aug 10, 2007
It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.
Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?
if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?
Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?
Thanks in for you help in advance
View 1 Replies
View Related