SQL 2012 :: 1.5 Million Records Into Temp Table
Sep 23, 2014
I come from a web based world were loading 1.5 million records into a temp table is suicide. I’m doing more data warehouse stuff now and I was looking into optimizing a buddies proc and noticed he was loading 1.5 million records into a temp table. We had a discussion about it because being from a web world I was drastically against it. He on the other hand didn’t feel it was an issue being it gets called once maybe twice a day. The tempdb is set to autogrow and it is on a different drive than all the other databases on the box. It has one ldf and mdf. He’s creating an index on the table after load. Why we shouldn’t be loading 1.5 million recs into temp table?
View 5 Replies
ADVERTISEMENT
Mar 12, 2015
We are facing a weird scenario in which the snapshot is getting corrupted after insertupdate few million records in to a table .
SQL Server 2012
windows server 2008 R2
service pack 1
64-bit OS
View 1 Replies
View Related
Jun 12, 2015
I have a requirement to delete 1 Million records from a table having 10 Million data and it's being queried on 24/7 basis (don't have a downtime). how can I achieve that?
View 13 Replies
View Related
Mar 25, 2014
I use code below to insert data into a temp table.
How many records can insert into a temp table?
Select * into #temp from ORDER
View 3 Replies
View Related
Nov 10, 2014
I have 2 tables with this schema
CREATE TABLE tableValues(
[LASTENCRYPTIONDT] [datetime] NULL,
[ENCRYPTIONID] [int] NULL,
[NAME] [varchar](50) NULL
[Code] ....
I want to update tableToUpdate in batches of 5000 per batch and set the lastenecryptionDT to null based on the the join to the tableValues using the column ENCRYPTIONID, and also output updated rows into another table. Incase I would need to do a rollback.
View 3 Replies
View Related
Mar 26, 2004
i have a directory database with approx. 80 million records. i am feeding the database with bulk_insert. Indexing one of the fields took about 8 hrs. After indexing when i run queries with the indexed field the response time is under 1 sec. However if i run select queries with like on non-indexed fields it takes more than 2 mins. So i decided to index 4 other fields in the database and it looks like the indexing process is going to run for 2 days.
i am a novice in SQL database design and i am not sure if this is the best way to index the table. i am just using create index. Any suggestions / advice welcome.
View 5 Replies
View Related
Jul 21, 2015
I am trying to update a large table which consists of 45 million records , it is taking more than 2 days to the update , below is my approach
1. The table has only one clustered index and no other indexes on the table.
2. I am updating in batches say 20000 record-wise.
3. Changed the recovery mode to bulk logged and auto-growth size is set to 300MB and there is enough space in my disk for transaction log .
But still the query is running slowly.
View 10 Replies
View Related
Feb 16, 2006
All,
I need to load a 50 million records table monthly. Any suggestion about the best/fast way to do it?
Thanks a lot
View 2 Replies
View Related
Aug 6, 2015
I have a table that I need to do some computations on all the data but first I need to remove the duplicate records and insert the results into a destination table. Here's the example below. My table has 3.1 million rows. I have tried using the DISTINCT and the GROUP BY but both ways to select the data takes about half a minute to run. I'm wondering if there is a way to increase performance. Users are ok with this time since the process runs overnight but improving it won't hurt. I do have a clustered index on these fields but that doesn't seem to improve any.
SELECTDateYear ,
DateMonth ,
Nbr ,
Nbr1 ,
Nbr2 ,
Datafield1 ,
Datafield2,
[code].....
View 7 Replies
View Related
Aug 11, 2014
I need to use Bulk insert statement for copying a table with 200 million rows to another table on the same server...the table has no primary key or identity column.... script for BULK INSERT ...
View 9 Replies
View Related
Nov 23, 2004
PLease help me with a intruiging problem with Stored Procedure, which is driving me mad.......
I wrote a Stored Procedure as listed below.
I've included the options
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
the results are stored in a #Temp table
The SP executes fine on Query Analyzer, and shows the contents of the temp table. BUt when I run this SP using ASP, I got the following error
-----------
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/test_site/test.asp, line 59
------------
I've tried using Recordset.MoveNextRecordset, if at all there are more than one recordset, obviously I got the same error
CREATE PROCEDURE atul_med.[sp_rep_sale2]
(
@rep_in varchar(100)
)
AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
DECLARE
@REP int,
@temp_pack varchar(20),
@temp_ntp int,
@temp_party_code varchar(20),
@temp_party_name varchar(20),
@temp_prod_code varchar(20),
@temp_prod_desc varchar(20),
@tot_qty int,
@tot_amount int,
@iCnt int,
@tot_sale_qty int,
@net_tot_sale_qty int,
@TableName varchar(10)
select @REP= @rep_in
set @tot_sale_qty=0
set @net_tot_sale_qty=0
set @TableName='#Test2'
--select @TableName='Test'
set @page=1
set @RecsPerPage=100
Declare @SQL VarChar(1000),
@cnt int,
@iCurLine int, @SQLX VarChar(1000)
set @iCurLine=1
set @iCurLine=1
select @Cnt = (SELECT count(*) FROM VW_PART_REP WHERE (REP = @REP))
print "Nos Of Party :"+ convert(char(1),@iCnt)
Create Table #Test2 (ID int identity,prod_desc nvarchar(100),pack nvarchar(100),ntp int)-- ,tot_qty int ,tot_amt int )
while @iCurLine <= @cnt
begin
SELECT @SQL= "ALTER TABLE #Test2 ADD "
SELECT @SQL = @SQL + "party_qty"+convert(char(2),@iCurLine)+" int "
set @iCurLine=@iCurLine+1
Exec (@SQL)
print @sql
end
SELECT @SQL= "ALTER TABLE #Test2 ADD "
SELECT @SQL = @SQL +"tot_qty int , tot_amt int "
SELECT @SQL = @SQL
exec (@SQL)
print @SQL
select * from #Test2
--Create Table #Test1 (ID int identity,prod_desc nvarchar(100),pack nvarchar(100),ntp int,party_qty1 int ,party_qty2 int ,party_qty3 int ,party_qty4 int ,party_qty5 int ,tot_qty int ,tot_amt int )
set @iCurLine=1
--=============================================
--print '=================Product Name========================'
DECLARE PROD_CUR CURSOR FOR
SELECT distinct(prod_code) as prod_code,prod_desc,pack,ntp FROM VW_sales_sum
WHERE (REP = @REP)
group by prod_code,prod_desc,pack,ntp
Declare @SQL_ins VarChar(1000)
OPEN PROD_CUR
FETCH NEXT FROM PROD_CUR
INTO @temp_prod_code,@temp_prod_desc,@temp_pack,@temp_n tp
WHILE @@FETCH_STATUS = 0
BEGIN
--Print @temp_prod_desc+'-'+convert(varchar(10),@temp_pack)+'-'+convert(varchar(10),@temp_ntp)
--print '=================prod_code :'+convert(varchar(10),@temp_prod_code)+'========= ==============='
print "============Insert Statement============"
SET @SQL_ins = "Insert into "+@TableName+" values("
SET @SQL_ins = @SQL_ins +""""+ @temp_prod_desc+""","""+@temp_pack+""","""+convert(char(10),@temp_ntp)+""","
DECLARE Party_CUR1 CURSOR FOR
SELECT party_code,party_name FROM VW_PART_REP
WHERE (REP = @REP) group by party_code,party_name
DECLARE @SQL1 varchar(10),@SQL2 varchar(100)
set @SQL2=''
OPEN Party_CUR1
FETCH NEXT FROM Party_CUR1
INTO @temp_party_code,@temp_party_name
WHILE @@FETCH_STATUS = 0
BEGIN
print "==Party_name :"+ @temp_party_name +"===Party_Code :"+convert(char(3),@temp_party_Code)
set @tot_sale_qty=0
set @tot_sale_qty= (select sum(issuedqty)as tot_qty from vw_sales_sum
where party_code =@temp_party_code
and (REP = @REP)
and (prod_code=@temp_prod_code))
if @tot_sale_qty IS NULL
begin
set @tot_sale_qty=0
print "===Tot Qty :"+convert(varchar(10),@tot_sale_qty)
end
else
begin
print "===Tot Qty :"+convert(varchar(10),@tot_sale_qty)
end
SELECT @SQL1 = @tot_sale_qty
select@net_tot_sale_qty=@net_tot_sale_qty+@tot_sale_qty
--print @SQL1
Select @SQL2 = @SQL2 + @SQL1 +","
FETCH NEXT FROM Party_CUR1
INTO @temp_party_code,@temp_party_name
END
CLOSE Party_CUR1
DEALLOCATE Party_CUR1
print '==============================='
print @SQL2+convert(char(4),@net_tot_sale_qty)
--set @net_tot_sale_qty=@tot_sale_qty+@net_tot_sale_qty
--SET @SQL = @SQL+convert(char(10),@tot_sale_qty)+','
--set @tot_sale_qty=0
Select @SQL_ins = @SQL_ins+@SQL2+convert(char(8),@net_tot_sale_qty)
Select @SQL_ins = @SQL_ins+","
Select @SQL_ins = @SQL_ins+convert(char(12),(@net_tot_sale_qty*@temp _ntp))
Select @SQL_ins = @SQL_ins+")"
print @SQL_ins
Exec (@SQL_ins)
select@net_tot_sale_qty=0
FETCH NEXT FROM PROD_CUR
INTO @temp_prod_code,@temp_prod_desc,@temp_pack,@temp_n tp
END
CLOSE PROD_CUR
DEALLOCATE PROD_CUR
SELECT * FROM #Test2
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER ON
GO
View 8 Replies
View Related
Jun 19, 2008
Hi all
I'm new to sql and could do with some help resolving this issue.
My problem is as follows,
I have two tables a BomHeaders table and a BomComponents table which consists of all the components of the boms in the BomHeaders table.
The structure of BOMs means that BOMs reference BOMs within themselves and can potentially go down many levels:
In a simple form it would look like this:
LevelRef: BomA
1component A
1component B
1Bom D
1component C
What i would like to do is potentially create a temporary table which uses the BomReference as a parameter and will loop through the records and bring me back every component from every level
Which would in its simplest form look something like this
LevelRef: BomA
1......component A
1......component B
1......Bom D
2.........Component A
2.........Component C
2.........Bom C
3............Component F
3............Component Z
1......component C
I would like to report against this table on a regular basis for specific BomReferences and although I know some basic SQL this is a little more than at this point in time i'm capable of so any help or advice on the best method of tackling this problem would be greatly appreciated.
also i've created a bit of a diagram just in case my ideas weren't conveyed accurately.
Bill Shankley
View 4 Replies
View Related
Feb 11, 2015
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
only on calling the proc does this give an execution error
View 3 Replies
View Related
Dec 14, 1999
Hello,
Just in case anybody has had this problem after applying service pack 5a.
a stored procedure that sets serial numbers on records on a temporary table is skipping every other record.
I ran the code manually on isql and could not replicate the error, however when re-compiled on stored procedure it fails misserably.
when is Microsoft going to release a service pack that actually works?
Did you know that Executive does not recognize 2000 as a leap year?
If anybody has any suggestions on this problem, besides the obvious (using a real table) please write back
thanks.
David E. Pierri
Pseudo data-admin
ACNY
View 1 Replies
View Related
May 22, 2014
I am working with the SP tuning. I want to know clearly about the temp tables.Instead of Select * into #table, it is always better to create temp table structure and insert the data later.Is it true in the case, if the data is small? insert into #table (select colmn1, column 2 from TableA join Table B on JoinC on joinD on..If they use several joins, in that situation, which way of creating temp table would be better?
View 9 Replies
View Related
Jan 27, 2006
I am currently working on a simple page to insert 1.6 million UK postcode records into an SQL server table. The table has three columns for the postcode, longditude coordinate and lattitude coordinate. The data is sourced from a pipe (|) delimited txt file and inserted into the database using a FOR loop. The problem I have is that the page will hang after inserting only 10,000 records, the page displays either an invalid View State error or a page cannot be found error.
Now I assume the viewstate error stems from the fact that there is a form on the page which simply contains a button to execute the script and a few labels to show the progress. But without the form and associated viewstate the insert still fails to complete.... any ideas?? Would I be better running this on a thread or should I just do it in stages and be patient. I have now modified the page to read the database on load and pick up from where it crashes?
View 2 Replies
View Related
Aug 30, 2006
Meg writes "Hi,
I have a table that has 4+ million records. I need to update those records. I am facing some performance issue. Can someone please advice?
update stage
set batch_status = 1
where update_status = 0
Update transaction
Set aId = s.aId,
b = s.b,
from stage s
Where s.aId = transaction.aId
and s.batch_status = 1
Update stage
Set update_status = 1,
batch_status = 2
where
batch_status = 1
When I run the above query with "set rowcount 1000", it runs in one minute. When I run the query for "set rowcount 10000", it runs in 1 hour 56 minutes. Can someone help me to optimize it?
Thanks.
Meg"
View 4 Replies
View Related
Jul 20, 2005
Hey folks...So I have a table that looks like this:CREATE TABLE [tblStation] ([CAMPAIGN] [varchar] (8),[LISTNUM] [varchar] (10),[PHONE] [varchar] (10),[EVENTTIME] [datetime] ,[STATION] [int],[OPERATOR] [varchar] (16),[EVENTCODE] [varchar],[CALLSPAN] [decimal](18, 0),[FDISP] [int],[RECORDNUM] [varchar],[STC] [varchar],[PROMOC] [varchar],[EXP_CAMP] [varchar],[PROMO3] [varchar],[MAXATT] [char],[LISTNAME] [varchar],[SITENAME] [char],[Row_id] [int] IDENTITYIt's taking nine seconds to run the following command:SELECT count([fdisp])FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)WHERE fdisp IS NULLAnyone familiar with a table of this size having performance likethis? The [fdisp] column has a non clustered index on it.Thanks in advance...
View 1 Replies
View Related
Apr 3, 2013
Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.
View 5 Replies
View Related
Mar 5, 2014
it runs over night, pulls 10.5m rows. Inserts into a table, from a select (so "insert...select", rather than "select into"), from many tables, grouping on a max. It's complex. During the day, it runs fine - maybe 25 minutes. At night it *sometimes* runs fine, but then sometimes takes 4hours.Checking this morning there were 230 threads open for this one query. Checking sys.dm_os_tasks and sys.dm_os_waiting_tasks there were no other wait types on that session_id. None at all. Checking activity monitor, most of the existing threads were suspended on the insert.
There are 24 cores, but NUMA'd. We have maxdop on the server of 8. The maxdop option on the query is 12, just to speed up the select. Index and Stats refreshed daily. We've eight identical tempdb data files, on a separate spindle. Checking those, they are filling up using the round robin correctly
Would the delay be due to SQL trying to combine so many 'select' threads into one 'insert' thread (as it can't insert in parallel; 2014 can, apparently. Upgrades not available!)Should i change the SP to run the select into a temp table (table variable?) with a maxdop of 12, then do the insert into the actual table using a maxdop of 1. Checking the execution plans for a table variable implies the subtree cost comes down from 2.5m to 357k. What's best - temp table or table variable?
View 9 Replies
View Related
May 27, 2014
I try to modify a code to create a temp table but got an error.
Declare @headerid Int
Declare @Providerid Int
BELOW CODE IS WORKING:
SELECT @headerid=HEADERID,@Providerid=PROVIDERID
FROM CLAIM WITH(NOLOCK) WHERE EDI_CLM_NUM= 123
[code]...
Error:
Incorrect syntax near '='.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
View 5 Replies
View Related
May 12, 2015
I am trying to insert a single row in Temp table #InventoryItems . The temp table is mentioned in a curosor childcur_inventory. While looping through the cursor I have mentioned nested if else condition . In the Else condition where I have mentioned PRINT "Hello World" I want to insert a single row in the temp #InventoryItems. I trying to use Select Top 1 but the cursor is looping indefinitely trying to insert multiple record
I want to insert only one record with null values.
DECLARE childcur_inventory CURSOR FOR SELECT Structure_Number, State, Neighbor_State, Border_Bridge_Structure_Number FROM #InventoryItems
OPEN childcur_inventory
FETCH childcur_inventory INTO @Structure_Number, @State, @Neighbour_State, @Border_Bridge_Structure_Number
[Code] .....
View 5 Replies
View Related
Nov 16, 2001
How well SQL Server can support 300 million records...
Any body is working on big database like this. can anyone give me some input on this. it's going to be 60GB database size.
View 1 Replies
View Related
Mar 19, 2008
Hello,
What is the fastest way to update 20million records in our database.
I have tried to do a simple update statement like this:
update trail_log with (tablockx, holdlock)
set trail_log .entry_by = users.user_identity
from users
where trail_log.entry_by = users.user_id
but it take 10 plus hours to run since it cannot commit the transactions until the very end. So was was thinking that I need to commit in batch like after 50K but that is slow as well.
Set rowcount 50000
Declare @rc int
Set @rc=50000
While @rc=50000
Begin
Begin Transaction
update trail_log With (tablockx, holdlock)
set trail_log.entry_by = users.user_identity
from users
where trail_log.entry_by = users.user_id
and trail_log.entry_by not like '%[0-9]%'
Select @rc=@@rowcount
--Commit the transaction
Commit
End
go
I have let the above statement run for 1.5 hours and it only update 450000 rows. Any ideas...
Maybe I'm doing it wrong. Please Help!!
View 1 Replies
View Related
Oct 12, 2007
Hi all,
I have a sql script that updates records in a table with 40 million records.
There is some functionality in the script that could be put away in functions for code reuse/elegance.
Functions would cause execution overhead.
What else could I use besides functions that would allow me the code reuse and not compromise the execution over head? Is there any thing like includes in TSQL that would allow me to do so?
TIA..
View 4 Replies
View Related
Sep 30, 2014
In Access, I can import a txt file data(e.g. Claims.txt) as below specifications:
Choose the delimiter that separates your fields: Other (|)
First row contains field name: Yes
Text Qualifier:"
I need to create a store procedure to read txt file data (d:cliamcliams.txt) first column (ClaimNumber) into a temp table (#claim)
(This #claim table will use for my .Net program)
There about 5 txt files need to process every day.
View 1 Replies
View Related
Feb 13, 2015
I have created a global temp table in Step1 of SQL Job.
I have used that in remaining steps of same job...i ran the job
But i got error message like invalid object name ##xxxxxxxx later i have included as tempdb..##xxxxxxxx also. the i got invalid reference for...
From my SSMS:-
But i was able to do select query for the same from my SSMS...
i have incorporated all steps in single step and completed job...
My question is why ##temp table created in step1 is not able to use in other steps of same job ?
SQL Server 2012 Enterprise Edition
View 2 Replies
View Related
Apr 21, 2015
I want to insert data (month&year) from 2014 till now - into temp table using 2 while loop.
drop table #loop
create table #loop
(
seq int identity(1,1),
[month] smallint,
[Year] smallint
For some reason I cant not get 2015 data .
View 4 Replies
View Related
Apr 23, 2007
Hi
I have a new client with an existing system that has just over 2 million business listings in one table. Each business listing is associated with one business category.
* Company Table (around 20 fields):
companyID
companyName
categoryID
state
postCode
etc.
* Category Table (5 fields)
categoryID
categoryName
etc.
We are using MSSQL 2005 Express Edition with Advanced Services
A free text search needs to be performed on the companyName and categoryName limited by region (state and or postcode).
1) What kind of response times should I expect for the free text search (I have not used the free text search before)
2) How should I index the companyName and categoryName so they are both used in a joined query? i.e. Do I just configure the free text search index on each field separately and it should work?
Any suggestions appreciated.
Best Regards
Kevan
View 2 Replies
View Related
Mar 18, 2014
I want to compare ONLY 1 Column values from 2 tables having more than 4.9 million records. There is a difference of 4000 rows between the 2 tables.
SELECT ID From TABLE1 where ID not in (SELECT DISTINCT ID From TABLE2)
My above query took nearly 4.5 hours to run and I had to cancel it. Is there a better way to write the query . I just want to compare the ID - column values which are missing in TABLE2
View 7 Replies
View Related
Apr 8, 2008
Hi
I have 2 tables with more then million records in each and I have to perform full outer join.
The problem is that the join clause contains 2 different parameters (int and string) like this:
Select *
From a full outer join b
On a.cli = b.cli OR a.reference = b.reference
Because of the OR in the clause and the million records the query is infinite. If I change to one rule only then it works fine.
How can I join these 2 big tables with 2 rules?
Thanks
Itay
View 2 Replies
View Related
May 18, 2006
I have tried to process > 3 million Fuzzy grouping records on two different servers with no success. 3 mill works but anything above 4 mill doesn't. Some background:
We are trying to de-dup our customer table on: name (.5 min), address1 (.5 min), city (.5 min), state (exact). .8 overall record min score.
Output includes additional fields: customerid, sourceid, address2, country, phonenumber
Without SP1 installed I couldn't even get a few hundred thousand records to process
Two different servers - same problems. Note that SSIS and SQL Server are running locally on both
The higher end server has 4GB RAM, the other 2.5 GB RAM. Plenty of free disk space on both
SQL Server is configured to use 2 GB of RAM max
The page file is currently at 15GB
After running a number of test on both servers trying different batch sizes etc. the one thing I noticed is that it seems to always error out when SSIS takes over and starts chewing up all the available RAM. This happens after the index is created and SSIS starts "warming caches". On both servers SQL Server uses up about 1.6GB of RAM at this point while SSIS keeps taking over RAM until all physical RAM is used up.
Some questions:
Has anyone been able to process more then 3 million records and if so what is your hardware configuration?
Should we try running SSIS from a different server so it has access to the full amount of physical RAM? (so it doesn't have to fight for RAM with SQL Server)
Should we install Win 2003 Enterprise Server so we can add more RAM?
Any ideas why switching to the page file might be causing errors?
Thanks!!
Keith Doyle
View 17 Replies
View Related
Feb 18, 2014
I am having with trying to import XLSX files into SQL 2012 64 Bit.
I have installed the Access driver (AccessDatabaseEngine_x64.exe)
I have configured the script to run the following SP
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
[Code] ....
So I first create my Temp Table
The run the SP above then I run the insert into the Temp table defined
INSERT INTO tempdb.dbo.TempTRBZ (IsNew,CoID, Zip, City, County,StateCode,Rate,Taxable,TaxShip,TaxLab,CountryID,StateID)
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=C:TempNotInTrbzJan.xlsx;HDR=YES','SELECT * FROM [Data$]')
[Code] ....
The error message I get back is
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
What I have set wrong on the import? Using SSIS at this point is not a real option.
View 0 Replies
View Related
