超长位置/在语句中-更好的选择? [英] Extra Long Where/In Statement - Better option?

问题描述

我有大约13,000个AccountID，我需要使用左联接从多个数据表中提取各种数据.accountID的总数为百万.我没有对该服务器的写访问权，但我想知道是否有一种方法可以无论如何创建自定义/临时表并对其进行联接，而不是在(....) 陈述.该accountID当前位于单个Excel列中，因此我必须以某种方式将其返回到服务器中.

I've got about 13,000 AccountIDs that I need to pull various data from several data tables using left-joins. The total # of accountIDs is in the millions. I don't have write-access to the server but I was wondering if there was a way I could maybe create a custom/temporary table anyway and do a join to that rather than writing a really, really long Where AccountID in (.....) statement. The accountIDs are currently in a single Excel column so I'd have to get them back in the server somehow.

有想法吗?

推荐答案

您可以使用OPENDATASOURCE来访问Excel文件，但是有人将不得不将Excel文件推送到服务器(不理想).

You can use OPENDATASOURCE to access your Excel file, but someone will have to push you the Excel file to the server (not ideal).

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\DataFolder\Documents\TestExcel.xls;Extended Properties=EXCEL 5.0')...[Sheet1$] ;

您可以要求您的DBA团队在本地计算机上安装SQl Server客户端，并设置与实时服务器的链接服务器，一切就绪.要求他们设置所需数据的视图，并授予您访问该视图的权限.我想，这就是您所需要的.

You can ask your DBA team to install a SQl Server Client on your local machine and set up a linked server to the live server, and you are all set. Ask them to set up a view of the data you need, and give you access to the view. That's all you need, I guess.

这篇关于超长位置/在语句中-更好的选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！