Loading intermediate table

  • Hi,

    I have these tables and data:

    CREATE TABLE [Table1](
      [PartnerSiteID] [int] IDENTITY(1,1) NOT NULL PK,
      [PartnerID] [int] NOT NULL,
      [Code] [varchar](50) NOT NULL,
      [Name] [varchar](150) NULL
    )

    CREATE TABLE [TableCategory](
      [TableCategoryID] [int] IDENTITY(1,1) NOT NULL PK,
      [CategoryName][varchar](50) NOT NULL
    )

    CREATE TABLE [TableCategoryLink](
      TableCategoryLinkID [int] IDENTITY(1,1) NOT NULL,
      [TableCategoryID] [int] NOT NULL,
      [PartnerSiteID] [int] NOT NULL,
    PRIMARY KEY CLUSTERED
    (
      TableCategoryLinkID ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]

    ALTER TABLE TableCategoryLink WITH CHECK ADD CONSTRAINT [FK_CategoryID] FOREIGN KEY(TableCategoryID)
    REFERENCES [TableCategory] (TableCategoryID)
    GO
    ALTER TABLE TableCategoryLink WITH CHECK ADD CONSTRAINT [FK_PartnerSiteID] FOREIGN KEY([PartnerSiteID])
    REFERENCES [Table1] ([PartnerSiteID])
    GO

    INSERT [Table1] ([PartnerSiteID], [PartnerID], [Code], [Name]) VALUES (1, 1, N'0001', N'Name1')
    GO
    INSERT [Table1] ([PartnerSiteID], [PartnerID], [Code], [Name]) VALUES (2, 1, N'0002', N'Name2')
    GO
    INSERT [Table1] ([PartnerSiteID], [PartnerID], [Code], [Name]) VALUES (3, 1, N'0003', N'Name3')
    GO
    INSERT [Table1] ([PartnerSiteID], [PartnerID], [Code], [Name]) VALUES (4, 1, N'0004', N'Name4')
    GO

    INSERT TableCategory (TableCategoryID, [CategoryName]) VALUES (2, N'Archery')
    GO
    INSERT TableCategory (TableCategoryID, [CategoryName]) VALUES (3, N'Boxing')
    GO
    INSERT TableCategory (TableCategoryID, [CategoryName]) VALUES (4, N'Cycling')
    GO

    I want to write a procedure to load TableCategoryLink table, which is intermediate table, I want to make a call once and load bulk records. please help.

  • What is the source of data for this table?  Look at INSERT BULK

  • hoseam - Friday, March 23, 2018 6:10 AM

    Hi,

    I have these tables and data:

    CREATE TABLE [Table1](
      [PartnerSiteID] [int] IDENTITY(1,1) NOT NULL PK,
      [PartnerID] [int] NOT NULL,
      [Code] [varchar](50) NOT NULL,
      [Name] [varchar](150) NULL
    )

    CREATE TABLE [TableCategory](
      [TableCategoryID] [int] IDENTITY(1,1) NOT NULL PK,
      [CategoryName][varchar](50) NOT NULL
    )

    CREATE TABLE [TableCategoryLink](
      TableCategoryLinkID [int] IDENTITY(1,1) NOT NULL,
      [TableCategoryID] [int] NOT NULL,
      [PartnerSiteID] [int] NOT NULL,
    PRIMARY KEY CLUSTERED
    (
      TableCategoryLinkID ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]

    ALTER TABLE TableCategoryLink WITH CHECK ADD CONSTRAINT [FK_CategoryID] FOREIGN KEY(TableCategoryID)
    REFERENCES [TableCategory] (TableCategoryID)
    GO
    ALTER TABLE TableCategoryLink WITH CHECK ADD CONSTRAINT [FK_PartnerSiteID] FOREIGN KEY([PartnerSiteID])
    REFERENCES [Table1] ([PartnerSiteID])
    GO

    INSERT [Table1] ([PartnerSiteID], [PartnerID], [Code], [Name]) VALUES (1, 1, N'0001', N'Name1')
    GO
    INSERT [Table1] ([PartnerSiteID], [PartnerID], [Code], [Name]) VALUES (2, 1, N'0002', N'Name2')
    GO
    INSERT [Table1] ([PartnerSiteID], [PartnerID], [Code], [Name]) VALUES (3, 1, N'0003', N'Name3')
    GO
    INSERT [Table1] ([PartnerSiteID], [PartnerID], [Code], [Name]) VALUES (4, 1, N'0004', N'Name4')
    GO

    INSERT TableCategory (TableCategoryID, [CategoryName]) VALUES (2, N'Archery')
    GO
    INSERT TableCategory (TableCategoryID, [CategoryName]) VALUES (3, N'Boxing')
    GO
    INSERT TableCategory (TableCategoryID, [CategoryName]) VALUES (4, N'Cycling')
    GO

    I want to write a procedure to load TableCategoryLink table, which is intermediate table, I want to make a call once and load bulk records. please help.

    Creating a link table from existing data in two other tables that have nothing in common is not possible by code unless you want a Cartesian Product or random associations.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply