Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to delete first couple of rows without knowing column name Expand / Collapse
Author
Message
Posted Friday, January 25, 2008 11:40 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:04 PM
Points: 97, Visits: 354
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.


------------
:)
Post #447730
Posted Friday, January 25, 2008 12:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 6:34 AM
Points: 7,108, Visits: 6,274
Why are you using SQL to delete from an Excel spreadsheet? What are you trying to accomplish?



Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #447746
Posted Friday, January 25, 2008 12:45 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:04 PM
Points: 97, Visits: 354
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.


------------
:)


  Post Attachments 
Error.bmp (10 views, 2.36 MB)
Excel File.bmp (11 views, 1.36 MB)
Post #447767
Posted Friday, January 25, 2008 12:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:15 PM
Points: 7,113, Visits: 14,965
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?
Post #447772
Posted Friday, January 25, 2008 3:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 6:34 AM
Points: 7,108, Visits: 6,274
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #447849
Posted Monday, January 28, 2008 7:24 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:04 PM
Points: 97, Visits: 354
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.:)


------------
:)
Post #448292
Posted Monday, January 28, 2008 7:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 6:34 AM
Points: 7,108, Visits: 6,274
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #448302
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse