While Loops

Question

If I want to perform 14 inserts into 7 different temp tables selecting the same data using different dates, where do I specify in my While Loop statement how to do so?  Is this correct?

declare@ Loop int

set Loop=1

WHILE@ Loop <=14

BEGIN

--insert

SET @Loop=@Loop + 1

END

Should the statement go before or after it selects the appropriate data to insert?

Answer 1

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

This is minimal polite behavior on SQL forums. 

>> If I want to perform 14 inserts into 7 different temp tables selecting the same data using different dates,  where do I specify in my WHILE @local_loop_counter statement how to do so? <<

This is a crazy way to write SQL. Temp tables are how non-SQL programmers fake hanging scratch tapes in T-SQL, so they can keep doing magnetic tape file processing and not RDBMS. An SQL programmer would use the date column in the original table (if you had been polite enough to post the DDL) to tell us which of the seven subsets a row belongs.  If you need to keep them separate, use VIEWs. 

Actually, this is not even a magnetic tape mindset! Have you ever seen a punch card sorter? That is what you are trying to write in T-SQL! Temp tables to mimic sorter pockets!  

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 2

declare@ Loop int
 
set Loop=1
 
WHILE@ Loop <=14
 
BEGIN
 
/**************  DYNAMIC SQL here **************/
 
SET @Loop=@Loop + 1
 
END

Look how it's done here:

http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 3

Yes, thats correct

declare@ Loop int
set Loop=1
WHILE@ Loop <=14
BEGIN
--insert
--INSERT STATEMENTS
SET @Loop=@Loop + 1
END

Regards
satheesh.

Answer 4

Why would there be a WHILE loop in the first place?

Since we don't know your tables, we cannot write the WHILE loop for you. But I doubt that dynamic SQL as suggested in one post is the solution.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

A more polite tone of voice would be greatly appreciated.  Thanks but no thanks for the help (also known as being just rude)!

Answer 6

Any progress?

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: SQL Server 2012 Programming