發表者討論內容

冷日
(冷日) 發表時間：2022/7/19 3:31

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15766

[轉貼]Microsoft SQL Server 跨資料庫查詢

跨資料庫查詢

從 SQL Server 2014 (12.x) 開始，記憶體優化資料表不支援跨資料庫交易。您無法在同時存取記憶體最佳化資料表的相同交易或相同查詢中存取另一個資料庫。您無法輕鬆地從某一個資料庫的資料表中，將資料複製到另一個資料庫中的記憶體最佳化資料表。

資料表變數並非交易式。因此，記憶體最佳化資料表變數可以在跨資料庫查詢中使用，也因而有助於將某個資料庫中的資料移至另一個資料庫的記憶體最佳化資料表中。您可以使用兩筆交易。在第一筆交易中，將資料從遠端資料表插入變數中。在第二筆交易中，從變數中將資料插入本機記憶體最佳化資料表。如需記憶體最佳化資料表變數的詳細資訊，請參閱使用記憶體最佳化加快暫存資料表與資料表變數的速度。

本例說明將某個資料庫的資料傳送至不同資料庫的記憶體最佳化資料表的方法。

建立測試物件。在 SQL Server Management Studio 中執行下列 Transact-SQL。

USE master;
GO

SET NOCOUNT ON;

-- Create simple database
CREATE DATABASE SourceDatabase;
ALTER DATABASE SourceDatabase SET RECOVERY SIMPLE;
GO

-- Create a table and insert a few records
USE SourceDatabase;

CREATE TABLE SourceDatabase.[dbo].[SourceTable] (
	[ID] [int] PRIMARY KEY CLUSTERED,
	[FirstName] nvarchar(8)
	);

INSERT [SourceDatabase].[dbo].[SourceTable]
VALUES (1, N'Bob'),
	(2, N'Susan');
GO

-- Create a database with a MEMORY_OPTIMIZED_DATA filegroup

CREATE DATABASE DestinationDatabase
 ON  PRIMARY
( NAME = N'DestinationDatabase_Data', FILENAME = N'D:\DATA\DestinationDatabase_Data.mdf',	SIZE = 8MB),
 FILEGROUP [DestinationDatabase_mod] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'DestinationDatabase_mod', FILENAME = N'D:\DATA\DestinationDatabase_mod', MAXSIZE = UNLIMITED)
 LOG ON
( NAME = N'DestinationDatabase_Log', FILENAME = N'D:\LOG\DestinationDatabase_Log.ldf', SIZE = 8MB);

ALTER DATABASE DestinationDatabase SET RECOVERY SIMPLE;
GO

USE DestinationDatabase;
GO

-- Create a memory-optimized table
CREATE TABLE [dbo].[DestTable_InMem] (
	[ID] [int] PRIMARY KEY NONCLUSTERED,
	[FirstName] nvarchar(8)
	)
WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA );
GO

嘗試跨資料庫查詢。在 SQL Server Management Studio 中執行下列 Transact-SQL。

INSERT [DestinationDatabase].[dbo].[DestTable_InMem]
SELECT * FROM [SourceDatabase].[dbo].[SourceTable]

您應該會收到下列錯誤訊息：

訊息 41317，層級 16，狀態 5
存取記憶體最佳化資料表或原生編譯模組的使用者交易，無法存取多位使用者資料庫或資料庫模型與 msdb，且不能寫入 master。

建立記憶體最佳化資料表類型。在 SQL Server Management Studio 中執行下列 Transact-SQL。

USE DestinationDatabase;
GO

CREATE TYPE [dbo].[MemoryType]
    AS TABLE
    (
	[ID] [int] PRIMARY KEY NONCLUSTERED,
	[FirstName] nvarchar(8)
    )
    WITH
        (MEMORY_OPTIMIZED = ON);
GO

重新嘗試跨資料庫查詢。這一次，來源資料會先傳送給記憶體最佳化資料表變數。接著 table 變數的資料會傳輸至記憶體最佳化資料表。

    -- Declare table variable utilizing the newly created type - MemoryType
    DECLARE @InMem dbo.MemoryType;

    -- Populate table variable
    INSERT @InMem SELECT * FROM SourceDatabase.[dbo].[SourceTable];

    -- Populate the destination memory-optimized table
    INSERT [DestinationDatabase].[dbo].[DestTable_InMem] SELECT * FROM @InMem;
    GO

