Table options for combining stored procedures and auto-increment identifier for column name

Question

Table options for combining stored procedures and auto-increment identifier for column name

CREATE TABLE dbo.TestTableCamera
(
    Id                int,
    AliasTitle        nvarchar(500),
    Make              nvarchar(500),
    Model             nvarchar(MAX) 
)

I have a type that is passed as data stored in a stored procedure. I would like to first insert some columns into one table if they do not exist and add the primary key identifier to the alias header. After that, I would like to insert into another table, that is, the image table into which the cameras are inserted, into the camera table. I need to maintain referential integrity, but I'm not sure how to determine which identifier for the camera belongs to which image. I'm also not sure how to add the primary key identifier to the alias headers.

    CREATE TYPE [dbo].[MediaGalleryFlickrImportParameters] AS TABLE
    (
        Title             nvarchar(500),
        Description       nvarchar(MAX),
        Hits              bigint,
        CameraAliasTitle  nvarchar(255),
        CameraMake        nvarchar(55),
        CameraModel       nvarchar(100)
    )

I have two tables

CREATE TABLE dbo.TestTableImage
(
    Id                int,
    AliasTitle        nvarchar(500),
    Title             nvarchar(500),
    Description       nvarchar(MAX),
    Hits              bigint,
    CameraId          int
)

  CREATE TABLE dbo.TestTableCamera
(
    Id                int,
    AliasTitle        nvarchar(500),
    Make              nvarchar(500),
    Model             nvarchar(MAX) 
)

My procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.media_gallery_flickr_import
(   
   @dataImported [dbo].[MediaGalleryFlickrImportParameters] READONLY
)
AS
BEGIN
 DECLARE @InsertedCameraId bigint;

    SET NOCOUNT ON;


/*Camera Details*/   
--IF (NOT NULLIF(@PreviousStartDate, '') IS NULL AND NOT NULLIF(@PreviousStartDate, '') IS NULL)
--BEGIN

        INSERT INTO TestTableCamera (AliasTitle, Make, Model)
SELECT DISTINCT CONCAT (CameraAliasTitle,'-',(SELECT ISNULL (MAX(Id)+1,0) FROM TestTableCamera)), CameraMake, CameraModel
FROM @dataImported di
WHERE
NOT EXISTS (SELECT * FROM TestTableCamera c
            WHERE               
            di.CameraAliasTitle = c.AliasTitle
            AND di.CameraMake = c.Make
            AND di.CameraModel = c.Model)
AND         NOT NULLIF(di.CameraAliasTitle, '') IS NULL
            AND NOT NULLIF(di.CameraMake, '') IS NULL
            AND NOT NULLIF(di.CameraModel, '') IS NULL 

   -- SET @InsertedCameraId = (SELECT Id FROM camera WHERE make = 'tes');  
--END


INSERT INTO dbo.TestTableImage
    (Title, Description, Hits)
SELECT 
    Title, Description, Hits
FROM 
    @dataImported



END
GO

I thought I could do something in this direction with auto increment coloumn

INSERT INTO TestTableCamera (AliasTitle, Make, Model)
SELECT DISTINCT CONCAT (CameraAliasTitle,'-',(SELECT ISNULL(MAX(id)+1,0) FROM TestTableCamera)), CameraMake, CameraModel
FROM @dataImported di
WHERE
NOT EXISTS (SELECT * FROM camera c
            WHERE 
            NOT NULLIF(di.CameraAliasTitle, '') IS NULL
            AND NULLIF(di.CameraMake, '') IS NULL
            AND NULLIF(di.CameraModel, '') IS NULL
            AND di.CameraAliasTitle = c.AliasTitle
            AND di.CameraMake = c.Make
            AND di.CameraModel = c.Model)

, , o

ALTER TRIGGER InsertedCameraAlias ON TestTableCamera
AFTER INSERT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
IF UPDATE(Id)
          BEGIN

           UPDATE  dbo.TestTableCamera
                SET AliasTitle = Concat(a.AliasTitle,'-', a.Id)
                FROM dbo.TestTableCamera a
                JOIN inserted i ON a.ID = i.ID
                --WHERE id EXISTS IN(SELECT id from inserted)               
          END
