Need to cleardown a table due to disk space problems

I need to delete about 3 million rows from a table that is part of a merge
publication. I think that I will have to copy out the rows that I want to
keep into a temp table and truncate the table and then copy the rows back in.
My question is how best to go about this? I think that the best way is to go
into the publication properties and uncheck the table on the articles tab.
Then to carry out the same process of copying out the data to be kept,
truncate the table, then move the rows to be kept back in. Then add the
article back into the publication. In order for the article to replicated
after adding it back in would I have to do a snapshot or would it resume by
itself?
Russell,
this sounds OK. However if the publication already has a subscription, you
won't be able to remove the individual article and you'll have to drop the
entire subscription before proceeding. You could drop the subscription,
remove the rows on publisher and subscriber then do a nosync initialization.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Need to bulk insert quickly with .NET

Hi all, I hope this is the correct place for this post.
I need to write a service that will bulk insert 3 million+ records from a
fixed width formatted text file to an SQL Server table. When I started
importing these records I used Access to put the records into a table and
DTS to get the table into SQL server. This process took maybe 1/2 hour.
I then tried using .NET to import the data by grabbing the textfile into a
datatable in batches of 250 records and then updating to the SQL server
database with a DataAdaptor. This process took about 5 hours which is kind
of unacceptable.
Is there anyway to speed up this process? Can .NET access any bulk insert
functionality? I need to automate this process into a service and would
like to get it to run on the same order as the manual process, and I would
like to use a service programmed in .NET. If I could even call SQL to acess
bulk insert functionality on SQL Server that would be fine, but SQL Server
does not seem to support fixed width text files.ADO.NET doesn't have a built-in bulk insert capability. As I see it you have
several options to do this. But the best bet is to use the SQLCommand object
to call the T-SQL BULK INSERT statement. Other options include creating a
bcp format file and calling that from the .NET program. Likewise, you could
create a DTS package and call that use the .NET COM interop classes or you
could use DMO's BulkInsert object.
Michael O.
"Todd Burry" <tburry@.nospam.com> wrote in message
news:Xns94CAA6111D5C1mythstoddburrycom@.2
07.46.248.16...
> Hi all, I hope this is the correct place for this post.
> I need to write a service that will bulk insert 3 million+ records from a
> fixed width formatted text file to an SQL Server table. When I started
> importing these records I used Access to put the records into a table and
> DTS to get the table into SQL server. This process took maybe 1/2 hour.
> I then tried using .NET to import the data by grabbing the textfile into a
> datatable in batches of 250 records and then updating to the SQL server
> database with a DataAdaptor. This process took about 5 hours which is kind
> of unacceptable.
> Is there anyway to speed up this process? Can .NET access any bulk insert
> functionality? I need to automate this process into a service and would
> like to get it to run on the same order as the manual process, and I would
> like to use a service programmed in .NET. If I could even call SQL to
acess
> bulk insert functionality on SQL Server that would be fine, but SQL Server
> does not seem to support fixed width text files.|||Thanks for the help. I didn't realize that the BULK INSERT supports fixed
width files. That will work just fine.

Need to bulk insert quickly with .NET