另請參閱
移轉至 In-Memory OLTP

原文出處：
跨資料庫查詢 - SQL Server | Microsoft Docs

回覆

冷日
(冷日) 發表時間：2022/7/19 3:37

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15766

[轉貼]SQL Server 跨庫查詢

SQL Server跨庫查詢

方式一：

語句

SELECT * FROM 數據庫A.dbo.表A a, 數據庫B.dbo.表B b WHERE a.field=b.field

"DBO"可以省略如

SELECT * FROM 數據庫A..表A a, 數據庫B..表B b WHERE a.field=b.field

方式二（在一個數據庫掛上另一個數據庫的外鏈）：

SqlServer數據庫：

--這句是映射一個遠程數據庫
EXEC sp_addlinkedserver '遠程數據庫的IP或主機名',N'SQL Server'
--這句是登錄遠程數據庫
EXEC sp_addlinkedsrvlogin '遠程數據庫的IP或主機名', 'false', NULL, '登錄名', '密碼'

--登錄後，可以用以下格式操作遠程數據庫中的對象
select * from [遠程數據庫的IP或主機名].[數據庫名].[dbo].[表名]

insert into openrowset('sqloledb','192.168.0.100';'sa';'10060','select * from knss2009.dbo.yw_kck') select * from yw_kck

示例：

--創建鏈接服務器 
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '遠程服務器名或ip地址 ' 
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用戶名 ', '密碼 ' 

--查詢示例 
select * from ITSV.數據庫名.dbo.表名 

--導入示例 
select * into 表 from ITSV.數據庫名.dbo.表名 

--以後不再使用時刪除鏈接服務器 
exec sp_dropserver  'ITSV ', 'droplogins '

方式三：

--連接遠程/局域網數據(openrowset/openquery/opendatasource) 
--1、openrowset （比較推薦這種做法）

--查詢示例 
select * from openrowset( 'SQLOLEDB ', 'sql服務器名 '; '用戶名 '; '密碼 ',數據庫名.dbo.表名) 

--生成本地表 
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服務器名 '; '用戶名 '; '密碼 ',數據庫名.dbo.表名) 

--把本地表導入遠程表 
insert openrowset( 'SQLOLEDB ', 'sql服務器名 '; '用戶名 '; '密碼 ',數據庫名.dbo.表名) 
select *from 本地表 

--更新本地表 
update b 
set b.列A=a.列A 
from openrowset( 'SQLOLEDB ', 'sql服務器名 '; '用戶名 '; '密碼 ',數據庫名.dbo.表名)as a inner join 本地表 b 
on a.column1=b.column1

--openquery用法需要創建一個連接

--首先創建一個連接創建鏈接服務器 
exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '遠程服務器名或ip地址 ' 
--查詢 
select * 
FROM openquery(ITSV,  'SELECT *  FROM 數據庫.dbo.表名 ') 
--把本地表導入遠程表 
insert openquery(ITSV,  'SELECT *  FROM 數據庫.dbo.表名 ') 
select * from 本地表 
--更新本地表 
update b 
set b.列B=a.列B 
FROM openquery(ITSV,  'SELECT * FROM 數據庫.dbo.表名 ') as a  
inner join 本地表 b on a.列A=b.列A 

--3、opendatasource/openrowset 
SELECT   * 
FROM   opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陸名;Password=密碼 ').test.dbo.roy_ta 
--把本地表導入遠程表 
insert opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陸名;Password=密碼 ').數據庫.dbo.表名 
select * from

跨庫取數使用示例(注意做判斷)：

IF EXISTS(SELECT 1 FROM sys.synonyms WHERE name='SYN305_ys_CostAndCashSet')

DROP SYNONYM SYN305_ys_CostAndCashSet

GO

CREATE SYNONYM SYN305_ys_CostAndCashSet FOR [dotnet_erp305_hnjy].dbo.ys_CostAndCashSet

GO

不允許遠程訪問出現異常解決方案：

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

原文出處：
SQL Server跨库查询 - LeLe.xu - 博客园

回覆

