我有這種方法可以在另一個(gè)數(shù)據(jù)庫(kù)的表中添加來(lái)自另一個(gè)數(shù)據(jù)庫(kù)的相同值：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 " );}問(wèn)題是當(dāng)我多次運(yùn)行它時(shí)，記錄被復(fù)制了。我能做些什么來(lái)阻止這種情況？我需要它，當(dāng)我第二次、第三次、X 次運(yùn)行它時(shí)，只添加不相同的內(nèi)容。
1 回答
婷婷同學(xué)_
TA貢獻(xiàn)1844條經(jīng)驗(yàn) 獲得超8個(gè)贊
使用not exists條件來(lái)測(cè)試重復(fù)項(xiàng)，如下所示。
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注意：在語(yǔ)句中添加 an 沒(méi)有任何好處insert，表本質(zhì)上是無(wú)序的，order by如果順序很重要，您必須在 select 上使用。
- 1 回答
- 0 關(guān)注
- 134 瀏覽
添加回答
舉報(bào)
0/150
提交
取消