Hi all, I hope this is the correct place for this post.
I need to write a service that will bulk insert 3 million+ records from a
fixed width formatted text file to an SQL Server table. When I started
importing these records I used Access to put the records into a table and
DTS to get the table into SQL server. This process took maybe 1/2 hour.
I then tried using .NET to import the data by grabbing the textfile into a
datatable in batches of 250 records and then updating to the SQL server
database with a DataAdaptor. This process took about 5 hours which is kind
of unacceptable.
Is there anyway to speed up this process? Can .NET access any bulk insert
functionality? I need to automate this process into a service and would
like to get it to run on the same order as the manual process, and I would
like to use a service programmed in .NET. If I could even call SQL to acess
bulk insert functionality on SQL Server that would be fine, but SQL Server
does not seem to support fixed width text files.
ADO.NET doesn't have a built-in bulk insert capability. As I see it you have
several options to do this. But the best bet is to use the SQLCommand object
to call the T-SQL BULK INSERT statement. Other options include creating a
bcp format file and calling that from the .NET program. Likewise, you could
create a DTS package and call that use the .NET COM interop classes or you
could use DMO's BulkInsert object.
Michael O.
"Todd Burry" <tburry@.nospam.com> wrote in message
news:Xns94CAA6111D5C1mythstoddburrycom@.207.46.248. 16...
> Hi all, I hope this is the correct place for this post.
> I need to write a service that will bulk insert 3 million+ records from a
> fixed width formatted text file to an SQL Server table. When I started
> importing these records I used Access to put the records into a table and
> DTS to get the table into SQL server. This process took maybe 1/2 hour.
> I then tried using .NET to import the data by grabbing the textfile into a
> datatable in batches of 250 records and then updating to the SQL server
> database with a DataAdaptor. This process took about 5 hours which is kind
> of unacceptable.
> Is there anyway to speed up this process? Can .NET access any bulk insert
> functionality? I need to automate this process into a service and would
> like to get it to run on the same order as the manual process, and I would
> like to use a service programmed in .NET. If I could even call SQL to
acess
> bulk insert functionality on SQL Server that would be fine, but SQL Server
> does not seem to support fixed width text files.
|||Thanks for the help. I didn't realize that the BULK INSERT supports fixed
width files. That will work just fine.

Need suggestion on loading a 50 million records table from Oracle

All,

I need to load a 50 million records table monthly. Any suggestion about the best/fast way to do it?

Thanks a lot

All

I tried today; it used 8 hours to load the data just from the Oracle table to the staging table on sql server 2005. It is unacceptable!!! There are must be a better way to do it.

In the data flow, there is only an OLE DB source (from Oracle) -> data conversion transformation(convert the CLOB type) > OLE DB target (SQL server 2005)

Do I need to do more in the data flow? What should I do?

Many Thanks

|||

There IS a better way of doing it. Scott Barrett has done loads of work on this sort of stuff and has written some great blogs on it. Especially this one: http://microsoftdw.blogspot.com/2005/11/final-storyhow-to-get-data-out-of.html.

Search this forum as well - you'll find loads of good stuff.

Lastly, if you do only one thing make sure its to read this fabulous post (http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx) on SSIS and Oracle from Donald Farmer, Group Program Manager for SSIS.

-Jamie

sql

Need input...Creating Indexes on 37 million row table...

Hello, all. Looking for suggestions, input,
recommendations, etc. I'm running SQL 2K.
I have a table with 37 million rows that I need to create
about 5 new indexes on. The columns already exist. The
db is used for datawarehousing, and contains static
data...the only update is a monthly insert of about 300K
records. Other than that, users only query it all day.
The db is also running in Simple Recovery mode (no need to
log any transactions).
Realizing this will take a LONG time to run, I'd like to
find out the best/most efficient way/with minimum downtime
to get this done. Luckily, I have the luxury of
restricted access to the db while I perform this, if need
be.
So, I'd like to hear from the gurus on how to do this.
Thanks
RozThere really is no faster way to create an index other than to make sure
your log file (even in simple mode) is on a separate raid array than the
data or tempdb. If you have tempdb on a separate array than the data you
can specify the sort in tempdb option to speed it up some.
Andrew J. Kelly SQL MVP
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2746101c4636e$82f9ae10$a501280a@.phx
.gbl...
> Hello, all. Looking for suggestions, input,
> recommendations, etc. I'm running SQL 2K.
> I have a table with 37 million rows that I need to create
> about 5 new indexes on. The columns already exist. The
> db is used for datawarehousing, and contains static
> data...the only update is a monthly insert of about 300K
> records. Other than that, users only query it all day.
> The db is also running in Simple Recovery mode (no need to
> log any transactions).
> Realizing this will take a LONG time to run, I'd like to
> find out the best/most efficient way/with minimum downtime
> to get this done. Luckily, I have the luxury of
> restricted access to the db while I perform this, if need
> be.
> So, I'd like to hear from the gurus on how to do this.
> Thanks
> Roz

