Bulk UPDATE on SQL Server 2000

I need to do a bulk update of 1 field in a data-table (removing
zero-padding). The update is expected to update 52.5 million records.
What techniques could I use so I can ensure
1) Consistency in the transactional log backups (every 10 mins)
2) Consistency in the full DB backup every day
3) Disk drive of Transaction logs do not get filled up completely (I suspect
even if I set rown count to 10,000 and loop through, disk-space would still
be used')
Target environment
SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
(Different disk drives for data and log files)A normal set based operation will do fine. update my table set myfield =
'mynewvalue'
However, this will create significant locking so you're only going to be
this aggressive on a database that's not in use (Overnight, for most) or
write a cursor that includes some logic in it but that's going to be slow.
DatabaseAdmins.com
Databases Reliable. Available.
"Patrick" wrote:

> I need to do a bulk update of 1 field in a data-table (removing
> zero-padding). The update is expected to update 52.5 million records.
> What techniques could I use so I can ensure
> 1) Consistency in the transactional log backups (every 10 mins)
> 2) Consistency in the full DB backup every day
> 3) Disk drive of Transaction logs do not get filled up completely (I suspe
ct
> even if I set rown count to 10,000 and loop through, disk-space would stil
l
> be used')
> Target environment
> SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> (Different disk drives for data and log files)|||SQL Server takes care of consistency. To make sure you don't blow up the
transaction log, dividing the update into smaller chunks of updates (e.g.
10,000 rows at a time as you mentioned) is the right approach in general.
I'd like to add that you may have to pace your updates (e.g. sleep for some
time after each iteration) so that your transaction log backups at the
10-minute interval have a chance coming in to clear out the inactive portion
of the log for reuse. Alternative, you can backup the transaction log after
each iteration (or every so many iterations) in the loop. You need to
experiment a bit to find out the best chunk size and whether the loop withou
t
any wait time would be too tight in your particular configuration.
Also, it's certainly possible you can choose to update all the rows in a
single shot, depending on the size of your transaction log.
Linchi
"burt_king" wrote:
[vbcol=seagreen]
> A normal set based operation will do fine. update my table set myfield =
> 'mynewvalue'
> However, this will create significant locking so you're only going to be
> this aggressive on a database that's not in use (Overnight, for most) or
> write a cursor that includes some logic in it but that's going to be slow.
> --
> DatabaseAdmins.com
> Databases Reliable. Available.
>
> "Patrick" wrote:
>|||I have composed the following SQL, could you tell me what is wrong? The
Select getDate() only printed <10 dates! when I am expecting millions of
records to be updated!
DECLARE @.recCount int
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_transactions
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_stocks
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_inventory
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
"Linchi Shea" wrote:
[vbcol=seagreen]
> SQL Server takes care of consistency. To make sure you don't blow up the
> transaction log, dividing the update into smaller chunks of updates (e.g.
> 10,000 rows at a time as you mentioned) is the right approach in general.
> I'd like to add that you may have to pace your updates (e.g. sleep for som
e
> time after each iteration) so that your transaction log backups at the
> 10-minute interval have a chance coming in to clear out the inactive porti
on
> of the log for reuse. Alternative, you can backup the transaction log afte
r
> each iteration (or every so many iterations) in the loop. You need to
> experiment a bit to find out the best chunk size and whether the loop with
out
> any wait time would be too tight in your particular configuration.
> Also, it's certainly possible you can choose to update all the rows in a
> single shot, depending on the size of your transaction log.
> Linchi
> "burt_king" wrote:
>

Bulk UPDATE on SQL Server 2000

