Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: Is there any option to use loop statement in MS SQL Stored Procedure?

    Is there any option to use loop statement in MS SQL Stored Procedure?
    Suppose I have two tables Table-A and Table-B
    Table-A
    ID Name Phone
    1 X 12354
    2 Y 3689
    3 Z 2578

    I want to insert all the values of Table-A into Table-B by using Stored Procedure
    I am using MS SQL 2005

    Thanks in advance
    Rashed

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There are several loop syntaxes but you should hardly ever use them. Look at INSERT INTO syntax in Books Online (SQL Server help)

  3. #3
    Join Date
    Sep 2008
    Posts
    5
    Hi,
    you can do this without using any loop statement. Since looping slowdown the process.

    you can user select into statement or insert into statements.

    Sathiesh

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sathiesh2005 View Post
    you can user select into statement or insert into statements.
    you could, but using one of those two approaches would be a mistake

    thanks for playing, please try again

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You have something against "insert into"?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    You have something against "insert into"?
    nope, i like that one

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry. Missed your "one" of these two, qualifier...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    Sorry. Missed your "one" of these two, qualifier...
    no prob

    for someone who makes as few mistakes as you, you are allowed an occasional oversight

    i am correct, yes? SELECT INTO would be wrong in this case?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Actually there's not much of a bad thing, providing that prior to the statement the table in the INTO clause has been verified to be absent. But of course it comes from the laziness of the coder, who chooses to avoid typing the CREATE with all the fields, then the INSERT with possibly fewer fields, which leads to having to type the SELECT. Understandable, but sloppy
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SELECT INTO used to be really bad, because it was a completely unlogged transaction and would blow away your ability to restore logs, necessitating a full backup to restart the recoverability sequence. But that was WAY back in time, before even version 7 I think.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That was in pre-SP5a of 6.5. They finally fixed it in 5a, and they also fixed locking sysobjects when implicit CREATE gets executed when you fire SELECT...INTO. Since then, it became more efficient in multi-user environment to use the latter vs. CREATE/INSERT...Man, I feel old talking about that time...Where's that REFIT script I wrote 14 years ago?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i wonder if it's worth pointing out in the context of this thread that table B already exists and probably has rows

    would you still recommend SELECT INTO?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hush up Rudy. You're interrupting our reminiscing about the good ol' days.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by r937 View Post
    i wonder if it's worth pointing out in the context of this thread that table B already exists and probably has rows

    would you still recommend SELECT INTO?
    Well...Ummmm...Can I say "No" or it's too late? I was just answering Sathiesh's comments earlier...Please...I won't do it again...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do NOT let that Canadian boss you around. Next thing you know, they'll be trying to invade. Yes, I know Texas is a long way from the border, but here in Ohio we have historical experience with Canadian aggression.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

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
  •