Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AdiI
Starting Member
31 Posts |
 Posted - 2010-04-22 : 01:16:39
|
| Hello, i'm new in sql server world. i have been a issue hope u guys help me out. i have a table comprising 145 columns and 12 million rows. i want to extract this table in text file. Data in a table is not store in sorted form. i want to extract data in file in sorted order. For this i have created a view like create view TRans1 as select * from report order by trans_id. it give me error to use top than i use the following create view TRans1 as select top 1200000 * from report order by trans_idView has been create successfully. when i issue the following BCP command it select all records in more then two hours and one hour to extract. i know that is there any better way to this rapidly becoz it take space in tepmdb 32gb for select statement. plz help how to tune this processmy 2nd question i have carriage return character in data whose break my records in next line plz tell how to overcome this issue in bcp. here is my BCP which i use to extract data declare @sql varchar(8000)select @sql = 'bcp TEST1..TRANS in e:\ReportItem.txt -c -t"|" -T -S'+ @@servernameexec master..xp_cmdshell @sql adeel |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-22 : 01:26:05
|
| You can use TOP 100 PERCENT so that you don't have to hard-code the row count.The method that you are using is the best and fastest method. It is using tons of space in tempdb because that's where the ORDER BY happens. If you removed the business rule to sort it, then it wouldn't happen. The only way to tune your process is to index the table or the view (indexed views have many rules and aren't used all that often). How often do you have to run this process? If it's often, then you may want to consider putting the clustered index on your trans_id column.I use this for my row terminators: -r\r\nTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
AdiI
Starting Member
31 Posts |
Posted - 2010-04-22 : 01:47:23
|
| i have tried these -r\r\n but nothing happen record still break to next line will u plz embed this switch in my BCP commandadeel |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
AdiI
Starting Member
31 Posts |
Posted - 2010-04-22 : 02:27:18
|
| you mean i can not handle carriage return in BCP shell. so what are the meaning if these switches that you mention in previous post. i have use pipe sign as a delimiter. i have also check if pipe found in data replace it with space. i have to put another replace for carriage returnthis will degrade my performance. any other optimal way to thisadeel |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-22 : 02:30:14
|
| The -r switch is to provide the row terminator. \r\n means carriage return and line feed (forget which order they are in). I was suggesting \r\n in case your data contains either \r or \n and not both. BCP can handle pretty much anything, but the problem is that your data contains the returns. What do you intend to do with this file? Perhaps if we knew the intention we'd be able to help out better.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
AdiI
Starting Member
31 Posts |
Posted - 2010-04-22 : 03:07:20
|
| it is our new client database. we have to extract it data in file then we will perform transformation process on this file and load into database where is data use our application for analytical Reporting and man more. Now as a DBA its my job to extract the data from client system and load in text file and deliver to transformation team. but this carriage return break line and transformation team complain that we are getting miss match in records when i look into this matter i found there is carriage in data.this script run daily to extract incremented data after transformation i would be load in dataware house.adeel |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2010-04-22 : 09:34:04
|
| Is the reporting database on another SQL server? The same one or a linked one possibly? There seems to be alternatives if that's the case. Perhaps a DTS/SSIS package to move the data? As Tara stated bcp is going to be faster but a package could provide additional flexibility to getting exactly what you need in an easier way.Terry-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher |
|
|
|
|
|
|
BCP PERFORMANCE ISSUE