END
GO

+4

sql-server sql-server-2008

Matthew Chambers 12 . '14 14:41

1

Philip Kelley · Accepted Answer · 2014-01-15T00:30:18+0000

Tricky. ...

:

( , ).
TestTableCamera, .

TestTableCamera, :

CREATE TABLE dbo.TestTableCamera
(
    Id                int  identity(1,1) primary key,  --  Added identity property
    AliasTitle        nvarchar(500),
    Make              nvarchar(500),
    Model             nvarchar(MAX) 
)

. , :

IF objectproperty(object_id('dbo.media_gallery_flickr_import'), 'isProcedure') = 1
    DROP PROCEDURE dbo.media_gallery_flickr_import

GO
CREATE PROCEDURE dbo.media_gallery_flickr_import

    @dataImported as dbo.MediaGalleryFlickrImportParameters READONLY

AS

    SET NOCOUNT on

    DECLARE @NewIds table (Id int not null)


    --  Extract list of cameras from the passed-in data
    --  Use the EXCEPT clause to filter out any items already in the target table
    --  Bonus: using EXCEPT removed dupicates
    --  Any rows added are caught by the output clause (we only need Id)
    INSERT TestTableCamera (AliasTitle, Make, Model)
     output inserted.Id
      into @NewIds
     select CameraAliasTitle, CameraMake, CameraModel
      from @dataImported
     except select AliasTitle, Make, Model
      from TestTableCamera


    --  For all rows inserted (and caught by the output clause),
    --  update their AliasTitle with the newly-generated Id.
    --  The primary key ensures that this will perform optimally
    UPDATE TestTableCamera
     set AliasTitle = AliasTitle + ' - ' + cast(ni.Id as varchar(10))
     from TestTableCamera ttc
      inner join @NewIds ni
       on ni.Id = ttc.Id

RETURN 0
GO

.

SET NOCOUNT on
TRUNCATE TABLE TestTableCamera

DECLARE
  @TestSet_1 dbo.MediaGalleryFlickrImportParameters


--  Four new cameras
INSERT @TestSet_1 values
  ('Title 1', 'This is the first item',  100, 'Camera Alias A', 'Make AA', 'Model 1')
 ,('Title 2', 'This is the second item', 200, 'Camera Alias A', 'Make AA', 'Model 1')
 ,('Title 3', 'This is the third item',  300, 'Camera Alias B', 'Make BB', 'Model 2')
 ,('Title 4', 'This is the fourth item', 400, 'Camera Alias C', 'Make BB', 'Model 3')
 ,('Title 5', 'This is the fifth item',  500, 'Camera Alias D', 'Make CC', 'Model 1')

EXECUTE media_gallery_flickr_import @TestSet_1

SELECT * from TestTableCamera order by Id

, ... . !

:

SET NOCOUNT on
TRUNCATE TABLE TestTableCamera

DECLARE
  @TestSet_1 dbo.MediaGalleryFlickrImportParameters
 ,@TestSet_2 dbo.MediaGalleryFlickrImportParameters


--  Four new cameras
INSERT @TestSet_1 values
  ('Title 1', 'This is the first item',  100, 'Camera Alias A', 'Make AA', 'Model 1')
 ,('Title 2', 'This is the second item', 200, 'Camera Alias A', 'Make AA', 'Model 1')
 ,('Title 3', 'This is the third item',  300, 'Camera Alias B', 'Make BB', 'Model 2')
 ,('Title 4', 'This is the fourth item', 400, 'Camera Alias C', 'Make BB', 'Model 3')
 ,('Title 5', 'This is the fifth item',  500, 'Camera Alias D', 'Make CC', 'Model 1')

EXECUTE media_gallery_flickr_import @TestSet_1

SELECT 'First pass' [ ], * from TestTableCamera order by Id

