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 |
|
Mamsaac
Starting Member
9 Posts |
 Posted - 2011-06-06 : 19:41:53
|
| I have a table with a clustered key that has 3 columns, where the three of them are generated with default values (they have to do with date and hour and minute in int values :)).I got files that I already import with BCP, but they do NOT include those three columns, since they are created by the database.I was wondering if it's possible to use the BCP hint ORDER(cols...) for such task. The file is ordered in the sense that the way it is imported is just the order it shall have, but the composite key provided is not IN the file.EDIT: I believe this might also have to do with SSIS, since SSIS uses BCP after all. Would it work with SSIS?Thanks for the input and I will clarify if I'm not giving enough information. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-07 : 04:38:23
|
| Simplest way is to create a view on the table excluding those columns and import into that.You could also use a format file - but use that as a last resort.SSIS selects the columns to import.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Mamsaac
Starting Member
9 Posts |
Posted - 2011-06-07 : 19:20:44
|
| I'm not talking about -that- issue. I'm already using a format file for doing so.I'm talking about the hints one can provide for optimization and if the order hint would be of use for this situation. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-08 : 06:41:43
|
| I doubt it - you don't have anything that the ordering hint would drop the check of. Why not try it and see?(I would get rid of the format file as well).Have you removed indexes and have the database in bulk logged mode?Do you actually have an issue that you are looking to solve or is this a theoretical question?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Mamsaac
Starting Member
9 Posts |
Posted - 2011-06-08 : 18:21:28
|
| Issue.There's no way removing the indexes is a good idea in the situation. The database gets several billion rows per day (it gets cleaned up every 8 days), so removing and adding the indexes actually causes a big slow down.Tons of data is stored on a file, then it's batched into the database.Why do you dislike using the format file? Is there any special reason? I'm rather curious. |
|
|
|
|
|
|
Order hint in bcp for auto-generated fields