October 4, 2002 at 12:55 am
This is what I am doing:
* Create a temp table.
* Insert records into temp table From T1.
* Get records from temp table and insert them into T2
* Update the records in T1 for the records in the temp table.
* Then I return all the records in the temp table.
Yeah..it's ugly.. is there another option other then a temp table...we are doing all this with about 20,000 records...and sometimes we got lock errors and such.
I was under the impression that I need to use a temp table because I refer to it about three times.
Any suggestions would be helpful.
Thanks
October 4, 2002 at 2:44 am
Why do you need to load the temp table prior to your insert? Is it not possible to insert direct from T1 into T2 by using SELECT INTO (or if that's turned off INSERT based on a SELECT statment.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
October 4, 2002 at 3:43 am
INSERT is a better option as opposed to SELECT INTO which does have known locking issues. Now as for your answer it would help to see you current code logic to understand what you are doing and what options are available.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 4, 2002 at 11:34 am
And if you are using SQL Server 2000, you can experiment with table variables to see if that performs better for you. Then it stores the data in memory instead of in the tempdb.
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 4, 2002 at 3:44 pm
I didn't know if I could post this much data but here we go:
CREATE PROCEDURE spBuildSendList_New
@intCompID int,
@intTotalAmt int
AS
declare @strTableName varchar(10)
declare @str_sql varchar(100)
select @strTableName = '_' + convert(nvarchar,@intCompID )
select @str_sql = 'Insert Into ' + @strTableName + ' Select EmailID From #ttTable'
/* Check to see if table exists for @intCompID, if not the table is created. */
exec spCheckTable @strTableName
/*Create the temp table to hold values from the initial select statement */
Create table #ttTable
(
Email varchar(100),
EmailID int
)
/*Insert the new emails into the temp table */
Insert Into #ttTable
execute spGetTheList_2 @intCompID, @intTotalAmt, @strTableName
/*Execute the sql script to insert the newly grabbed emails into the companies table*/
exec (@str_sql)
Update tblEmails Set EmailFlag = 4 Where EmailID IN (Select EmailID From #ttTable)
/*Return the newly grabbed emails*/
Select * From #ttTable
IF @@ROWCOUNT < @intTotalAmt
BEGIN
exec spRunEmailReset
END
drop table #ttTable
As far as Table types go....can someone point me twards some documentation...I've had a hard time finding some.
Thanks.
October 4, 2002 at 4:34 pm
I would add a word of caution - using the table data type may actually cause you more problems. I would actually recommend that you create a permanent table that is truncated for each set of rows. That way, you can add indexes to it - using variables of the table data type are always accessed via Table Scans - I've emailed sqlwish to ask for this to be addressed - we'll wait and see. If you created your #tt temp table permanently this would also cut down the i/o impact dramatically.
Simon, UK
(still looking for a job...)
October 5, 2002 at 5:32 am
I like permanent tables too if it will work in the situation (sometimes you may have different cols each time).
Andy
October 6, 2002 at 8:34 pm
I have the same columns all the time...
But there could be multiple instances running at the same time..so if it were a premenant table, and two added their data at the same time....all my updates would update data that did not belong to that query.
Does that make sence??
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply