0

I have an old SQL script that is currently run by loading it into SQL Server Management studio and running it. I'd like to clean this up by turning it into a series of functions that are stored in the database itself.

The basic sequence of steps that the current code does is like this:

(Miles of SQL logic)

  1. Create a temporary table
  2. BULK INSERT from a CSV file into the temporary table
  3. Massage the data
  4. Merge the data into the "real" table
  5. DROP the temporary table

(Miles of SQL logic)

I'd like to wrap steps 1-5 in a function, but I'm stuck at how to perform a BULK INSERT when you can't BULK INSERT into a table variable, and you're also not allowed to create temporary tables from within a function.

So what's the right way to fix this issue?

Thanks!

Community
  • 1
  • 1
ThoughtProcess
  • 449
  • 1
  • 6
  • 21
  • 2
    That's not what functions are intended for, why not do it in a stored procedure which is a single addressable entity where there are no such restrictions on temp tables? – Alex K. Jan 17 '14 at 17:21
  • 1
    As well as not being able to use temp tables you won't be able to do step 4 in a function either. They can't modify data in external tables. – Martin Smith Jan 17 '14 at 17:26
  • 2
    Or a DTS/SSIS package is ideal for this type of scenario. – NigelK Jan 17 '14 at 17:28

1 Answers1

1

As already mentionned in the comment, the solution that differs the less to yours is doing that in a stored procedure rather than in a functoin, which is intended to modify the content of a table.

On a short term perspective, this should be clearly the easiest to implement for you but on a long term learnin SSIS could be a good investment.

C.Champagne
  • 5,381
  • 2
  • 23
  • 35