Need input...Creating Indexes on 37 million row table...

Hello, all. Looking for suggestions, input,
recommendations, etc. I'm running SQL 2K.
I have a table with 37 million rows that I need to create
about 5 new indexes on. The columns already exist. The
db is used for datawarehousing, and contains static
data...the only update is a monthly insert of about 300K
records. Other than that, users only query it all day.
The db is also running in Simple Recovery mode (no need to
log any transactions).
Realizing this will take a LONG time to run, I'd like to
find out the best/most efficient way/with minimum downtime
to get this done. Luckily, I have the luxury of
restricted access to the db while I perform this, if need
be.
So, I'd like to hear from the gurus on how to do this.
Thanks
Roz
There really is no faster way to create an index other than to make sure
your log file (even in simple mode) is on a separate raid array than the
data or tempdb. If you have tempdb on a separate array than the data you
can specify the sort in tempdb option to speed it up some.
Andrew J. Kelly SQL MVP
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2746101c4636e$82f9ae10$a501280a@.phx.gbl...
> Hello, all. Looking for suggestions, input,
> recommendations, etc. I'm running SQL 2K.
> I have a table with 37 million rows that I need to create
> about 5 new indexes on. The columns already exist. The
> db is used for datawarehousing, and contains static
> data...the only update is a monthly insert of about 300K
> records. Other than that, users only query it all day.
> The db is also running in Simple Recovery mode (no need to
> log any transactions).
> Realizing this will take a LONG time to run, I'd like to
> find out the best/most efficient way/with minimum downtime
> to get this done. Luckily, I have the luxury of
> restricted access to the db while I perform this, if need
> be.
> So, I'd like to hear from the gurus on how to do this.
> Thanks
> Roz

Need input...Creating Indexes on 37 million row table...

Hello, all. Looking for suggestions, input,
recommendations, etc. I'm running SQL 2K.
I have a table with 37 million rows that I need to create
about 5 new indexes on. The columns already exist. The
db is used for datawarehousing, and contains static
data...the only update is a monthly insert of about 300K
records. Other than that, users only query it all day.
The db is also running in Simple Recovery mode (no need to
log any transactions).
Realizing this will take a LONG time to run, I'd like to
find out the best/most efficient way/with minimum downtime
to get this done. Luckily, I have the luxury of
restricted access to the db while I perform this, if need
be.
So, I'd like to hear from the gurus on how to do this.
Thanks
RozThere really is no faster way to create an index other than to make sure
your log file (even in simple mode) is on a separate raid array than the
data or tempdb. If you have tempdb on a separate array than the data you
can specify the sort in tempdb option to speed it up some.
--
Andrew J. Kelly SQL MVP
"Roz" <anonymous@.discussions.microsoft.com> wrote in message
news:2746101c4636e$82f9ae10$a501280a@.phx.gbl...
> Hello, all. Looking for suggestions, input,
> recommendations, etc. I'm running SQL 2K.
> I have a table with 37 million rows that I need to create
> about 5 new indexes on. The columns already exist. The
> db is used for datawarehousing, and contains static
> data...the only update is a monthly insert of about 300K
> records. Other than that, users only query it all day.
> The db is also running in Simple Recovery mode (no need to
> log any transactions).
> Realizing this will take a LONG time to run, I'd like to
> find out the best/most efficient way/with minimum downtime
> to get this done. Luckily, I have the luxury of
> restricted access to the db while I perform this, if need
> be.
> So, I'd like to hear from the gurus on how to do this.
> Thanks
> Roz