August 11, 2008 at 3:36 am
Hi Experts,
My problem is that i have a table containing 10 million records i want to import those data to text files .Am not able to do it as a whole so am planning to divide it something like 1million records in one file and another 1 million in another file like that.So how can i achieve the same.There is no column specifying number or something which is helpful in using between clause.
TIA
August 11, 2008 at 4:15 am
Ratheesh.K.Nair (8/11/2008)
Hi Experts,My problem is that i have a table containing 10 million records i want to import those data to text files .Am not able to do it as a whole so am planning to divide it something like 1million records in one file and another 1 million in another file like that.So how can i achieve the same.There is no column specifying number or something which is helpful in using between clause.
TIA
Write a simple C# application like
// modify the connection string
SqlConnection connection = new SqlConnection("Server=.;Database=tempdb;Trusted_Connection=True;");
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM myVeryBigTable"; // <-- modify the query
connection.Open();
StreamWriter writer = null;
try
{
using (SqlDataReader reader = command.ExecuteReader())
{
string fileNameTemplate = @"C:\tmp\foo_{0}.txt"; // <-- Modify the file template
int rowCounter = 0;
int fileId = 0;
while (reader.Read())
{
if (rowCounter%1000000 == 0)
{
if (writer != null)
{
writer.Close();
}
writer = new StreamWriter(String.Format(fileNameTemplate, fileId++));
rowCounter = 0;
}
for (int i = 0; i < reader.FieldCount; i++)
{
writer.Write(reader.ToString()); // whatever format you want :)
writer.Write(",");
}
writer.WriteLine();
rowCounter++;
}
}
}
finally
{
connection.Dispose();
command.Dispose();
if(writer != null)
{
writer.Close();
}
}
Regards,
Andras
August 11, 2008 at 5:27 am
Or alternatively, you could set up an SSIS package to export "TOP 10000000 FROM " set an exported flag on these records and loop until the count on the query for the records to be exported is zero
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
August 12, 2008 at 1:17 am
Thanks for the Reply,
Is there any way other than the above mentioned.Is it possible to use select statement using rownum or something like that??
August 12, 2008 at 1:33 am
Ratheesh.K.Nair (8/12/2008)
Thanks for the Reply,Is there any way other than the above mentioned.Is it possible to use select statement using rownum or something like that??
You can introduce a "virtual" row number by doing something like:
WITH orderedTable
AS ( SELECT row_number() OVER ( ORDER BY name ) AS nr
, *
FROM sys.all_columns
)
SELECT *
FROM orderedTable
WHERE nr >= 1000 AND nr < 2000
However, you will need to make sure that the OVER part you can specify something that will order the data in a deterministic way. (and hopefully order it fast!, so something like the clustered columns, if you cannot use that, then an index, ... ) You would need to execute this query a few times with different parameters. This is when the correct "OVER" part kicks in, I'm sure you would like to avoid a full table scan every time 🙂
Regards,
Andras
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply