0

I created SQLCLR stored procedure that retrieve large amount of data from external service. Can I bulk copy the data to a new table in the same SQLCLR stored procedure?

Yshayy
  • 325
  • 2
  • 10

1 Answers1

1

I'll zoom out here to the general problem of how to expose data from a service inside SQL. What can might do is rewrite your stored procedure as a Table Valued CLR function that streams the results. Then you can use like this:

insert into MyTable(id, name)
select id, name from dbo.MyTableFunc(agrs)

The infrastructure for this is well optimized, I used it and was quickly able to get it to some 3k rows per second when I stopped optimizing, and the bottleneck was in getting the rows over the network.

The upside of exposing the service as a function is that you also directly use it in queries and joins, no need to store it in to a table on disk first (extra step with slow disks, transaction log, locks yadayada). It also cancels gracefully and you can do top 100 etc.

If you tell us more/ give some code we can help more.

gjvdkamp
  • 9,929
  • 3
  • 38
  • 46
  • Actually, my first try was using CLR table-UDF for the same reasons you mentioned. But I had a problem, I needed the ability to project only some of the columns and UDF have predefined schema. So, I tried solving it with a UDF that return the key columns and XML column containing the additional columns I wanted to project. But it made the sqlclr radically slower because of the xml serialization so I changed it to using stored procedure. Right now, I'm using stored procedure without sqlbulkcopy (simple transcation) and it works fairly well, but it will probably be a problem for 1m+ rows. – Yshayy Feb 24 '13 at 14:33