如何将游标中的结果插入到temptable中 [英] How to insert result from a cursor into a temptable

问题描述

我有大约60个我正在查询的具有相同表格的数据库;但是，我希望将查询结果插入到临时表中，以便我可以使用存储在该临时表中的结果创建格式良好的报告。我如何实现这一目标？下面是我的查询，循环遍历每个数据库并运行查询以选择所有活动订单：

  DECLARE   @ db_name   Varchar （ 100 ）
 
  DECLARE   @ SQL   Varchar （ 3000 ）
 
  DECLARE  c_db_names  CURSOR   FOR  
  SELECT  name 
  FROM  master.dbo.sysdatabases 
  WHERE  name  NOT   IN （'  master'，'  AdventureWorks2008'，  TestDB'，' 开发'，'  Northwind' ）  -   可能需要排除更多数据库 
 
 < span class =code-keyword> OPEN  c_db_names 
 
  FETCH  c_db_names  INTO   @ db_name  
 
  WHILE   @@ Fetch_Status  =  0  
  BEGIN  
 
  SET   @ SQL  = '  SELECT OrderID，OrderStatus，OrderDetails，FulfillmentDate，ShippingDate，AddDate来自
' +  @ db_name  + '  .dbo.ActiveOrders inner join' +  @ db_name  + ' 。dbo.FinalShipmentNumbers on' +  @ db_name  + ' 。dbo.ActiveOrders.OrderDetails = 
' +  @ db_name  + ' 。dbo.FinalShipmentNumbers.OrderID其中addDate位于''2016年1月30日''和''2016年2月26日''' 
 
  SELECT   @ db_name  作为 Client_Name 
  EXEC （ @ SQL ）
  FETCH  c_db_names  INTO   @ db_name  
  END  
 
  CLOSE  c_db_names 
  DEALLOCATE  c_db_names 

我尝试了什么：



我试图将查询结果插入临时表

解决方案

请尝试这种方式：

  DECLARE   @ c_db_names   AS   TABLE  
（
 ID  INT   IDENTITY （ 1 ， 1 ），
名称sysname 
）
 
  DECLARE   @ Records   AS   TA BLE  
（
 ID  INT   IDENTITY （ 1 ， 1 ），
名称sysname，
 OrderID  NVARCHAR （ 10 ），
 OrderStatus  NVARCHAR （ 10 ），
 OrderDetails  NVARCHAR （ 10 ）， 
 FulfillmentDate  DATETIME ，
 ShippingDate  DATETIME ，
 AddDate  DATETIME  
）
 
 
  DECLARE   @ SQL   NVarchar （ 3000 ）
  DECLARE   @ ID   INT  
  DECLARE   @ DBName  sysname 
 
  SET   NOCOUNT   ON  
 
  INSERT   @ c_db_names  
  SELECT  name 
  FROM  master.dbo.sysdatabases 
  WHERE  name  NOT   IN （'  master'，< span class =code-string>'  AdventureWorks2008'，'  TestDB'，' 开发'，'  Northwind'）  -   可能需要排除更多数据库 
 
  SELECT   @ID  = COUNT（ 1 ） FROM   @c_db_names  
 
  WHILE （ @ ID >  0 ）
 
  BEGIN  
 
  SELECT   @ DBName  = name 
  FROM  < span class =code-sdkkeyword> @ c_db_names  
  WHERE  ID =  @ ID  
  SET   @ ID  =  @ ID   -   1  
 
  SET   @ SQL  = '  SELECT''' +  @ DBName  + ' ''AS [Name]，AO.OrderID，AO.OrderStatus ，AO.OrderDetails，FS.FulfillmentDate，FS.ShippingDate，FS.AddDate' +  CHAR （ 13 ）+  CHAR （ 10 ）
 + < span class =code-string>'  FROM' +  @ DBName  + ' 。dbo.ActiveOrders AS AO' +  CHAR  （ 13 ）+  CHAR （ 10 ） 
 + '  INNER JOIN' +  @ DBName  + ' 。dbo.FinalShipmentNumbers AS FS' +  CHAR （ 13 ）+  CHAR （ 10 ）
 + '  ON' +  CHAR （ 13 ）+  CHAR （ 10  ）
 + '  AO.OrderDetails = FS.OrderID其中addDate介于''1/30/2016''之间和''2/26/2016''' +  CHAR （ 13 ）+ < span class =code-keyword> CHAR （ 10 ）+  CHAR （ 13 ）+  CHAR （ 10 ）
 
  PRINT   @ SQL ; 
  INSERT   INTO   @ Records  
  EXEC  SP_EXECUTESQL  @ SQL  
 
  END  
 
  SET   NOCOUNT   OFF  
  SELECT  *  FROM   @记录 

