Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Looping with SQL

Posted on 2014-12-30
9
Medium Priority
?
99 Views
Last Modified: 2014-12-31
Hello

I want to run a very quick query against a table (1)
From this table I would like to extract some key records, and insert them into a new table (x)
I would then like to repeat this process on the next table (2) and repeat until I am out of tables.
The tables are all named with the date they were created as follows:
tbl_Table_2014_12_15
tbl_Table_2014_12_14
tbl_Table_2014_12_13
tbl_Table_2014_12_12

Is this something I am able to do with SQL?

Thanks
0
Comment
Question by:BananaFury
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40523424
Hi,

You can do this with While Loop.

Take Temp table store table name into it
Assign count of that table to variable and in the loop assign table names to another variable

In the query side perform dynamic query passing source and destination table

Hope this will help you
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40523427
0
 

Author Comment

by:BananaFury
ID: 40523593
Hello,

Thanks very much for your replies.

I confess, I am modest with SQL at best. Could you please provide me with an example of the code required for my particular scenario?

Thanks
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40523601
my article has sample code, only you need to fill in the part of the sql that you need
your explanation is vague, so providing some "end code" will not be possible...
as a side note, your requirement looks like you may actually need to step back and check if for example a partitioned table is the better/faster option in regards what you may need to do.
so, question 1: what is the process about, actually (why do you want to implement this)
question 2: please clarify what data should go from where to where exactly, and what about existing data etc...
0
 

Author Comment

by:BananaFury
ID: 40523608
Hello,

Thanks for your help.
I want to go through each of the tables and pull from them all of the Open records. These records I would like to place in a separate table that contains only the Open records, but from each table (which is a point in time)

Below is the only way I know how to tackle such a task. We have 3 years of tables though, so 1000+ tables so I suspect there is an easier way of doing this.


INSERT INTO tbl_All_Open_Records
SELECT *
FROM tbl_Table_2014_12_15 WHERE [Status] = 'Open'

INSERT INTO tbl_All_Open_Records
SELECT *
FROM tbl_Table_2014_12_14 WHERE [Status] = 'Open'

INSERT INTO tbl_All_Open_Records
SELECT *
FROM tbl_Table_2014_12_13 WHERE [Status] = 'Open'

..... etc
0
 
LVL 18

Assisted Solution

by:Simon
Simon earned 700 total points
ID: 40523670
Guy's advice to take a step back is good :)

If you're doing this as a one off and do want to get all the relevant table names to loop through with dynamic SQL...

SELECT table_name FROM YourDatabaseName.information_schema.tables
where TABLE_TYPE='base table'
and TABLE_NAME like 'tbl_Table_20[0-9][0-9][_][0-1][0-9][_][0-3][0-9]'

Open in new window


You could use this query to populate  the table-valued variable @c that Guy shows in his non-cursor code version in his article:

declare @c table (id int identity(1,1),name sysname)

insert into @c ( name) SELECT  table_name FROM [Your Database Name Here].INFORMATION_SCHEMA.tables
where TABLE_TYPE='base table'

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 40523695
so, indeed we take the code template from:
http://www.experts-exchange.com/Database/MS-SQL-Server/A_13640-processing-cursor-vs-temp-table-syntax.html
-- starting with the variables you will need in the processing part
declare @name sysname
declare @l nvarchar(max)

-- here is the table variable definition, which lives only for the duration of the run and is cleaned up automatically
-- for "small" results, it will stay purely in memory; larger sets may be "stored" in the temp database
declare @c table (  name sysname )
-- fill the table with data, update your select as you need it
-- if your process requires some "order" in the processing, still an ORDER BY here will be useless (unless you also use and need a TOP X clause) see below on where you have to put the ORDER BY

set nocount on
insert into @c ( name)
SELECT TABLE_NAME 
  FROM information_schema.tables
 WHERE TABLE_TYPE='base table'
   AND TABLE_CATALOG='YourDatabaseNameHere'
   AND TABLE_NAME like 'tbl_Table_20[0-9][0-9][_][0-1][0-9][_][0-3][0-9]' 

-- process, either if the above insert did at least 1 row, or if the below "delete" did indeed 
while @@ROWCOUNT <> 0
begin
  -- fetch 1 record from the table. 
  -- if your process requires some "order" in the processing, put the corresponding ORDER BY here
  select top 1 @name = name from @c

  if @@ROWCOUNT <> 0
  begin
   -- process the data here, exactly the same as with the cursor example.
   set @l =  ' INSERT INTO tbl_All_Open_Records
     SELECT *
	FROM [' + @name + '] WHERE [Status] = ''Open''
          '

   print @l
   exec(@l)
  end

  -- here we delete the row from the temp table , which is nothing else than a processing queue table, hence
  delete @c where name = @name
END 

Open in new window

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 300 total points
ID: 40523916
Here's some thoughts on this, in case they help.

I'd strongly suggest to using transactions for each month rather than day.  That many separate transactions will take longer and generate more log usage.  Even then, be sure to have the necessary pre-allocated, and thus pre-formatted, log space available before the loads start.

I'd also suggest -- on the extremely limited info so far -- clustering the final table on date, as I suspect that's how it will primarily be processed.  [Unless obviously you've already defined another clustering key that is NOT an identity.]

My preferred approach would be to dynamically create multiple jobs, one per affected month, to do the loading rather than using in-line execution.  This gives these advantages:
(1) async execution of multiple/all loads -- sp_start_job simply starts the job and returns, so multiple jobs could easily be run at once
(2) can easily re-run a single month without adjusting code;
(3) monthly transaction size -- hopefully not too big, but not too small either.
0
 

Author Closing Comment

by:BananaFury
ID: 40525077
Thanks very much for all of your help
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question