Need help on SQL script

  • Hello, I am learning SQL and wondering if someone can help me with script below – not sure if I am posting it in correct session

    I have a table TBL1 with data like below

    Col1,Col2,Col3Col4,Col5

    12Val_A,Val_B,Val_C

    12Val_DVal_B,Val_E

    12…..

    22…

    22…

    Tons of data…

    Now I want to read through TBL1 and insert some rows where Col1 =1 with data below

    32Val_AVal_BVal_C

    32Val_DVal_BVal_C

    32….

    So basically, I want to duplicate the rows and just replace the first column with new value (replace 1 with 3)

    BTW, Col1, COl3, Col4, Col5 are the keys od the table

    Thanks in advance

    James

  • Welcome to the forum and congrats on trying to improve you T-SQL coding ability. Please read the article in my signature and repost you question with the create table statements and sample data inserts along with the desired output. This will help you get a more precise answer.

    With that being said you could do the following:

    insert TBL1

    select 3, Col2, Col3, Col4, Col5

    from TBL1

    where Col1 = 1



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Are you sure that rows

    Col1,Col2,Col3Col4,Col5

    32Val_AVal_BVal_C

    32Val_DVal_BVal_C

    are not already existing rows?

    Igor Micev,My blog: www.igormicev.com

  • Keith Tate (2/3/2014)


    Welcome to the forum and congrats on trying to improve you T-SQL coding ability. Please read the article in my signature and repost you question with the create table statements and sample data inserts along with the desired output. This will help you get a more precise answer.

    With that being said you could do the following:

    insert TBL1

    select 3, Col2, Col3, Col4, Col5

    from TBL1

    where Col1 = 1

    That is basically what I would do as well.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thaks all for the help.

    It's works great! Now can I ask more?

    Sorry for not adding them all in once - just try to make it simple for myself 🙂

    Col1, Col2, Col3 Col4, Col5

    1 2 Val_A, Val_B, Val_C

    1 2 Val_D Val_B, Val_E

    1 2 …..

    2 2 …

    2 2 …

    Now I want to modify the Col2 base on the Col4’s value(Bal_B)

    So it would be like this:

    1 2 Val_A, Val_B, Val_C

    1 2 Val_D Val_B, Val_E

    1 2 …..

    2 2 …

    2 2 …

    3,Val_B,Val_A,Val_B,Val_C

    3,Val_B,Val_D Val_B,Val_E

  • You get one freebie without create table statements and sample data :-D. Please read the article that I mentioned and add the necessary information to the post and someone will be able to answer your question.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • jds-685721 (2/3/2014)


    Thaks all for the help.

    It's works great! Now can I ask more?

    Sorry for not adding them all in once - just try to make it simple for myself 🙂

    Col1, Col2, Col3 Col4, Col5

    1 2 Val_A, Val_B, Val_C

    1 2 Val_D Val_B, Val_E

    1 2 …..

    2 2 …

    2 2 …

    Now I want to modify the Col2 base on the Col4’s value(Bal_B)

    So it would be like this:

    1 2 Val_A, Val_B, Val_C

    1 2 Val_D Val_B, Val_E

    1 2 …..

    2 2 …

    2 2 …

    3,Val_B,Val_A,Val_B,Val_C

    3,Val_B,Val_D Val_B,Val_E

    Think about the question you are asking and the general tsql constructs. Try to write that out in as close to valid tsql as you can get and post it if it isn't working.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Here is my revised request again. Please help

    CREATE TABLE [dbo].[MYTABLE]

    (

    [TND_CD] [smallint] NOT NULL,

    [ACCPT_FOR_SLS_FG]

    [STR_ID] [int] NOT NULL,

    [HIER_ID] [int] NOT NULL,

    [LOC_ID] [int]NOT NULL

    )

    CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED

    (

    [TND_CD] ASC,

    [STR_ID] ASC,

    [HIER_ID] ASC,

    [LOC_ID] ASC

    )

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT #MYTABLE ON

    INSERT INTO [MYTABLE] ([TND_CD], [ACPT_PYMT_FLG], [STR_ID], [HIER_ID], [LOC_ID])

    SELECT '1', '10','0','0','1' UNION ALL

    SELECT '1', '10','0','0','2' UNION ALL

    SELECT '1', '10','0','0','3' UNION ALL

    SELECT '1', '10','0','0','4' UNION ALL

    SELECT '1', '10','0','0','5' UNION ALL

    SELECT '2', '11','0','0','1' UNION ALL

    SELECT '2', '11','0','0','2' UNION ALL

    SELECT '2', '11','0','0','3' UNION ALL

    SELECT '2', '11','0','0','4' UNION ALL

    SELECT '2', '11','0','0','5'

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT #MYTABLE OFF

    -- Get the number of rows in the looping table

    DECLARE @RowCount int

    SET @RowCount = (Select count(*) FROM [MYTABLE] WHERE TND_CD =1 )

    -- Declare an iterator

    DECLARE @I int

    -- Initialize the iterator

    SET @I = 1

    Declare @STR_ID int

    Declare @HIER_ID int

    Declare @LOC_ID int

    -- Loop through the rows of a table @MYTABLE

    WHILE (@I <= @RowCount)

    BEGIN

    Select @STR_ID=LOC_ID, @HIER_ID=HIER_ID, @LOC_ID=LOC_ID FROM [MYTABLE] WHERE TND_CD=1

    print TND_CD

    Print @STR_ID

    print @HIER_ID

    print @LOC_ID

    INSERT INTO [MYTABLER] VALUES(8 ,10,@STR_ID ,@HIER_ID,@LOC_ID)

    PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I)

    SET @I = @I + 1

    END

    Here is what I am expecting to get:

    [TND_CD][ACCPT_FOR_SLS_FG][STR_ID][HIER_ID][LOC_ID]

    110001

    110002

    110003

    110004

    110005

    211001

    211002

    211003

    211004

    211005

    810101

    810202

    810303

    810404

    810505

    and this is what error I got:

    1

    5

    10

    0

    5

    Msg 2627, Level 14, State 1, Line 25

    Violation of PRIMARY KEY constraint 'PK_TENDER'. Cannot insert duplicate key in object 'dbo.TENDER'.

    The statement has been terminated.

    Row No = 1

    1

    5

    10

    0

    5

    Msg 2627, Level 14, State 1, Line 25

    Violation of PRIMARY KEY constraint 'PK_TENDER'. Cannot insert duplicate key in object 'dbo.TENDER'.

    The statement has been terminated.

    Row No = 2

    1

    5

    10

    0

    5

    Looks like my cusor is always pointing to the last record of my selection

    Please help and Thanks in advance

    James

  • I think your approach is not good. Even if you manage to resolve it now, you'll have issues later if going this way.

    My suggestion is to re-create your table a bit differently:

    CREATE TABLE [dbo].[MYTABLE]

    (

    [ID] int identity(1,1) primary key,

    [TND_CD] [smallint] NOT NULL,

    [ACCPT_FOR_SLS_FG] /*???*/

    [STR_ID] [int] NOT NULL,

    [HIER_ID] [int] NOT NULL,

    [LOC_ID] [int] NOT NULL

    )

    Do not create CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED

    you already have primary clustered key (surrogate). You can create non-clustered key instead of the old clustered key.

    Then keep on with your exhibitions.

    Regards,

    Igor

    Igor Micev,My blog: www.igormicev.com

  • Thanks for reply

    Unfortunately this is our existing table structure – I can’t change it.

  • You do not need to use a loop for this. Just modify the original suggestion so that you only return rows that don't already exist.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks Sean, It worked!.

    If you know how to do it then it becomes very simple 🙂

    Thanks all for the helps

  • You are quite welcome. Glad that worked for you.

    If possible, can you post what worked you so that others who may stumble across this thread can see a working solution?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Oh it’s just for me as a learner 😉

    What I did is follow Keith’s instruction like below:

    insert TBL1

    select 3, Col2, Col6, Col4, Col5, Col6 -- replace Col3 with Col6

    from TBL1

    where Col1 = 1

Viewing 14 posts - 1 through 13 (of 13 total)

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