locked
HOW do I insert & update data into mapping table RRS feed

  • Question

  • Hi Folks !

    Any one there can help me ?

    I have two tables as NEWS table  & WEBSITE table

    Third table is mapping table named WEBSITE_NEWS_MAP

     

    News Table - news_id int not null(PK), news_description varchar(200), news_date datetime.

    Website Table :- Website_id int not null (PK), Website_Name varchar(200), Website_Description varchar(1000)

    Website_News_Map Table:- Website_News_Map_id int not null (PK), news_id int (FK), Website_id int (FK)

    Now, I have an ADMIN PANEL in my website , where I use to add same news for multiple website.

    I have created an stored procedure that successfully add the news into the NEWS table , but not into the Website_News_map table.

    Below is the stored procedure , Iam using :-

    ALTER

     

    PROCEDURE [dbo].[test]

    @news_heading

    as text,

    @news_item

    as text,

    @news_text

    as text,

    @news_date

    as datetime,

    @news_published_date

    as datetime,

    @news_create_date

    as datetime,

    @news_status

    as tinyint,

    @news_sub_text

    as text,

    @WebSite_ID

    varchar(200)

    AS

    Declare

     

    @news_id int

    BEGIN

     

    insert into news

     

     

    (news_heading,

    news_item

    ,

    news_text

    ,

    news_date

    ,

    news_published_date

    ,

    news_create_date

    ,

    news_status

    ,

    news_sub_text

    )

     

    values

     

     

    (@news_heading,

    @news_item

    ,

    @news_text

    ,

    @news_date

    ,

    @news_published_date

    ,

    @news_create_date

    ,

    @news_status

    ,

    @news_sub_text

    )

     

    set

     

    @news_id = @@identity

     

    Update

     

    news

    set

     

    news_createdby = @news_id

    where

     

    news_id = @news_id

    INSERT

     

    INTO

    WebSiteNews_Map

    (

    news_id

    ,

    WebSite_ID

    )

    SELECT

    news_id

    ,

    WebSite_ID

    FROM

    WebSiteNews_Map

    INNER

     

    JOIN

    dbo

    .Split(@WebSite_ID,',') AS A ON WebSiteNews_Map.WebSite_ID = A.Element

     

    In the above code , the Split function is being used .(I have no idea how it is being used as I havent used FUNCTIONS in SQL SERVER. I have just copy/pasted it from INTERNET).

    Below is the function :-

    ALTER

     

    FUNCTION [dbo].[Split] ( @vcDelimitedString nVarChar(4000),

    @vcDelimiter

    nVarChar(100) )

    /**************************************************************************

    DESCRIPTION: Accepts a delimited string and splits it at the specified

    delimiter points. Returns the individual items as a table data

    type with the ElementID field as the array index and the Element

    field as the data

    PARAMETERS:

    @vcDelimitedString - The string to be split

    @vcDelimiter - String containing the delimiter where

    delimited string should be split

    RETURNS:

    Table data type containing array of strings that were split with

    the delimiters removed from the source string

    USAGE:

    SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID

    AUTHOR: Karen Gayda

    DATE: 05/31/2001

    MODIFICATION HISTORY:

    WHO DATE DESCRIPTION

    --- ---------- ---------------------------------------------------

    ***************************************************************************/

    RETURNS

     

    @tblArray TABLE

    (

    ElementID

    smallint IDENTITY(1,1) not null primary key, --Array index

    Element

    nVarChar(1200) null --Array element contents

    )

    AS

    BEGIN

    DECLARE

     

    @siIndex

    smallint,

    @siStart

    smallint,

    @siDelSize

    smallint

    SET

     

    @siDelSize = LEN(@vcDelimiter)

    --loop through source string and add elements to destination table array

    WHILE

     

    LEN(@vcDelimitedString) > 0

    BEGIN

    SET

     

    @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)

    IF

     

    @siIndex = 0

    BEGIN

    INSERT

     

    INTO @tblArray (Element) VALUES(@vcDelimitedString)

    BREAK

    END

    ELSE

    BEGIN

    INSERT

     

    INTO @tblArray (Element) VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))

    SET

     

    @siStart = @siIndex + @siDelSize

    SET

     

    @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)

    END

    END

     

     

    RETURN

    END

    GO

     

    If possible any one can help me ?

     

     

     

    Monday, July 12, 2010 7:03 AM

Answers