I need to do a bulk update of 1 field in a data-table (removing
zero-padding). The update is expected to update 52.5 million records.
What techniques could I use so I can ensure
1) Consistency in the transactional log backups (every 10 mins)
2) Consistency in the full DB backup every day
3) Disk drive of Transaction logs do not get filled up completely (I suspect
even if I set rown count to 10,000 and loop through, disk-space would still
be used')
Target environment
SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
(Different disk drives for data and log files)A normal set based operation will do fine. update my table set myfield ='mynewvalue'
However, this will create significant locking so you're only going to be
this aggressive on a database that's not in use (Overnight, for most) or
write a cursor that includes some logic in it but that's going to be slow.
--
DatabaseAdmins.com
Databases Reliable. Available.
"Patrick" wrote:
> I need to do a bulk update of 1 field in a data-table (removing
> zero-padding). The update is expected to update 52.5 million records.
> What techniques could I use so I can ensure
> 1) Consistency in the transactional log backups (every 10 mins)
> 2) Consistency in the full DB backup every day
> 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> even if I set rown count to 10,000 and loop through, disk-space would still
> be used')
> Target environment
> SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> (Different disk drives for data and log files)|||SQL Server takes care of consistency. To make sure you don't blow up the
transaction log, dividing the update into smaller chunks of updates (e.g.
10,000 rows at a time as you mentioned) is the right approach in general.
I'd like to add that you may have to pace your updates (e.g. sleep for some
time after each iteration) so that your transaction log backups at the
10-minute interval have a chance coming in to clear out the inactive portion
of the log for reuse. Alternative, you can backup the transaction log after
each iteration (or every so many iterations) in the loop. You need to
experiment a bit to find out the best chunk size and whether the loop without
any wait time would be too tight in your particular configuration.
Also, it's certainly possible you can choose to update all the rows in a
single shot, depending on the size of your transaction log.
Linchi
"burt_king" wrote:
> A normal set based operation will do fine. update my table set myfield => 'mynewvalue'
> However, this will create significant locking so you're only going to be
> this aggressive on a database that's not in use (Overnight, for most) or
> write a cursor that includes some logic in it but that's going to be slow.
> --
> DatabaseAdmins.com
> Databases Reliable. Available.
>
> "Patrick" wrote:
> > I need to do a bulk update of 1 field in a data-table (removing
> > zero-padding). The update is expected to update 52.5 million records.
> >
> > What techniques could I use so I can ensure
> > 1) Consistency in the transactional log backups (every 10 mins)
> > 2) Consistency in the full DB backup every day
> > 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> > even if I set rown count to 10,000 and loop through, disk-space would still
> > be used')
> >
> > Target environment
> > SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> > (Different disk drives for data and log files)|||I have composed the following SQL, could you tell me what is wrong? The
Select getDate() only printed <10 dates! when I am expecting millions of
records to be updated!
DECLARE @.recCount int
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_transactions
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_stocks
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
SELECT @.recCount=50001
SET rowcount 50000
WHILE @.recCount>0
BEGIN
select getdate()
waitfor delay '000:00:5'
BEGIN TRAN
UPDATE
cor_inventory
SET
id_Entity = SUBSTRING(id_Entity,2,3)
WHERE
len(id_Entity)>3
and
id_Entity like '0%'
IF @.@.ERROR= 0
COMMIT TRAN
ELSE
BEGIN
ROLLBACK TRAN
print 'ERROR' + Cast(@.@.ERROR as varchar(50))
BREAK
END
SELECT @.recCount=@.@.ROWCOUNT
END
"Linchi Shea" wrote:
> SQL Server takes care of consistency. To make sure you don't blow up the
> transaction log, dividing the update into smaller chunks of updates (e.g.
> 10,000 rows at a time as you mentioned) is the right approach in general.
> I'd like to add that you may have to pace your updates (e.g. sleep for some
> time after each iteration) so that your transaction log backups at the
> 10-minute interval have a chance coming in to clear out the inactive portion
> of the log for reuse. Alternative, you can backup the transaction log after
> each iteration (or every so many iterations) in the loop. You need to
> experiment a bit to find out the best chunk size and whether the loop without
> any wait time would be too tight in your particular configuration.
> Also, it's certainly possible you can choose to update all the rows in a
> single shot, depending on the size of your transaction log.
> Linchi
> "burt_king" wrote:
> > A normal set based operation will do fine. update my table set myfield => > 'mynewvalue'
> >
> > However, this will create significant locking so you're only going to be
> > this aggressive on a database that's not in use (Overnight, for most) or
> > write a cursor that includes some logic in it but that's going to be slow.
> >
> > --
> > DatabaseAdmins.com
> > Databases Reliable. Available.
> >
> >
> > "Patrick" wrote:
> >
> > > I need to do a bulk update of 1 field in a data-table (removing
> > > zero-padding). The update is expected to update 52.5 million records.
> > >
> > > What techniques could I use so I can ensure
> > > 1) Consistency in the transactional log backups (every 10 mins)
> > > 2) Consistency in the full DB backup every day
> > > 3) Disk drive of Transaction logs do not get filled up completely (I suspect
> > > even if I set rown count to 10,000 and loop through, disk-space would still
> > > be used')
> > >
> > > Target environment
> > > SQL Server 2000 cluster on 2 nodes running Windows 2003 Server
> > > (Different disk drives for data and log files)

Bulk SQL Server 2000 Registration

Hi All,
I have about 60 or so SQL Servers to register. I don't have the option of
importing from another machine.
I think I maybe able to do this via SQL-DMO but not sure how to get started.
Does anyone have any ideas about doing mass SQl Server registrations?
Thanks.Check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_r_62pg.asp
Haven't tried it but you might also be able to create a query and loop for
sp_addserver via QA or OSQL.
HTH
Jerry
"brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> Hi All,
> I have about 60 or so SQL Servers to register. I don't have the option of
> importing from another machine.
> I think I maybe able to do this via SQL-DMO but not sure how to get
> started.
> Does anyone have any ideas about doing mass SQl Server registrations?
> Thanks.|||Thanks. I will try this.
Will keep the community posted ( pun intended)!
"Jerry Spivey" wrote:
> Check out:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_r_62pg.asp
> Haven't tried it but you might also be able to create a query and loop for
> sp_addserver via QA or OSQL.
> HTH
> Jerry
> "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> > Hi All,
> >
> > I have about 60 or so SQL Servers to register. I don't have the option of
> > importing from another machine.
> >
> > I think I maybe able to do this via SQL-DMO but not sure how to get
> > started.
> >
> > Does anyone have any ideas about doing mass SQl Server registrations?
> >
> > Thanks.
>
>|||OK. I have made a little progress.
I got the script to create the Server Groups in EM but it keeps crashing
with a memory error when I try to add the RegisteredServer object to the
RegisteredServers collection. In other words, I am unable to start
registering servers.
I have included the vbscript below:
' Declare variables
Const ForReading = 1, ForWriting = 2
Dim dmoApp, dmoServerGroup, dmoRegServer
Dim fso, MyFile, strServerName
on error resume Next
' Create a ref to the SQL Server Object
Set dmoApp = Wscript.CreateObject("SQLDMO.Application")
' Create a ServerGroup Object
' Set dmoServerGroup = CreateObject("SQLDMO.ServerGroup")
Set dmoServerGroup = Wscript.CreateObject("SQLDMO.ServerGroup")
' Add the CRAWLEY Server Group name
dmoServerGroup.Name = "CRAWLEY"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Add the TAMPA Server Group name
dmoServerGroup.Name = "TAMPA"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Register the TAMPA Servers
Set dmoRegServer = Wscript.CreateObject("SQLDMO.RegisteredServer")
dmoRegServer.UseTrustedConnection = 1
Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.OpenTextFile("C:\RWray\VBScripts\TampaServers.txt",
ForReading, True)
Do Until MyFile.AtEndOfStream
strServerName = MyFile.Readline
dmoRegServer.Name = strServerName
Wscript.Echo strServerName
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
WScript.Echo Err.Description
Loop
MyFile.Close
Set dmoApp = Nothing
Set dmoServerGroup = Nothing
set dmoRegServer = Nothing
It crashes on the line
dmoServerGroup.RegisteredServers.Add(dmoRegServer).
Thanks.
"brawtaman" wrote:
> Thanks. I will try this.
> Will keep the community posted ( pun intended)!
> "Jerry Spivey" wrote:
> > Check out:
> >
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_ob_r_62pg.asp
> >
> > Haven't tried it but you might also be able to create a query and loop for
> > sp_addserver via QA or OSQL.
> >
> > HTH
> >
> > Jerry
> > "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> > news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> > > Hi All,
> > >
> > > I have about 60 or so SQL Servers to register. I don't have the option of
> > > importing from another machine.
> > >
> > > I think I maybe able to do this via SQL-DMO but not sure how to get
> > > started.
> > >
> > > Does anyone have any ideas about doing mass SQl Server registrations?
> > >
> > > Thanks.
> >
> >
> >

Bulk SQL Server 2000 Registration

Hi All,
I have about 60 or so SQL Servers to register. I don't have the option of
importing from another machine.
I think I maybe able to do this via SQL-DMO but not sure how to get started.
Does anyone have any ideas about doing mass SQl Server registrations?
Thanks.Check out:
http://msdn.microsoft.com/library/d... />
r_62pg.asp
Haven't tried it but you might also be able to create a query and loop for
sp_addserver via QA or OSQL.
HTH
Jerry
"brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> Hi All,
> I have about 60 or so SQL Servers to register. I don't have the option of
> importing from another machine.
> I think I maybe able to do this via SQL-DMO but not sure how to get
> started.
> Does anyone have any ideas about doing mass SQl Server registrations?
> Thanks.|||Thanks. I will try this.
Will keep the community posted ( pun intended)!
"Jerry Spivey" wrote:

> Check out:
> http://msdn.microsoft.com/library/d...>
b_r_62pg.asp
> Haven't tried it but you might also be able to create a query and loop for
> sp_addserver via QA or OSQL.
> HTH
> Jerry
> "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
>
>|||OK. I have made a little progress.
I got the script to create the Server Groups in EM but it keeps crashing
with a memory error when I try to add the RegisteredServer object to the
RegisteredServers collection. In other words, I am unable to start
registering servers.
I have included the vbscript below:
' Declare variables
Const ForReading = 1, ForWriting = 2
Dim dmoApp, dmoServerGroup, dmoRegServer
Dim fso, MyFile, strServerName
on error resume Next
' Create a ref to the SQL Server Object
Set dmoApp = Wscript.CreateObject("SQLDMO.Application")
' Create a ServerGroup Object
' Set dmoServerGroup = CreateObject("SQLDMO.ServerGroup")
Set dmoServerGroup = Wscript.CreateObject("SQLDMO.ServerGroup")
' Add the CRAWLEY Server Group name
dmoServerGroup.Name = "CRAWLEY"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Add the TAMPA Server Group name
dmoServerGroup.Name = "TAMPA"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Register the TAMPA Servers
Set dmoRegServer = Wscript.CreateObject("SQLDMO.RegisteredServer")
dmoRegServer.UseTrustedConnection = 1
Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.OpenTextFile("C:\RWray\VBScripts\TampaServers.txt",
ForReading, True)
Do Until MyFile.AtEndOfStream
strServerName = MyFile.Readline
dmoRegServer.Name = strServerName
Wscript.Echo strServerName
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
WScript.Echo Err.Description
Loop
MyFile.Close
Set dmoApp = Nothing
Set dmoServerGroup = Nothing
set dmoRegServer = Nothing
It crashes on the line
dmoServerGroup.RegisteredServers.Add(dmoRegServer).
Thanks.
"brawtaman" wrote:
[vbcol=seagreen]
> Thanks. I will try this.
> Will keep the community posted ( pun intended)!
> "Jerry Spivey" wrote:
>sql

Bulk SQL Server 2000 Registration

Hi All,
I have about 60 or so SQL Servers to register. I don't have the option of
importing from another machine.
I think I maybe able to do this via SQL-DMO but not sure how to get started.
Does anyone have any ideas about doing mass SQl Server registrations?
Thanks.
Check out:
http://msdn.microsoft.com/library/de..._ob_r_62pg.asp
Haven't tried it but you might also be able to create a query and loop for
sp_addserver via QA or OSQL.
HTH
Jerry
"brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
> Hi All,
> I have about 60 or so SQL Servers to register. I don't have the option of
> importing from another machine.
> I think I maybe able to do this via SQL-DMO but not sure how to get
> started.
> Does anyone have any ideas about doing mass SQl Server registrations?
> Thanks.
|||Thanks. I will try this.
Will keep the community posted ( pun intended)!
"Jerry Spivey" wrote:

> Check out:
> http://msdn.microsoft.com/library/de..._ob_r_62pg.asp
> Haven't tried it but you might also be able to create a query and loop for
> sp_addserver via QA or OSQL.
> HTH
> Jerry
> "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> news:8A49CC26-4F93-49C7-9407-8451F2C38648@.microsoft.com...
>
>
|||OK. I have made a little progress.
I got the script to create the Server Groups in EM but it keeps crashing
with a memory error when I try to add the RegisteredServer object to the
RegisteredServers collection. In other words, I am unable to start
registering servers.
I have included the vbscript below:
' Declare variables
Const ForReading = 1, ForWriting = 2
Dim dmoApp, dmoServerGroup, dmoRegServer
Dim fso, MyFile, strServerName
on error resume Next
' Create a ref to the SQL Server Object
Set dmoApp = Wscript.CreateObject("SQLDMO.Application")
' Create a ServerGroup Object
' Set dmoServerGroup = CreateObject("SQLDMO.ServerGroup")
Set dmoServerGroup = Wscript.CreateObject("SQLDMO.ServerGroup")
' Add the CRAWLEY Server Group name
dmoServerGroup.Name = "CRAWLEY"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Add the TAMPA Server Group name
dmoServerGroup.Name = "TAMPA"
dmoApp.ServerGroups.Add(dmoServerGroup)
' Register the TAMPA Servers
Set dmoRegServer = Wscript.CreateObject("SQLDMO.RegisteredServer")
dmoRegServer.UseTrustedConnection = 1
Set fso = Wscript.CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.OpenTextFile("C:\RWray\VBScripts\TampaServers. txt",
ForReading, True)
Do Until MyFile.AtEndOfStream
strServerName = MyFile.Readline
dmoRegServer.Name = strServerName
Wscript.Echo strServerName
dmoServerGroup.RegisteredServers.Add(dmoRegServer)
WScript.Echo Err.Description
Loop
MyFile.Close
Set dmoApp = Nothing
Set dmoServerGroup = Nothing
set dmoRegServer = Nothing
It crashes on the line
dmoServerGroup.RegisteredServers.Add(dmoRegServer) .
Thanks.
"brawtaman" wrote:
[vbcol=seagreen]
> Thanks. I will try this.
> Will keep the community posted ( pun intended)!
> "Jerry Spivey" wrote:

bulk sql insert task can do tables with identity?

i got some bulk insert tasks in SSIS inserting into some tables with identity set ON....

in 1 column. Can the bulk insert task go smoothly?

PS: i cannot find anywhere in the bulk insert task that can set the ignore identity columns...........

now my steps are prepare database -> create database -> bulk insert into tables

working on my previous problem, Jamie.

Open the Bulk Insert Task, select the Options page, select Options, open the drop down. Check "Enable Identity Insert".

Bulk repopulation of a table

Hi, I have a table T with 3 int columns, I need to update it every X
hours with a query that returns the same 3 int columns. Currently I'm
doing it using a cursor iterating over each row and checking whether I
need to DELETE/UPDATE/INSERT it, this however isn't very efficient.
So I tested 2 additional methods, one involving deleting all the rows
from the target table and then inserting everything and the other by
dropping the table, recreating and inseting.
The results are not very surprising:
PROC,CPU,READS,WRITES,DURATION
DROP,2720,177757,218,4000
DELE,3150,183060,230,5300
CURS,8200,504818,247,8240
Which indicates that DROPing and reCREATing is the way to go, any
suggestions?
I don't like the DROP/CREATE scheme since it might involve the TABLEs
downtime (even when I CREATE a T1 then DROP T and sp_rename T1->T). If
there is no better way, will there be problems due to sp_rename
changing the name but not the ID of an object?
P.S. it's SQL2005, and the query itself executes in (2300,11596,0,2730)
[same column names as above].
Thanks.Can you give some more information so we can provide a more realistic
answer? The DDL for the existing table would help along with how or where
you are getting the data to update the table with. If the new data is a
complete up to date set then you have several options. I am assuming it is a
flat file but since you didn't provide that information it is only a guess.
I would look at using BULK INSERT to load the new data into a table that you
create. Lets call it X. Then you can prep this table to get it exactly like
you want it to be, drop the original table and rename X to what the old one
was. You may find that renaming the old one first, renaming X and then
dropping might yield the least time that the original table is off-line to
the users. But either way it should be a matter of a second or less.
Andrew J. Kelly SQL MVP
<johnsolver@.gmail.com> wrote in message
news:1137942204.960433.107600@.g49g2000cwa.googlegroups.com...
> Hi, I have a table T with 3 int columns, I need to update it every X
> hours with a query that returns the same 3 int columns. Currently I'm
> doing it using a cursor iterating over each row and checking whether I
> need to DELETE/UPDATE/INSERT it, this however isn't very efficient.
> So I tested 2 additional methods, one involving deleting all the rows
> from the target table and then inserting everything and the other by
> dropping the table, recreating and inseting.
> The results are not very surprising:
> PROC,CPU,READS,WRITES,DURATION
> DROP,2720,177757,218,4000
> DELE,3150,183060,230,5300
> CURS,8200,504818,247,8240
> Which indicates that DROPing and reCREATing is the way to go, any
> suggestions?
> I don't like the DROP/CREATE scheme since it might involve the TABLEs
> downtime (even when I CREATE a T1 then DROP T and sp_rename T1->T). If
> there is no better way, will there be problems due to sp_rename
> changing the name but not the ID of an object?
> P.S. it's SQL2005, and the query itself executes in (2300,11596,0,2730)
> [same column names as above].
> Thanks.
>|||Instead of deleting all rows from the table you can truncate it. Its not
a good idea to drop and recreate table.
Or
you can first store the data in one temporary and then update related
data from temporary table in permanent table using TSQL.
Please post data and how you want to update it.
Regards
Amish Shah
*** Sent via Developersdex http://www.examnotes.net ***|||Hi, sorry for not providing enough info:
the table:
CREATE TABLE [dbo].[T](
[tid] [int] NOT NULL,
[pid] [int] NOT NULL DEFAULT (0),
[r] [int] NOT NULL,
CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED
(
[t] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
The query returns the exact 3 columns, simplistically: SELECT tid,pid,r
FROM A (it returns ~90k rows) I don't think that the exact query
matters... I'll be happy to provide additional info if needed.
Thanks.|||As to updating data, assuming T contains the following:
tid,pid,r
1,3,6
2,3,40
5,4,60
and the query (Q) returns
tid,pid,r
1,3,50
8,1,1000
Then I would like T to contain exactly what Q returned, namely:
DELETE FROM T WHERE tid=2
DELETE FROM T WHERE tid=5
UPDATE T SET r=50 WHERE tid=1
INSERT INTO T (tid,pid,r) VALUES(8,1,1000)
etc.|||If you are updating the value for r in table T, based on a match of both tid
and pid from the source table, then
--create a temporary table that has the data to be imported.
SELECT tid, pid, r INTO #importtable FROM (...the rest of your import query
...)
--Delete records from T that aren't in the new import batch
DELETE FROM T WHERE NOT EXISTS
(SELECT i.tid FROM #importtable i WHERE i.tid = T.tid)
--update the records that have matching tid and pid values
UPDATE T SET r = i.r FROM #importtable i
INNER JOIN T ON T.tid=i.tid AND T.pid = i.pid
--INSERT new records into T that didn't exist before
INSERT INTO T
SELECT tid,pid,r FROM #importtable i
WHERE NOT EXISTS
(SELECT tid FROM T WHERE T.tid = i.tid)
This gets more complicated if the PK on T is composite; I couldn't really
tell from the DDL you posted earlier.
Of course, it may be faster to just truncate the table and import in the new
data, as suggested earlier by Amish.
Another suggestion is to encapsulate the above into an INSTEAD OF trigger,
and then you could simply run
INSERT INTO T SELECT ...rest of your import query here ...
The INSTEAD OF trigger would fire before any primary key constraints would
be checked, so it could acheive what you want.
"johnsolver@.gmail.com" wrote:

> As to updating data, assuming T contains the following:
> tid,pid,r
> 1,3,6
> 2,3,40
> 5,4,60
> and the query (Q) returns
> tid,pid,r
> 1,3,50
> 8,1,1000
> Then I would like T to contain exactly what Q returned, namely:
> DELETE FROM T WHERE tid=2
> DELETE FROM T WHERE tid=5
> UPDATE T SET r=50 WHERE tid=1
> INSERT INTO T (tid,pid,r) VALUES(8,1,1000)
> etc.
>|||Thanks for the reply Mark,
I've tried your suggestion, performance-wise it's better than the
CURSOR solution (not surprising) but it's not as good as DROP or
TRUNCATE approaches. The key isn't a composite it's only one column as
you guessed, tid.
Amish: Why isn't it a good idea to DROP/CREATE the table? (except for
the minimal downtime due to sp_rename)?
Thanks.|||Johnsol
I also suggested you
you can first store the data in one temporary and then update related
data from temporary table in permanent table using TSQL.
But I was unable to give solution untill go post DDL.
Second
when you change the table name causes the sp, view and references
invalid which uses this table until you recreate new table with old
name.
If you drop and recreate table you have to recreate all relations ,
indexes again. You can not guarantee referential integrity of data
of othe tables if this table is part of any foregin key relationship
with them, since this table drops many times.
In some cases we have seen that updating data in the table was not
possible using TSQL easily and you have to create large number of temp
tables and check number of conditions and check data from number of
tables.
So I have gave you all the options but untill I get the data I was not
able to give you some solution.
Regards
Amish Shah|||Thanks for the reply Amish,
in the end I'll probably go with CREATE and DROP simply because it's
performance is about 1.5 times faster than the temp table solution, I
don't have any foreign keys/views built on the table.
So I've opted for the following (DDL follows), btw. should I wrap the
whole sequence in BEGIN TRANS ... COMIT TRANS?
CREATE TABLE [dbo].[T_1](
[tid] [int] NOT NULL,
[pid] [int] NOT NULL DEFAULT ((0)),
[r] [int] NOT NULL,
CONSTRAINT [PK_T_1] PRIMARY KEY CLUSTERED
(
[tid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO T_1 SELECT tid,pid,r FROM A;
DROP TABLE T;
EXEC sp_rename 'T_1','T';
EXEC sp_rename 'T.PK_T_1','PK_T','INDEX';|||Yes at least you should do all in transaction.
Regards
Amish Shah