Loading intermediate table

• hoseam

  SSCertifiable

  Points: 5533

  More actions

  March 23, 2018 at 6:10 am

  #344116

  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.

• Bert-701015

  SSCommitted

  Points: 1615

  More actions

  March 23, 2018 at 7:07 am

  #1984401

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

• Jeff Moden

  SSC Guru

  Points: 1004280

  More actions

  March 24, 2018 at 5:07 pm

  #1984493

  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)