I have a large table that was imported from a CSV using the import wizard. I have a query that selects from this table and replaces all of the string "code" columns with integer ids from other dimension tables and performs some transformations on some other columns. This all works well.
I need to insert the results of this query into a table in the database that has the required foreign key links and indices. This is also the table that the SQL objects (sprocs, functions, views) use to access the data.
My issue is that the insert takes forever (1.2 hrs) before it eventually dies by complaining about the size of the transaction log. I don't have access to log onto the server. I only have access through SSMS with db_owner rights on the database in question.
My questions are:
- How can I perform the insert without logging it in the transaction log? I know there is
OPENROWSET(BULK ...)but these require a file.
- Would the insert go faster if I drop the indices? I think this would break the execution plan of the existing SQL objects that use the destination table. Is this advisable if there is a clustered index on the destination table?
- Is not logging the insert the correct way to go or should I use some other method?