I have around 60 databases that I am querying against that have the same tables; however, I want the result of my query to be inserted into a temp table so that I can create a well formatted report using the result stored in that temp table. How do I achieve that? Below is my query that loops through each database and run the query to select all active orders:

DECLARE @db_name Varchar(100)

DECLARE @SQL Varchar(3000)

DECLARE c_db_names CURSOR FOR
SELECT name 
FROM master.dbo.sysdatabases
WHERE name NOT IN('master','AdventureWorks2008','TestDB','Development','Northwind') --might need to exclude more databases

OPEN c_db_names

FETCH c_db_names INTO @db_name

WHILE @@Fetch_Status = 0
BEGIN   

SET @SQL = 'SELECT OrderID, OrderStatus, OrderDetails, FulfillmentDate, ShippingDate , AddDate from 
       '+ @db_name +'.dbo.ActiveOrders inner join '+ @db_name +'.dbo.FinalShipmentNumbers on '+ @db_name +'.dbo.ActiveOrders.OrderDetails = 
       '+ @db_name +'.dbo.FinalShipmentNumbers.OrderID where addDate between ''1/30/2016'' and ''2/26/2016'''

SELECT @db_name As Client_Name
EXEC(@SQL)      
FETCH c_db_names INTO @db_name
END

CLOSE c_db_names
DEALLOCATE c_db_names

What I have tried:

I have tried to insert the result of my query into a temp Table

解决方案

Please try this way:

DECLARE @c_db_names AS TABLE
(
ID INT IDENTITY(1,1),
name sysname
)

DECLARE @Records AS TABLE
(
ID INT IDENTITY(1,1),
name sysname,
OrderID NVARCHAR(10), 
OrderStatus NVARCHAR(10), 
OrderDetails NVARCHAR(10), 
FulfillmentDate DATETIME, 
ShippingDate DATETIME, 
AddDate DATETIME
)


DECLARE @SQL NVarchar(3000)
DECLARE @ID INT
DECLARE @DBName sysname

SET NOCOUNT ON

INSERT @c_db_names
SELECT name 
FROM master.dbo.sysdatabases
WHERE name NOT IN('master','AdventureWorks2008','TestDB','Development','Northwind') --might need to exclude more database

SELECT @ID = COUNT(1) FROM @c_db_names

WHILE (@ID > 0)

	BEGIN

		SELECT @DBName = name
		FROM @c_db_names
		WHERE ID = @ID
		SET @ID = @ID - 1

		SET @SQL =    'SELECT ''' + @DBName + ''' AS [Name], AO.OrderID, AO.OrderStatus, AO.OrderDetails, FS.FulfillmentDate, FS.ShippingDate, FS.AddDate' + CHAR(13) + CHAR(10) 
					+ 'FROM ' + @DBName +'.dbo.ActiveOrders AS AO ' + CHAR(13) + CHAR(10) 
					+ 'INNER JOIN '+ @DBName +'.dbo.FinalShipmentNumbers AS FS ' + CHAR(13) + CHAR(10) 
					+ 'ON' + CHAR(13) + CHAR(10)
					+ ' AO.OrderDetails = FS.OrderID where addDate between ''1/30/2016'' and ''2/26/2016'''  + CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)

	   PRINT @SQL;
	   INSERT INTO @Records
	   EXEC SP_EXECUTESQL @SQL

	END

SET NOCOUNT OFF
SELECT * FROM @Records

这篇关于如何将游标中的结果插入到temptable中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！