避免添加重复值

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_idfrom (&nbsp; &nbsp; select&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; schema_name(t.[schema_id]) + '.' + t.[name] as table_view&nbsp; &nbsp; &nbsp; &nbsp; , substring(column_names, 1, len(column_names)-1) as [columns]&nbsp; &nbsp; &nbsp; &nbsp; , case when i.is_primary_key = 1 then 'Primary_key'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; when i.is_unique = 1 then 'Unique'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else 'Not_unique' end as [type]&nbsp; &nbsp; &nbsp; &nbsp; , i.[name] as index_name&nbsp; &nbsp; &nbsp; &nbsp; , i.index_id&nbsp; &nbsp; from sys.objects t&nbsp; &nbsp; inner join sys.indexes i&nbsp; &nbsp; on t.[object_id] = i.[object_id]&nbsp; &nbsp; cross apply (&nbsp; &nbsp; &nbsp; &nbsp; select col.[name] + ', '&nbsp; &nbsp; &nbsp; &nbsp; from sys.index_columns ic&nbsp; &nbsp; &nbsp; &nbsp; inner join sys.columns col on ic.[object_id] = col.[object_id] and ic.column_id = col.column_id&nbsp; &nbsp; &nbsp; &nbsp; where ic.[object_id] = t.[object_id]&nbsp; &nbsp; &nbsp; &nbsp; and ic.index_id = i.index_id&nbsp; &nbsp; &nbsp; &nbsp; order by col.column_id&nbsp; &nbsp; &nbsp; &nbsp; for xml path ('')&nbsp; &nbsp; ) D (column_names)&nbsp; &nbsp; where t.is_ms_shipped <> 1&nbsp; &nbsp; and index_id > 0) X-- The following where clause prevents the insertion of duplicateswhere not exists (&nbsp; &nbsp; select 1&nbsp; &nbsp; from test.dbo.indexes I&nbsp; &nbsp; 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 上使用。