我有這種方法可以在另一個數據庫的表中添加來自另一個數據庫的相同值：public function insert(){ $indexes= DB::connection('sqlsrv') ->select ( " IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[dbo].[indexes]') AND type in (N'U')) CREATE TABLE test.dbo.indexes ( table_view nvarchar(500) not null, [columns] nvarchar(500) not null, [type] nvarchar(50) not null, index_name nvarchar(500) not null, index_id int not null ) insert into test.dbo.indexes (table_view, [columns], [type], index_name, index_id) select schema_name(t.schema_id) + '.' + t.[name] as table_view, substring(column_names, 1, len(column_names)-1) as [columns], case when i.is_primary_key = 1 then 'Primary_key' when i.is_unique = 1 then 'Unique' else 'Not_unique' end as [type], i.[name] as index_mane, i.index_id from sys.objects t inner join sys.indexes i on t.object_id = i.object_id cross apply (select col.[name] + ', ' from sys.index_columns ic inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id where ic.object_id = t.object_id and ic.index_id = i.index_id order by col.column_id for xml path ('') ) D (column_names) where t.is_ms_shipped <> 1 and index_id > 0 order by schema_name(t.schema_id) + '.' + t.[name], i.index_id " );}問題是當我多次運行它時，記錄被復制了。我能做些什么來阻止這種情況？我需要它，當我第二次、第三次、X 次運行它時，只添加不相同的內容。
1 回答
婷婷同學_
TA貢獻1844條經驗 獲得超8個贊
使用not exists條件來測試重復項，如下所示。
insert into test.dbo.indexes (table_view, [columns], [type], index_name, index_id)
select table_view, [columns], [type], index_name, index_id
from (
select
schema_name(t.[schema_id]) + '.' + t.[name] as table_view
, substring(column_names, 1, len(column_names)-1) as [columns]
, case when i.is_primary_key = 1 then 'Primary_key'
when i.is_unique = 1 then 'Unique'
else 'Not_unique' end as [type]
, i.[name] as index_name
, i.index_id
from sys.objects t
inner join sys.indexes i
on t.[object_id] = i.[object_id]
cross apply (
select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col on ic.[object_id] = col.[object_id] and ic.column_id = col.column_id
where ic.[object_id] = t.[object_id]
and ic.index_id = i.index_id
order by col.column_id
for xml path ('')
) D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0
) X
-- The following where clause prevents the insertion of duplicates
where not exists (
select 1
from test.dbo.indexes I
where I.table_view = X.table_view and I.[columns] = X.[columns] and I.[type] = X.[type] and I.index_name = X.index_name and I.index_id = X.index_id
);
order by注意：在語句中添加 an 沒有任何好處insert，表本質上是無序的，order by如果順序很重要，您必須在 select 上使用。
- 1 回答
- 0 關注
- 128 瀏覽
添加回答
舉報
0/150
提交
取消