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

SQL, How To Include Recs....

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

Last 100 Recs!!! Does This Make Sense

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

DTS Export To Excel Too Many Recs?

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

Delete Double Loaded Recs...??

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

Loading In .txt File W/ Master/detail Recs

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

Count Table Recs = To System Date

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

Redirect Rows Move Good And Bad Recs To Bad Table.

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

COMMIT TRAN Does Not Commit

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

1000 To 1,000

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

Converting 1000 Into 10.00

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

First 1000 Rows In 6.5

Oct 15, 2001

How to select the first 1000 rows from the tbale in sql server 6.5..?

View 1 Replies View Related

1000 + Databses!!

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

DB Table With 1000 Columns?

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

How Do I Add 1000+ New Users To ASPNET_DB?

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

SCHEMA -- 1000 Products With Different Attributes

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

EM - Open Table - Return Top... -1000

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

INSERT Same Record 1000 Times

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

Enterprise SQL Projects (1000+ Stored Procedures)

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

It Takes 2 Seconds To Transfer 1000 Records Using DTS.

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

SQL 2012 :: Log File Viewer - Can See Only 1000 Records

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

Calculating Admits Per 1000 From Two Data Sets

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

SQL Server Agent Job Log With 1000 Rows Limitation?

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

Transact SQL :: Primary Key Auto Incrementing By 1000 Instead Of 1

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

Active Directory 1000 Rows Limitation

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

Want Insert 1000 Rows Into SQL Tables Through Application

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

DWH Problem: Updating A Table With Every 1000 Records A Checkpoint

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

Transact SQL :: Query To Insert Over 1000,0000 Record

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

DB Design :: Database XYZ Has More Than 1000 Virtual Log Files Which Is Excessive

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

Outputting Large Rows (>1000 Chars) Via Osql Or Isql?

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

Table Set Of Records - Show Purchase Time When It Crossed 1000

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

Active Directory Query 1000 Page Size Limitaion

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

How Do I Make 30 Sec Running Query (select C1 Sum(x) From T1 Where C1 &&> 1000 Group By C1) Run Faster?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved