SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to delete first couple of rows without knowing column name


How to delete first couple of rows without knowing column name

Author
Message
//Ravi
//Ravi
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 451
hi,

I am trying to delete first 10 rows from an excel table by using SSIS. I am just using a simple sql command to delete first 10 rows from the excel file.

Would anyone know a sql query to just delete first 10rows from the table without knowing column names?

I was thinking about using "DELETE FROM 'table' where" and after where I don't what do put.

Please help.
Thanks.

Ravi.

------------
Smile
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14928 Visits: 9006
Why are you using SQL to delete from an Excel spreadsheet? What are you trying to accomplish?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
//Ravi
//Ravi
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 451
Brandie Tarvin (1/25/2008)
Why are you using SQL to delete from an Excel spreadsheet? What are you trying to accomplish?



I have to import data from an excel file. First 10 rows of the excel file are just random description of client that I don't need. I tried to import all the data in one table, but it gives metadata mismatch error. It also tries to tuncate the data. I think what SSIS does is it looks at first couple of rows in excel and tries to determine data type, and it ends up deciding on the wrong ones.

The only way I was able to run the package is manually deleting first 10 rows of the table. But I have to automate it. Every month someone else will run the package once I create it. Let me know if you have any other ideas?

Take a look at pictures. It'll give you more idea what i'm talking about. One screen shot is the error and other one is actual excel file.

Thanks.
Ravi.

------------
Smile
Attachments
Error.bmp (17 views, 2.00 MB)
Excel File.bmp (16 views, 1.00 MB)
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12627 Visits: 18584
Have you looked at ignoring the data errors or redirecting them to another table during import? That's one of the options you can set up on your Excel Data source.

Also - during the bulk import process you can specify where to start (starting row).

Redirect row is a bit more work, but better in the long run, since it will help you catch any data errors that might creep in within the stuff you DO want to see.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14928 Visits: 9006
Without knowing at least one column name, there's no good way to do a delete other than manually before the run of the package.

I recommend redirecting the rows out of the data source as previously suggested.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
//Ravi
//Ravi
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 451
Thank you so much for everyone's suggestions.

Seems like Bulk Import would work. I tired it, but it says I don't have permission to do a bulk import. The DBA here can't figure out how to give me access (it kinda sucks).

I'm little new to SSIS, so i've never looked redirecting rows. I will go through a tutorial today on redirecting rows. Thanks for your guidance. I think after going through the tutorial, I should be okay.

Ravi.Smile

------------
Smile
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14928 Visits: 9006
Ravi,

It might be the DBA doesn't want to give that kind of access. But just in case the DBA really can't figure it out, tell him/her to grant you access to the BulkAdmin server role. Then you should be able to use Bulk Insert (assuming there are no other DENY permissions preventing you from doing this).

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search