冷日
(冷日) 發表時間：2022/7/19 3:44

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15766

[分享]冷日實做 MS-SQL Server 跨資料庫（服務器）Insert Into From Select

冷日實做範例：

USE [NowDatabase]
--創建服務器鏈接
EXEC sp_addlinkedserver  'other_srv_lnk','','SQLOLEDB','IP,Port'
EXEC sp_addlinkedsrvlogin 'other_srv_lnk','false',null,'Account','AccountPwd'
GO
--插入資料表使用查訊語句（從另一台服務器把資料塞過來本地資料表）
INSERT INTO [NowDatabase].dbo.[New_UpdateAML]
           ([PN]
           ,[VC]
           ,[uAML]
           ,[Comment]
           ,[plant]
           ,[mfr]
           ,[mfpn]
           ,[isPAS]
           ,[InsertUser]
           ,[InsertDateTime]
           ,[isEnable]
           ,[Remark])
SELECT	UpdateAML.PN , UpdateAML.VC , UpdateAML.UAML , UpdateAML.Comment , UpdateAML.Plant , UpdateAML.mfr , UpdateAML.mfrpn , 0 , 'UserName' , getDate() , 1 , 'Insert From Server1'
FROM	other_srv_lnk.[DatabaseName].[dbo].[UpdateAML]
WHERE 	UpdateAML.PN IS NOT NULL
AND 	UpdateAML.VC IS NOT NULL
AND 	UpdateAML.UAML IS NOT NULL
AND		UpdateAML.Plant IS NOT NULL
GO
--不再使用時刪除鏈接服務器
EXEC sp_dropserver 'other_srv_lnk','droplogins'
GO

回覆

冷日
(冷日) 發表時間：2022/10/25 4:14

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15766

Re: [分享]冷日實做 MS-SQL Server 跨資料庫（服務器）Insert Into From Select

08.使用 INSERT INTO … SELECT 輸入不重複資料

前言

這幾個月遇到一個需求：在某一個 Stored Procedure 需要把 Temp Table 資料寫入某一個資料表，但 ID重複的資料不寫入。寫入的語法為 INSERT SELECT，而重點在過濾資料的部分。一開始想到的作法是 NOT EXISTS 語法，但因為不理解，擔心有效能上的問題，於是在 Stack Overflow 上找到相關文章，發現其實有多種寫法，也有專家分析其優劣。

介紹

舉個簡單的例子，我們有資料表 A 與 B：
Table_A

Id	Name
1	AA
2	BB
3	CC

Table_B

Id	Name
1	PP

需求是將資料表A 的資料放入資料表B，但不放入 ID 重複的資料。如果不過濾資料直接放入，我們的語法大概會長這樣

INSERT INTO TABLE_B
  (Id, Name)
SELECT ta.Id,
       ta.Name
  FROM TABLE_A ta

而我們會有三種方法
NOT EXISTS

INSERT INTO TABLE_B (Id, name)
SELECT ta.Id,
       ta.Name
  FROM TABLE_A ta
 WHERE NOT EXISTS ( SELECT Id
                      FROM TABLE_B tb
                     WHERE tb.Id = t1.Id )

NOT IN

INSERT INTO TABLE_B (idIdName)
SELECT ta.Id,
       ta.Name
  FROM TABLE_A ta
 WHERE ta.Id NOT IN (SELECT Id
                      FROM TABLE_B)

LEFT JOIN/IS NULL

INSERT INTO TABLE_B  (Id, Name)
   SELECT ta.Id,
          ta.Name
     FROM TABLE_A ta
LEFT JOIN TABLE_B tb ON t2.id = t1.id
    WHERE t2.Id IS NULL

一看到 LEFT JOIN + IS NULL，就覺得這方法好像不錯，但其實專家們不推薦使用。若欄位皆為非NULL 的情況下 NOT EXISTS 與 NOT IN 執行效率接近，且比起 LEFT JOIN + IS NULL 快上3倍。
而如果有複合鍵 NOT EXISTS 會特別有效 (可以點選參考資料2，看一下詳細比較)。

看看 Stack Overflow 找到解決方法時，別忘了看一下專家解釋，如果直接複製貼上可能會出糗；如果有回答不錯的，別忘了幫忙投票一下 :D