Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    1

    Question Unanswered: Urgent!!! read the table content

    i have a requirement..
    1.table1 contains multiple coluns and consists many rows of data
    2.we need to insert into table2 ( few columns data from table1)

    lets say. table1 contains 1000 rows of data..
    while looping throw table1 and inserts into table2.. if any error encounters... we need to skip that record and go ahead with other rows of data...

    error prone record from table1 need to logged into some other table

  2. #2
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    you can use this statement also

    Insert into Table2(c1,c2,c3,c4)
    select c1,c2,c5,c6 from Table1
    where c1='' (as your requirement its just an example)


    both tables total number of columns should be equal but columns can be different

    Syed Jahanzaib Bin Hassan
    MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

    My Blog
    A|U|R|E|U|S – S|A|L|A|H

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SJahanzaib, unfortunately you overlooked an important part of the problem

    an INSERT/SELECT statement will stop as soon as an error is encountered

    he clearly stated "if any error encounters... we need to skip that record and go ahead with other rows of data..."
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    Here is need of

    DECLARE @c1 int
    DECLARE @c2 varchar(300)
    DECLARE @id int

    DECLARE @INScount int
    DECLARE @insert int

    SELECT @INScount = count(*) from TABTEST1
    set @insert =0

    while @insert != @INSCount
    begin

    Select top 1 @id=id,@c2=col1111 from Tabtest1

    Begin Transaction T1
    insert into Tabtest2(col1111) values(@c2)


    if @@ERROR = 0
    Begin
    commit transaction t1
    print 'commit'
    delete from table1 where id = @id
    End
    else
    Begin
    rollback transaction T1
    print 'rollback'
    End
    set @insert = @insert + 1
    End

    that is a sample code

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Learned something new

    SJahanzaib, thank you for showing me something in your code that I had never seen before, that will help me out immeasurably.

    Select top 1 @id=id,@c2=col1111 from Tabtest1

    This statement demonstrates an assignment to variables from within a SELECT statement. I had never seen that before and I have a need for that kind of syntax every day.

    I just tested it out and, sure-enought, it works.

    Thanks.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by PracticalProgram View Post
    SJahanzaib, thank you for showing me something in your code that I had never seen before, that will help me out immeasurably.

    Select top 1 @id=id,@c2=col1111 from Tabtest1
    ...
    I have not tried SJahanzaib's code.

    I just wonder how it will perform once an INSERT of a record fails and generates an error and results in a rollback. It won't result in an endless loop, due to the
    while @insert != @INSCount
    loop condition. That is a good thing.

    But suppose we have a source table with 1000 records, and the INSERT of the 11th record fails. There will be 990 records left in the source table. I wonder if
    Select top 1 @id=id,@c2=col1111 from Tabtest1
    won't keep returning the failing record over and over again (989 times), preventing the other records from being moved to the destination table.

    I think the rollback code should mark this record so it will not be SELECTed any more. It could be done with an extra column in the source table (easiest solution) or with an extra temp table that holds the Id's of failing records (when you are not permitted to alter the source table):
    INSERT INTO TempBadRecs (id) VALUE(@id)

    And the SELECT could be altered into:
    Select top 1 @id=id,@c2=col1111 from Tabtest1 WHERE NOT EXISTS (SELECT 1 FROM TempBadRecs as B where B.id = @id)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •