避免添加重复值

Question

我有这种方法可以在另一个数据库的表中添加来自另一个数据库的相同值：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 次运行它时，只添加不相同的内容。

Answer 1

使用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 上使用。