--  Do it again, show that nothing new comes in
EXECUTE media_gallery_flickr_import @TestSet_1

SELECT 'Second pass' [ ], * from TestTableCamera order by Id


--  One new camera, one repeat
INSERT @TestSet_2 values
  ('Title 1', 'This is a duplicate of the first item',  100, 'Camera Alias A', 'Make AA', 'Model 1')
 ,('Title 6', 'This is the sixth item',  600, 'Camera Alias E', 'Make DD', 'Model 7')

EXECUTE media_gallery_flickr_import @TestSet_2

SELECT 'Third pass' [ ], * from TestTableCamera order by Id

... ! Weve "AliastTitle" , , . .

. , EXCEPT, "". , .

, :

DROP TABLE TestTableCamera
CREATE TABLE dbo.TestTableCamera
(
    Id                int  identity(1,1) primary key,  --  Added identity property
    AliasTitle        nvarchar(500),
    AdjustedAlias as AliasTitle + ' - ' + cast(Id as varchar(10)),
    Make              nvarchar(500),
    Model             nvarchar(MAX) 
)

, output:

IF objectproperty(object_id('dbo.media_gallery_flickr_import'), 'isProcedure') = 1
    DROP PROCEDURE dbo.media_gallery_flickr_import

GO
CREATE PROCEDURE dbo.media_gallery_flickr_import

    @dataImported as dbo.MediaGalleryFlickrImportParameters READONLY

AS

    SET NOCOUNT on

    --  Extract list of cameras from the passed-in data
    --  Use the EXCEPT clause to filter out any items already in the target table
    --  Bonus: using EXCEPT removed dupicates
    INSERT TestTableCamera (AliasTitle, Make, Model)
     select CameraAliasTitle, CameraMake, CameraModel
      from @dataImported
     except select AliasTitle, Make, Model
      from TestTableCamera

RETURN 0
GO

, . ? , , , . kludgy, AliasTitle. , .

, , !

ADDENDA, :

TestTableImage , , , CameraId :

CREATE TABLE dbo.TestTableImage
(
    Id                int  identity(1,1) primary key,
    AliasTitle        nvarchar(500),
    Title             nvarchar(500),
    Description       nvarchar(MAX),
    Hits              bigint,
    CameraId          int
     constraint FK_TestTableImage__TestTableCamera
      foreign key references TestTableCamera (Id)
)

TestTableCamera ( - INSERT):

IF objectproperty(object_id('dbo.media_gallery_flickr_import'), 'isProcedure') = 1
    DROP PROCEDURE dbo.media_gallery_flickr_import

GO
CREATE PROCEDURE dbo.media_gallery_flickr_import

    @dataImported as dbo.MediaGalleryFlickrImportParameters READONLY

AS

    SET NOCOUNT on

    --  Extract list of cameras from the passed-in data
    --  Use the EXCEPT clause to filter out any items already in the target table
    --  Bonus: using EXCEPT removed dupicates
    INSERT TestTableCamera (AliasTitle, Make, Model)
     select CameraAliasTitle, CameraMake, CameraModel
      from @dataImported
     except select AliasTitle, Make, Model
      from TestTableCamera

    --  With camera now defined, join in on it by the "natural key"
    --  (AliastTitle, Make, Model uniquely identify a camera, it was
    --  either already in the table or we just added it, so we can
    --  just join on it)
    INSERT TestTableImage (AliasTitle, Title, Description, Hits, CameraId)
     select ttc.AdjustedAlias, di.Title, di.Description, di.Hits, ttc.Id
      from @dataImported di
       inner join TestTableCamera ttc
        on ttc.AliasTitle = di.CameraAliasTitle
         and ttc.Make = di.CameraMake
         and ttc.Model = di.CameraModel

RETURN 0
GO

, , ( - < TRUNCATE TABLE

DELETE TestTableImage
DELETE TestTableCamera

AliasTitle; , . (, ID ?)

, , , . , , output, temp .

Table options for combining stored procedures and auto-increment identifier for column name

More articles: