Update ved Insert hvis eksistens er sand

btw. vi snakker om 100 tabeller hvor jeg er for doven til at finde unique_index på dem  - og derved lave mine updates selv.

Pointen er at kunne opdatere databasen når der kommer nye data, som måske findes i forvejen via deres index.

Det her hjælper dig ikke helt, men du kan måske arbejde lidt videre med ideen:

CREATE TRIGGER UpdateOnDuplicateInsert
ON dbo.DinTabel
INSTEAD OF INSERT
AS
BEGIN TRY
  INSERT INTO dbo.DinTabel
  SELECT * FROM inserted
END TRY
BEGIN CATCH
  UPDATE dbo.DinTAbel
  SET ...
END CATCH
END
GO

Problemet ved ovenstående er, at der godt kan være mere end én række i inserted tabellen, så måske bør man udenom hele try catch blokken loope alle rækker i inserted igennem via en cursor fx, og så for hver af dem forsøge inserten, og så håndtere hvis det går galt. Der mangler så den logik der skal ske i de tilfælde hvor insert'en fejler. Der har jeg ikke lige noget godt bud på hvordan den skal gribes an, da det må være noget med at slå op i nogle systemtabeller og se efter unique indexes, og hvilke kolonner de findes på.

Om man catcher fejl ved INSERT og laver UPDATE i trigger eller app gør nok ikke
den store forskel.

Jeg ville nok gøre det i app, fordi i app vil det være muligt at lave en
generel metode som forsøger INSERT og hvis den fejler så selv konstruerer
UPDATE statement.

Det må være vejen frem.

arne :

pointen var at jeg skriver min import således:

"insert into [table] values (a,b,c,d,e,f);"

uden at vide noget om indexes eller tupple-navne ... En SQL trigger kunne derefter i samme sekund der sker en fejl, finde ud af hvilke tuppler er index og lave min update med dem.

jeg kan selvfølgelig lave en SP som returnere Index tuppler, hvorefter jeg laver insert/update med dem...

sjang :

det er da en start :)

en anden grund til at jeg ikke laver update-if-exists i programmet, er fordi det er for langsomt !

dét jeg gør, er at compilere statements på 1000 linier af "Insert into [] values ();"

hvis de 1000 fejler, tager jeg dem 1 efter 1, og logger alle som fejler.

dét jeg så i stedet kunne gøre, er jo at update dem som fejler... men igen, så mangler jeg opslags kolonnerne på tabellerne.

Er det noget du skal gøre ofte? Skal det automatiseres eller er det bare en engangsfornøjelse?

det er en kørsel vi laver 4 gange om året, med cirka 100 tabeller og omkring 40+ mio records

jeg har ikke helt kunne finde ud af den smarteste og hurtigste måde at lave denne kørsel på, andet end at slette alle data hvorefter vi lægger de nye data ind.

dertil kommer så 3-4 påfyldninger som gerne skulle benytte sig af dette system hvor den opdatere automatisk (men også gerne for resten !)

Hvis det kun er 40-50M og 4 gange om året så er det jo ok at slette såfremt det ikke har indflydelse på foreign keys.

Nu ved jeg ikke hvilken SQLServer i bruger, men 2005 har jo automatisk partitionering ellers kan man lave en ganske fin og stabil SSIS til den slags ETL!
Engang sad vi selv med SQL2000 og her havde jeg oprettet tabeller med kvartalsnumre+år, og så brugt views til altid at pege på den nyeste tabel/ tabeller. Ikke verdens bedste løsning, men den fungerede til vores behov. Idag ville jeg helst undgå at lave sådan noget klamp, ja faktisk helt undgå at bruge SQL2000 hehe :=)

SSIS =?
ETL =?


god dammit...

hvorfor findes der ikke et script til at lave en Update_if_exists_else_insert funktion ????

det må da være muligt ??

http://en.wikipedia.org/wiki/SQL_Server_Integration_Services
http://en.wikipedia.org/wiki/Extract,_transform,_load

Jeg tror at det er ikke findes i T-SQL fordi det normalt laves i app.

Hmmm... så et andet spørgsmål...


er det muligt at hente de kolonnenavne som indgår i et unique index på en tabel ?

for derved at lave UPSERT SQL'en dynamisk...

Prøv:

SP_HELPINDEX 'tabelnavn'

Det var netop derfor jeg spurgte til hvilken SQL version du kører med, SQL 2008 har det som du søger.

Men ellers kan man som sagt komme rimeligt langt med SSIS og når det er så sjældent som du skriver, ja.... så skal man passe på ikke at skyde gråspurve med kanoner.

databasen er SQL2005

men jeg tror jeg har fundet løsningen (fra en bruger på MSDN)

DECLARE @schema_name sysname
      , @table_name  sysname
      , @object_id  int
      , @column_name sysname
      , @is_unique  bit



--Declarations for dynamic SQL
DECLARE @sql            varchar(max)
      , @columns        varchar(max)
      , @source_columns varchar(max)
      , @on_clause      varchar(max)
      , @where_clause  varchar(max)

      , @set_clause    varchar(max)



DECLARE table_cursor CURSOR FOR
  SELECT s.name As [schema_name]
      , t.name As [table_name]
      , object_id
  FROM  sys.tables t
  INNER
    JOIN sys.schemas s
      ON t.schema_id = s.schema_id



OPEN table_cursor



FETCH NEXT FROM table_cursor INTO @schema_name, @table_name, @object_id



WHILE @@Fetch_Status = 0
  BEGIN

    --Reset dynamic SQL variables

    SET @columns = ''
    SET @source_columns = ''
    SET @on_clause = ''
    SET @where_clause = ''

    SET @set_clause = ''
    SET @sql = '
      ALTER TRIGGER [@schema_name].[tr_ioi_@table_name]
        ON [@schema_name].[@table_name]
        INSTEAD OF INSERT
      AS
        BEGIN
          BEGIN TRANSACTION

            UPDATE [@schema_name].[@table_name]
            SET    @set_clause
            FROM  inserted As [source]
            INNER
              JOIN [@schema_name].[@table_name] As [destination]
                ON @on_clause


            INSERT INTO [@schema_name].[@table_name] (@columns)
            SELECT @source_columns
            FROM  inserted As [source]
            LEFT
              JOIN [@schema_name].[@table_name] As [destination]
                ON @on_clause
            WHERE  @where_clause
          COMMIT TRANSACTION
        END'



    DECLARE column_cursor CURSOR FOR
      SELECT QuoteName(c.name) As [column_name]
          , i.is_unique
      FROM  sys.columns c
      LEFT
        JOIN (
              SELECT i.object_id
                  , x.index_column_id
                  , i.is_unique
              FROM  sys.indexes i
              INNER
                JOIN sys.index_columns x
                  ON i.object_id = x.object_id
                AND i.index_id = x.index_id
              WHERE  i.is_unique = 1
            ) i
          ON c.object_id = i.object_id
        AND c.column_id = i.index_column_id
      WHERE  c.object_id = @object_id
      ORDER
          BY c.column_id ASC



    OPEN column_cursor



    FETCH NEXT FROM column_cursor INTO @column_name, @is_unique



    WHILE @@Fetch_Status = 0
      BEGIN
        IF @is_unique = 1
          BEGIN
            SET @on_clause = @on_clause + '[source].' + @column_name + ' = destination.' + @column_name + ' AND '
            SET @where_clause = @where_clause + '[destination].' + @column_name + ' IS NULL AND '
          END

        ELSE
          BEGIN
            SET @set_clause = @set_clause + ',' + @column_name + ' = [source].' + @column_name
          END



        SET @columns = @columns + ',' + @column_name
        SET @source_columns = @source_columns + ',[source].' + @column_name


        FETCH NEXT FROM column_cursor INTO @column_name, @is_unique
      END



    --If no on clause is set, there are no unique columns on this table
    IF @on_clause = ''
      BEGIN
        PRINT QuoteName(@schema_name) + '.' + QuoteName(@table_name) + ' does not appear to have unique columns for matching - no trigger created on this object'
      END

    ELSE IF @set_clause = ''
      BEGIN
        PRINT QuoteName(@schema_name) + '.' + QuoteName(@table_name) + ' does not appear to have and non-unique columns - no trigger created on this object'
      END
    ELSE
      BEGIN
   
        --Trim leading commas
        SET @columns = Right(@columns, Len(@columns) - 1)
        SET @source_columns = Right(@source_columns, Len(@source_columns) - 1)

        SET @set_clause = Right(@set_clause, Len(@set_clause) - 1)
      --Remove trailing ANDs
        SET @on_clause = Left(@on_clause, Len(@on_clause) - 4)
        SET @where_clause = Left(@where_clause, Len(@where_clause) - 4)
   
        SET @sql = Replace(@sql, '@schema_name', @schema_name)
        SET @sql = Replace(@sql, '@table_name', @table_name)
        SET @sql = Replace(@sql, '@columns', @columns)
        SET @sql = Replace(@sql, '@source_columns, @source_columns)
        SET @sql = Replace(@sql, '@on_clause', @on_clause)
        SET @sql = Replace(@sql, '@where_clause', @where_clause)

        --SET @sql = Replace(@sql, '@set_clause', @set_clause)
   
        PRINT @sql
        --EXEC @sql
      END



    CLOSE column_cursor
    DEALLOCATE column_cursor



    FETCH NEXT FROM table_cursor INTO @schema_name, @table_name, @object_id
  END



CLOSE table_cursor
DEALLOCATE table_cursor