Loops to create a page!

Hi,

Right, I have this problem, and it;s more through lack of understanding vb.net that well more then an actual problem I will out line what I want to do,
basically it all revolves around a page that needs to be built when navigated to so it can be easily updated without anyone having to edit the code.

Get all the table names from a database
Loop through each of the results to build a statement
Nest a 2nd loop to split the returned data from the correct table
Build a listbox for each table returned

This is what I have currently, this works but the problem is, if another course is added, someone will need to manually edit the code on the page to add a new
code to get the new course hence why I want to create a loop that gets all the data so all someone needs to do is put in the all table the new course name. Please note
I cut this down to just show 2 result but there is about 30 odd.

1DBConn =New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=xx")2'Dim DBDataAdapter As SqlDataAdapter3 DBDataAdapter =New SqlDataAdapter("Select AOE,ADC, FROM TBL_Role WHERE Title = @.ddlTitle", DBConn)4 DBDataAdapter.SelectCommand.Parameters.Add("@.ddlTitle", SqlDbType.NVarChar)5 DBDataAdapter.SelectCommand.Parameters("@.ddlTitle").Value = TitleDropDown.SelectedValue6 DBDataAdapter.Fill(DSPageData,"Courses")78'Loop through each record, split at + and place in ListBoxs910 VarDS = DSPageData.Tables("Courses").Rows(0).Item("AOE")11Dim VarArrayAs String() = VarDS.Split("+")12Dim iAs Integer13 For i = 0To VarArray.Length - 11415Dim liAs New ListItem()16 li.Text = VarArray(i).ToString()17 li.Value = VarArray(i).ToString()18Me.txtAOE.Items.Add(li)19Next i2021 VarDS = DSPageData.Tables("Courses").Rows(0).Item("ADC")22 VarArray =Nothing23 VarArray = VarDS.Split("+")24 i =Nothing25 For i = 0To VarArray.Length - 12627Dim liAs New ListItem()28 li.Text = VarArray(i).ToString()29 li.Value = VarArray(i).ToString()30Me.txtADC.Items.Add(li)31Next i

Now here is my pseudo code to what I roughly want to do, hope it makes sense to someone and someone can point me in the correct direction. Please note,
I know the split bit works, so at the minute I am just trying to get the loop to get all my courses

1DBConn =New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=wgw;PWD=wgwsql;")2 DBSelect.Connection = DBConn3 DBSelect.Connection.Open()4'Get the row number in the database5 DBSelect.CommandText ="SELECT COUNT(*) FROM TBL_All"6 DBResult = DBSelect.ExecuteScalar()7 DBSelect.Connection.Close()8Dim Count = DBResult9'Get all the Tables and Keys in the Database's10 DBDataAdapter =New SqlDataAdapter("SELECT * FROM TBL_All", DBConn)11 DBDataAdapter.Fill(DSPageData,"All")12'declare all loop vars13Dim XAs Integer14 Dim YAs Integer15 Dim iAs Integer16'Loops through all the tables17Dim DSArrayAs String() = DSPageData.Tables("All").Items()18For Y = 0To Count19Dim VarDSAs String() = DSPageData.Tables("All").Rows(0).Item(DSArray(Y))20Dim SplitArrayAs String() = VarDS.Split("+")212223For i = 0To SplitArray.Length - 124Dim LiAs New ListItem()25 Li.Text = SplitArray(i).ToString()26 Li.Value = SplitArray(i).ToString()27Me.txt & DSArray(Y) &.Items.Add(Li)28Next i2930Next Y

I have just realised, in each loop for each course I will need to add the select statement so I think I need to add

DBDataAdapter = New SqlDataAdapter("SELECT " & Dr & "FROM TBL_" & DSArray(Y), DBConn)
DBDataAdapter.Fill(DSPageData, "Courses")

Into it

|||

ok think I am making some head way and I think I have logically what I want.

For every row of column TName in TBL_ALL, assign to var Dim TName
For every row of column PKey in TBL_ALL, assign to var Dim Pkey
For every row create statement "SELECT '" & Pkey & "' FROM '" & TName " "'")

Once I have made the loop that creates all of them select statements, I can run a loop to execute them,
and then a loop to assign the values to listboxes, but first step at a time.

Here is what I have so far, it doesn't work and I know its not right, but maybe someone can see where I am comming from
point my in a better direction. Please also note, the response.write bits are to check when my code i have working is working
but they are not needed

Thanks
Chris

1DBConn =New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;PWD=xx;")2 DBSelect.Connection = DBConn3 DBSelect.Connection.Open()4'Get the row number in the database5 DBSelect.CommandText ="SELECT COUNT(*) FROM TBL_All"6 DBResult = DBSelect.ExecuteScalar()7 DBSelect.Connection.Close()8Dim Count = DBResult9'Get all the Tables and Keys in the Database's10 DBDataAdapter =New SqlDataAdapter("SELECT TName FROM TBL_All", DBConn)11 DBDataAdapter.Fill(DSPageData,"TName")12 DBDataAdapter =New SqlDataAdapter("SELECT PKey FROM TBL_All", DBConn)13 DBDataAdapter.Fill(DSPageData,"PKey")14'declare all loop vars1516Dim DrAs DataRow17Dim DcAs DataColumn18'Loops through all the tables1920 'For Each Dr In DSPageData.Tables("All").Rows21 'For Each Dc In DSPageData.Tables("All").Columns22 'Dim Column As DataColumnCollection = DSPageData.Tables("All").Columns23 'Dim Table As String = (Dr(Dc.ColumnName.ToString))24 'Response.Write(Table)25 'DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;UID=wgw;PWD=wgwsql")26 'DBSelect.Connection = DBConn27 'DBSelect.Connection.Open()28 'DBSelect.CommandText = "SELECT '" & Dr(Dc.ColumnName.ToString) & "' FROM ('" & Table & "')"29 'DBSelect.ExecuteNonQuery()30 'Next Dc31 'Next Dr3233Dim Search()As SqlDataAdapter34Dim PKeyAs String35 Dim TNameAs String36 Dim ResultAs String()37For Each DrIn DSPageData.Tables("TName").Rows38For Each DcIn DSPageData.Tables("TName").Columns39'For i = 0 To Count40 PKey = (Dr(Dc.ColumnName.ToString))41 Response.Write(PKey)42 TName = (Dr(Dc.ColumnName.ToString))43 Response.Write(TName)44'DBSelect.CommandText = "SELECT '" & PKey & "' FROM '" & TName & "'"45 'DBResult = DBSelect.ExecuteNonQuery46 'Result(i) = DBResult47 'Next48Next49 Next50 For Each DrIn DSPageData.Tables("PKey").Rows51For Each DcIn DSPageData.Tables("PKey").Columns52'For i = 0 To Count53 PKey = (Dr(Dc.ColumnName.ToString))54 Response.Write(PKey)55 TName = (Dr(Dc.ColumnName.ToString))56 Response.Write(TName)57'DBSelect.CommandText = "SELECT '" & PKey & "' FROM '" & TName & "'"58 'DBResult = DBSelect.ExecuteNonQuery59 'Result(i) = DBResult60 'Next61Next62 Next63 End Sub

|||

maybe I am totally confusing everyone lol? I am so lost :(

loops oops

Hi,
How can I walk through a result of a select query? I have two tables.
TableA(id, name1, name2, notes), TableB(id, grade). I have to bulk insert
some data, (given: name1, name2, notes and grande too). The "bulk insert"
inserts datas in one step. So first I insert the values a temporary table
(TableTmp). Then I have to walk through this TableTmp and at each row I
have to do this:
1, insert name1, name2, notes into TableA
2, get back the actual @.@.identity
3, insert grade into TableB where is the last identity.
I would like to make it in sql query analizer...
I have problem with the loop. I have no idea how to make it.
Is it not overcomplicated? Is there any simple solution?
Thank you for help
chris
Message posted via http://www.webservertalk.comI don't think you'll need a loop. Usually it's best to avoid loops and row
by row processing.
Unfortunately you haven't given us any clues about keys or constraints or
shown us the table where this data comes from. So here is a wild guess:
INSERT INTO TableA (col1, col2, col3)
SELECT DISTINCT col1, col2, col3
FROM Unspecified
INSERT INTO TableB (id, col4, col5, col6)
SELECT DISTINCT
A.id /* the IDENTITY col from A */ ,
U.col4, U.col5, U.col6
FROM Unspecified AS U
JOIN TableA AS A
ON U.col1 = A.col1
AND U.col2 = A.col2
AND U.col3 = A.col3
David Portas
SQL Server MVP
--|||Mary,
To me it sounds like your database design is a bit odd.
You have the details of a student(?) in TableA, and the grade that the
student got in TableB. Presumably if you actually have:
TableA (students): id, name1, name2, notes...
TableC (subjects): id, code, description...
TableB (grades): studentid, subjectid, grade
then you could import your data into a temporary table... and then make sure
that all your subjects and students are listed:
insert into TableA (name1, ...)
select t.name1, ...
from TableTemp t
where not exists (select * from TableA a where a.name1 = t.name1 and a.name2
= t.name2 and ...)
(and similar for TableC)
And then insert the grades:
insert into TableC (studentid, subjectid, grade)
select stud.id, subj.id, t.grade
from TableTemp t JOIN TableA stud on stud.name1 = t.name1 and stud.name2...
JOIN TableC subj on subj.code = t.code ...
The '...' are the other fields and stuff that you're looking to identify
students by.
Of course, it's much nicer to have a separate file of "studentcode",
"subjectcode", "grade", if you can fetch the details like that.
Rob|||Thanx.
It seems working..Thank you very much

loops in mysql

can is use loops like while,for in mysql 3.9Hello,

what would you like to do ?

Best regards
Manfred Peter
Alligator Company Software GmbH
http://www.alligatorsql.comsql

Loops and building comma delimited strings

The problem:

I have 2 tables, with a one to many relationship - lets say customers, and order items.

Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this:

1, 2, 3

And if the next order item for that customer has a quantity of 4, the reference number value is

4, 5, 6, 7

And the final item with quantity of 2:

8, 9

Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric.

In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers.

If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it:

4, 5, 6 (2nd)
7,8 (3rd with same quantity of 2).

I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running.

This is what I have so far. The print lines and hard coded values are for debugging purposes only.

DECLARE @.NumberingType varchar(10)
DECLARE @.TotalSum int
DECLARE @.DoorLineItemID int
DECLARE @.Quantity int
DECLARE @.SeedInt int

SET @.SeedInt = 1

SELECT @.TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345

DECLARE UpdateRefCursor CURSOR FOR
SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1

OPEN UpdateRefCursor

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity
DECLARE @.RefNumberLine varchar(1024)
SET @.RefNumberLine = ''

WHILE @.@.FETCH_STATUS = 0
BEGIN

WHILE @.SeedInt <= @.Quantity
BEGIN

SET @.RefNumberLine = @.RefNumberLine + CONVERT(varchar, @.SeedInt, 101) + ', '
SET @.SeedInt = @.SeedInt + 1

END
PRINT @.RefNumberLine

SET @.SeedInt = @.Quantity + @.SeedInt
PRINT 'new seed: ' + CONVERT(varchar, @.SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @.Quantity + @.SeedInt, 101)

FETCH NEXT FROM UpdateRefCursor INTO @.DoorLineItemID, @.Quantity

END

CLOSE UpdateRefCursor
DEALLOCATE UpdateRefCursor

This returns the same delimited string for X number of items. So I'm getting this:

1,2,3
1,2,3
1,2,3

When I really want the results described above.

What am I doing wrong?

Thanks!

You really need to post a table structure and some data for us to use to try this out. That's a lot of variables with no data to reference to try out.|||solved. Thanks for your input.

Loops

I want to make a loop that extracts the first letter of each word in a given string, converts it to a capital letter, and then concats the string.
For example I want 'Happy Birthday To You' to end up as 'HBTY'--I used the products table in Northwind as example data
--hope this helps

select productname , CHARINDEX(' ', productname)as f1
into #a
from products

select productname , f1, substring(productname,f1+1,40)as bn1, charindex(' ',substring(productname,f1+1,30))as f2
into #b
from #a

select productname , f1, bn1, f2,
bn2 = case when f2 > 0 then substring(bn1,f2+1,40)else '' end,
f3 = case when f2 > 0 then charindex(' ',substring(bn1,f2+1,40))else '' end
into #c
from #b

select productname , f1, bn1, f2, bn2, f3,
bn3 = case when f3 > 0 then substring(bn2,f3+1,40)else '' end,
f4 = case when f3 > 0 then charindex(' ',substring(bn2,f3+1,40))else '' end
into #d
from #c

select productname , f1, bn1, f2, bn2, f3, bn3, f4,
bn4 = case when f4 > 0 then substring(bn3,f4+1,40)else '' end,
f5 = case when f4 > 0 then charindex(' ',substring(bn3,f4+1,40))else '' end
into #e
from #d

select productname,
left(productname,1)+left(bn1,1)+left(bn2,1)+left(b n3,1)+left(bn4,1) as product_abrev
from #e|||Wouldn't it be easier with a procedure? Thanks for your help.|||that was just off the cuff, never done that before, there might be an easier way but thats all I came up with in the few minutes I looked at it.

I am sure if I spent an hour or so on it I could come up with something better.

Good Luck|||Thanks again.

Looping through source connections

I am trying to build a package that loops through different SQL Server connections and puts the result set in a single SQL Server connection. I have looked at the Flat File example but this does not help. How do I loop through a list of SQL Servers (can be in a table, or file) make the connection to that SQL Server, run a script against Master to gather DB names (have this part) and write it to a reporting server (have this part). I have tried the For Loop and For Each Loop, but can't get either to work with SQL connections.

I have seen this question a lot, with no real answers. (Everyone who answers just points back to the Flat File example)

Thanks,

Pete

Let me make sure I understand what you are trying to do.

You want to loop through a table that basically gives you a servername,username and password. And for each servername found you want to connect to that server and execute a sql stmt or stored proc, get the results back and store the results on your reporting server.

Is that correct?

You could use property expressions to dynamically change the connections as you loop through your table.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75254&SiteID=1

|||Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.|||

Peter Cwik wrote:

Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.

Peter,

The reason everyone points to the flatfile example is because the steps that you need to go through are exactly the same. In the Flat File example you are talking about I am assuming (because you did not link to it) that you are setting the ConnectionString property using a property expression. Exactly the same in your scenario.

Can you explain why the Flat File example is not useful to you?

-Jamie

|||

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

|||I try to expand on the answer Darren gave in the post Jamie linked to above in this one of my own (GD at the number prepositions in this sentence!): http://forums.microsoft.com/MSDN/ShowPost.aspx?postid=843095&siteid=1

I'll admit there are things missing and not specific to your example, but if it gets you started, you may be able to figure the rest out.|||

Peter Cwik wrote:

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

Peter,

I don't think that response was warranted. People here are more than willing to help. I recommend you try and make your querie more specific though. i.e. Which particular step you are having problems with? If you're having trouble with all of them then let's take them one at a time - starting with the first one.

You are right that you need to "grab a list, parse through it while passing the information to the connection". Which part of that are you having problems with?

Assuming the list is in a database you can get that list into a SSIS variable using an Execute SQL Task.|||

The last step is where I lose it. I have been able to get the list, pass it to a variable. I couldn't figure out how to pass it a connection string (does that mean connection manager, tsql, vb...) I tried passing it to tsql string, which works as long as I have linked server connections, which is not what I want.

I think you may have misread my intent above. I never said that people here are not willing to help. If I thought that I wouldn't be here asking for help. My point is that experts sometimes get used to the higher level functions and assume others are on their level when giving instructions. In SSIS I am not advanced, I admit that, and I need a little hand holding to get going on this particular topic.

As for the flat file example, there are specific screens and prompts for inputing the name of a file and the directory. There is built-in functionality to handle this type of input for flat files but I have not found the same built-in functionality for cycling through SQL connections.

|||

That was pretty close. Thank you.

I had a developer write a VB script that does it all too, but like your example I'm trying to find the built in functionality of SSIS.

Thanks again.

Looping through source connections

I am trying to build a package that loops through different SQL Server connections and puts the result set in a single SQL Server connection. I have looked at the Flat File example but this does not help. How do I loop through a list of SQL Servers (can be in a table, or file) make the connection to that SQL Server, run a script against Master to gather DB names (have this part) and write it to a reporting server (have this part). I have tried the For Loop and For Each Loop, but can't get either to work with SQL connections.

I have seen this question a lot, with no real answers. (Everyone who answers just points back to the Flat File example)

Thanks,

Pete

Let me make sure I understand what you are trying to do.

You want to loop through a table that basically gives you a servername,username and password. And for each servername found you want to connect to that server and execute a sql stmt or stored proc, get the results back and store the results on your reporting server.

Is that correct?

You could use property expressions to dynamically change the connections as you loop through your table.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75254&SiteID=1

|||Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.|||

Peter Cwik wrote:

Yes you understand it right. The idea you expressed sounds right, but I don't know how to do it.

Peter,

The reason everyone points to the flatfile example is because the steps that you need to go through are exactly the same. In the Flat File example you are talking about I am assuming (because you did not link to it) that you are setting the ConnectionString property using a property expression. Exactly the same in your scenario.

Can you explain why the Flat File example is not useful to you?

-Jamie

|||

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

|||I try to expand on the answer Darren gave in the post Jamie linked to above in this one of my own (GD at the number prepositions in this sentence!): http://forums.microsoft.com/MSDN/ShowPost.aspx?postid=843095&siteid=1

I'll admit there are things missing and not specific to your example, but if it gets you started, you may be able to figure the rest out.|||

Peter Cwik wrote:

Jamie,

The Flat File example is not helpful because the syntax on the screens is different. When starting out on a new program like integration services specific instructions are needed. For example there are many places to right click and get properties, but if you don't have focus on the correct object the instructions on what to change in a property sheet won't make sense. Often times those that give advice forget the nuances of the programs. While we all laugh at the "idiot" who complains that "I can't click on your computer", we forget that newbies don't know that there is an icon on the main desktop called My Computer. I'm looking for specifics because I haven't done it before and its not intuitive.

I understand the concept of what I need to do, grab a list, parse through it while passing the information to the connection. But how? While the concept is clear there are obviously steps that need to be followed to get it to work. Its easy to get to the moon too, just build a vehicle that can move fast enough escape the earth's gravity. While the concept is understandable, without the specifics your going no where.

Sorry for my little rant, I do need and want help on the specific steps.

Hope this helps,

Peter Cwik

Peter,

I don't think that response was warranted. People here are more than willing to help. I recommend you try and make your querie more specific though. i.e. Which particular step you are having problems with? If you're having trouble with all of them then let's take them one at a time - starting with the first one.

You are right that you need to "grab a list, parse through it while passing the information to the connection". Which part of that are you having problems with?

Assuming the list is in a database you can get that list into a SSIS variable using an Execute SQL Task.|||

The last step is where I lose it. I have been able to get the list, pass it to a variable. I couldn't figure out how to pass it a connection string (does that mean connection manager, tsql, vb...) I tried passing it to tsql string, which works as long as I have linked server connections, which is not what I want.

I think you may have misread my intent above. I never said that people here are not willing to help. If I thought that I wouldn't be here asking for help. My point is that experts sometimes get used to the higher level functions and assume others are on their level when giving instructions. In SSIS I am not advanced, I admit that, and I need a little hand holding to get going on this particular topic.

As for the flat file example, there are specific screens and prompts for inputing the name of a file and the directory. There is built-in functionality to handle this type of input for flat files but I have not found the same built-in functionality for cycling through SQL connections.

|||

That was pretty close. Thank you.

I had a developer write a VB script that does it all too, but like your example I'm trying to find the built in functionality of SSIS.

Thanks again.

Looping Query

I know I have done this before but for the life of me I can remember how'
When I run the query below it loops and loops, how do I make it appear just
once - missing something but brain freeze has taken over aaaggghhh...
select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
[transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
from [T_GIFTCARDS], [transaction]
where req_login_time between '27 September,2004' and '28 September, 2004'
your help much appreciatedWhat do you mean by "The Query loops" ? A query doesn=B4t loop around on
its own, actually it doesn=B4t loop at all.|||same information in rows 1-84 shows up in query analyzer then row 85 shows
row 1 again then so on until row 829227262525......or until system stops
"Jens" wrote:

> What do you mean by "The Query loops" ? A query doesn′t loop around on
> its own, actually it doesn′t loop at all.
>|||examnotes (Ivo@.discussions.microsoft.com) writes:
> I know I have done this before but for the life of me I can remember how'
> When I run the query below it loops and loops, how do I make it appear
> just once - missing something but brain freeze has taken over
> aaaggghhh...
> select [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO],
> [transaction].[M_GIFTCARDNO], [transaction].[req_login_time]
> from [T_GIFTCARDS], [transaction]
> where req_login_time between '27 September,2004' and '28 September, 2004'
You have two tables in your WHERE clause, but there is no condition for
joining them, which means that you get call possible combination of
rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
rows in transactions for the given time range, the query will produce
one milliard rows.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog skrev:

> You have two tables in your WHERE clause, but there is no condition for
> joining them, which means that you get call possible combination of
> rows. If there are 1000 rows in T_GIFTCARDS, and there are a million
> rows in transactions for the given time range, the query will produce
> one milliard rows.
>
That would be one 'billion' in english, or are there even differences
between british and american english? (Not to mention Erland's swedish
english ;) )
/impslayer, aka Birger Johansson|||impslayer (impslayer@.hotmail.com) writes:
> That would be one 'billion' in english, or are there even differences
> between british and american english?
See http://www.m-w.com/dictionary/milliard.
However, both in British and American English, they capitalise "British",
"American" and "English". :-)
But maybe we should table this discussion before it goes too far. (Now,
let's see how many how get *that* one!)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog skrev:

> impslayer (impslayer@.hotmail.com) writes:
> See http://www.m-w.com/dictionary/milliard.
>
When will I ever (EVER!) learn to check stuff out before I make a
post?
Had no idea 'milliard' existed in English (sic!), and was hoping I'd
clarify that you meant 'billion'... Definitely no cheap shot, I promise
:)
/impslayer, aka NoBrainPoster|||impslayer (impslayer@.hotmail.com) writes:
> When will I ever (EVER!) learn to check stuff out before I make a
> post?
> Had no idea 'milliard' existed in English (sic!), and was hoping I'd
> clarify that you meant 'billion'... Definitely no cheap shot, I promise
>:)
Rest assured that if I ever say "billion", I mean a real big billion, and
not the wimpy billions they have over there!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Looping in Stored Procedures

can we write loops in MSSql stored procedures ?

my aim is to do something like

select * from table
while < recordset is not blank >
do something...
next record
end while

is this possible in stored procedure or should i do this from my client
application ?

pl explaintu can very well do this.
for eg:
create procedure looping
as
begin
declare @.l int
set @.l=10
while @.l>1
begin
print @.l
set @.l=@.l-1
end
end

or it can be a condition like
while exists (select * from table)
begin
some code...
end|||thanks for the information

but have one more qn

im SELECTing some rows from a table
and in the loop, i want to perform some operation on each selcted row
after performing the operations , will the loop moves to the next
record ?

pl comment|||no.
for that u need to use cursors.
but use of cursors is not the recomended way of doing things in sql.
so if u could post what u r trying to do, some one out here will surely be help u out.

Loop Through Flat Files Based On A Date Range

Hello,

I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;

CDNSC.CDNSC.SC00015.01012007

The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.

What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.

Any ideas on this?

Thank you for your help!

cdun2

One way is to move the files to an archive directory when you're done with them. That way you only pick up the files you need to process. Just throwing that idea out there.|||Thanks for your response. One thing that I have to be prepared for is that I may have to 'reload' data. The date range for any reloading could be anything.|||Well, you could (and perhaps should?) stage the data from the flat files in a staging table. Then, once in the staging table you can keep n number of loads around, all marked by either the load date, or a unique load number. After n number of loads, you can delete the old data. Each row in the staging table would also contain the source file name.

Or, you could simply load a table with the load date and a filename. That way, you've got a table that tells you which files to grab for a given load.
Load File
1 xyz.txt
1 fhs.txt
2 jfb.txt

The above still assumes that you "archive" the files into a subdirectory or somewhere.|||The data from the flat files will wind up in one ore more sql server tables. One idea I was considering was to stored the file names in a table, and maybe parse the date portion of the file name into a column. Then somehow use that as a lookup table to determine which files would be processed.|||

cdun2 wrote:

The data from the flat files will wind up in one ore more sql server tables. One idea I was considering was to stored the file names in a table, and maybe parse the date portion of the file name into a column. Then somehow use that as a lookup table to determine which files would be processed.

Right, but the point is that your first data flow simply loads the data from the files AS IS into a table. Then in the data flow that you use today, you point them to the staging table instead of the flat file(s).|||Oh, I see. The data that I need to process is a subset, and will be aggregated for reporting. The concern that I have is that somewhere down the road, the reporting requirements may change, and the users may need another column from the data. I'm thinking that if that happens, I'll need to add the additional column to the subset table, and reload everything.|||

cdun2 wrote:

Oh, I see. The data that I need to process is a subset, and will be aggregated for reporting. The concern that I have is that somewhere down the road, the reporting requirements may change, and the users may need another column from the data. I'm thinking that if that happens, I'll need to add the additional column to the subset table, and reload everything.

You're going to have to add the column in the flat file source anyway, would be one argument.

Regardless, this is just one way to tackle this problem. There are other ways I'm sure.|||

Actually, the destination table will have just a subset of the columns that are found in the flat files. If a need is found for an addional column of data, it will already be in the flat files. I'll just need to add it to the destination table.

What kind of component could I use in a DataFlow Task to read filenames?

Thanks again.

|||

cdun2 wrote:

Actually, the destination table will have just a subset of the columns that are found in the flat files. If a need is found for an addional column of data, it will already be in the flat files. I'll just need to add it to the destination table.

What kind of component could I use in a DataFlow Task to read filenames?

Thanks again.

If this is the case, then the staging table example still works. In your CONTROL flow, you'd use a foreach loop to spin through the files. Each filename can be put in a variable, which can be used in a data flow inside the foreach loop. Then, using a derived column in the data flow, you'd have access to the variable that contains the current filename.|||

cdun2 wrote:

Hello,

I currently have a For Each File container that loops through all files from a specific directory. The files have a naming convention that looks like this;

CDNSC.CDNSC.SC00015.01012007

The last segment of the file name is the date of the data in the file (mmddyyyy). The create date for these files is always a day later than indicated in the file name.

What I would like to do is to have more control over the 'range' of files that are looped through by using the date portion of the file name to define what group of files should be looped through. Ideally, I would like to have a 'StartDate' variable and an 'EndDate' variable that I could define at run time for the package, and the package would loop through all of the files where the date portion of the file name fell between 'StartDate' and 'EndDate'.

Any ideas on this?

Thank you for your help!

cdun2

Cdun2,

This is an alternative approach to the issue of processing only the files that are within a start/end date range. It uses Expression and precedence constraints:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

BTW,

Just to save extra work down the road; i would recomment to import all the columns of the files from the begining.

|||Thanks again for your input. I'll dig a little deeper into this next week. As far as bringing in all of the columns of data, the attempt is to aggregate so that we can meet a reporting need, and reduce the number of rows of data that we need to store. Its a resource/performance issue for us. I can't bring in any more than I need.|||

cdun2 wrote:

I can't bring in any more than I need.

But you can. To Rafael's point, you can at least have ALL of the flat file columns mapped in the connection manager. You don't have to do anything with them in the data flow, but at least they are there if you need to pick them up later; you won't have to redefine the connection manager.|||

Phil Brammer wrote:

cdun2 wrote:

I can't bring in any more than I need.

But you can. To Rafael's point, you can at least have ALL of the flat file columns mapped in the connection manager. You don't have to do anything with them in the data flow, but at least they are there if you need to pick them up later; you won't have to redefine the connection manager.

Yes, that makes sense. Thanks again.

|||

I took a look at the example here; http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

Thank you for providing this example. I am having a difficult time making the bridge between the precendent constraint, and the Data Flow Task where the content of the qualifying flat files are inserted into the SQL Server table.

Loop through each record and then each field within each record

I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.

Something like this maybe using a cursor or something else:

For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next

So below, all I need to do is figure out how to loop through each column for the current record in the cursor

AS

DECLARE Create_Final_Table CURSOR FOR

SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1

OPEN Create_Final_Table

FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2

WHILE @.@.FETCH_STATUS = 0
BEGIN

@.Chapter = chapter for this record

For each column in current record <-- not sure how to code this part is what I'm referring to

do some stuff here using sql for the column I'm on for this row

Next

Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record

Case 13

Insert Record
Insert Record
Insert Record

Case 11

Insert Record
Insert Record
Insert Record

Case 12

Insert Record
Insert Record
Insert Record

END

close Create_Final_Table
deallocate Create_Final_TableI need to essentially do 2 loops.Light fuse...One loops through each record and then inside each record row...stand back...I want to perform an insert on each column...cover ears...DECLARE Create_Final_Table CURSOR FOR

SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1

OPEN Create_Final_Table

FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2

WHILE @.@.FETCH_STATUS = 0
BEGIN

@.Chapter = chapter for this record

For each column in current record <-- not sure how to code this part is what I'm referring to

do some stuff here using sql for the column I'm on for this row

Next

Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record

Case 13

Insert Record
Insert Record
Insert Record

Case 11

Insert Record
Insert Record
Insert Record

Case 12

Insert Record
Insert Record
Insert Record

END

close Create_Final_Table
deallocate Create_Final_Table
KABOOM!!!!!!!!!!

Why are you doing this?|||Are you trying to normalize this beast? If so, I'd do one insert operation per column in the original table. Fast, easy, clear, simple... What's not to like?

-PatP

Loop through each record and then each field within each record

I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.
Something like this maybe using a cursor or something else:
For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next
So below, all I need to do is figure out how to loop through each column for the current record in the cursor

AS
DECLARE Create_Final_Table CURSOR FOR
SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1
OPEN Create_Final_Table
FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2
WHILE @.@.FETCH_STATUS = 0
BEGIN
@.Chapter = chapter for this record
For each column in current record <- not sure how to code this part is what I'm referring to
do some stuff here using sql for the column I'm on for this row

Next
Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record
Case 13
Insert Record
Insert Record
Insert Record
Case 11
Insert Record
Insert Record
Insert Record
Case 12
Insert Record
Insert Record
Insert Record
END
close Create_Final_Table
deallocate Create_Final_Table

Also, if you think there is a better way to do this, let me know.

Are you inserting from EBN_TEMP1 into multiple tables? If so then you can just use series of INSERT...SELECT statements. You need to reference the column you need in each SELECT statement.|||

I have to take every record from my select, cycle through each. So let's say I cycle to the first record in my cursor. I need to then cycle through each field in that row and take that field and do something with it.

Then move on to the next row, cycle through it's fields one by one and so on till I have done this for every row in my cursor. I just don't know how to cycle and reference each column in a unique row after each iteration of my cursor's rows

What I'll be doing wtih each colum is taking the value and inserting it into another table with some other values I'll specify in a select.

|||There must be a way to do a loop to go through each field in a cursor row, but I haven't come up with any and have searched internet forever. This is shocking that nobody has ever brought this up. All they talk about is looping through a cursor's rows or just rows in general, not how to take a row and loop through to do something with every single column (field) in the row. I have a good reason for this need so please don't ask why if you're tempted to.|||

I'm not trying to be rude whatsoever but to me that's inefficient to create multiple inserts and selects. But of course you probably didn't know that those selects and inserts would be inserting the same values, only the field value is changing in the statement at each iteration. So that's why I don't want to basically rewrite the same insert and select. I just need to loop through each and move in the value to a parameter in my insert statement

|||

SQL is not a procedural language so it is best to approach the problem with a set oriented mindset. And this is often hard to do. So if you can perform the operation efficiently using DMLs alone it is much more efficient for the engine and it is also easier for you to maintain the code. Let's take an example. (You have to provide some examples as to what you are doing in the insert. You didn't answer my question about whether you are inserting into multiple tables)

insert into t1 (f1, f2)

select f1, f2 -- any computations on the columns can be done here

from tbl

....

insert into t1 (f3, f4)

select f3, f4 -- any computations on the columns can be done here

from tbl

....

So there is nothing like looping through each column. There simply isn't any construct in TSQL or similar procedural languages in RDBMSes. On the other hand if you want to unpivot the results then you can do that using UNPIVOT operator in SQL Server 2005 or use SQL again. To use SQL to unpivot the operation of converting columns to rows then you can do something like below:

-- traditional SQL way

select f1

from tbl

...

union all

select f2

from tbl

....

-- another less obvious method

select case c.c when 1 then f1 when 2 then f2 end as f

from tbl

cross join (select 1 union all select 2) as c(c)

If you do not want to repeat the query multiple times then you can define a view or inline table-valued function or temporary table or table variables and use it instead. So there are many ways to avoid duplication of code. Best is to describe your problem rather than showing procedural code since there are many ways to perform the same set of operations in SQL much more efficiently and elegantly.

|||

My insert will look lik this and all go into one table because that table will end up being the flat file I create

Insert into table1 'a1', 'b1', @.ColumnName, @.ColumnValue, 'IO'

so for each column in the row, I have to insert it as a separate record into my final table.

Yes, this is inefficient but I have to do this for our stupid ERP system which whose UI only can map updates based on individual field records from a flat file....don't ask me why, it's retarted. they will take my flat file an use it in conjunctiuon with the ERP Import GUI to do so, I just have to create the flat file. Before the process was:

1) receive txt comma delimited file from our vendor

2) Parse it out into an MS Access Table

3) Create an individual record for each column in each row and include the AccountID with it and some other static values

4) save it as a fixed length flat file

Now I'm automating this process for them using SQL Server 2005 Integration Services. My flow is like this:

1) Use Flat File Source to import the comma delimmeted txt file (650,000 records)

2) Use Conditional Split to determine which records to filter out

3) Use OLE DB Destination Editor to move in the records to a table

4) Use a SQL TASK to code the splitting out of each field of each row into a new record in my final table. The final table will be used to create the fixed length flat file in the end.

#4 is what I'm trying to do. I have to include the following fields for each record in my final table:

AccountID, 'a1', 'b1', ColumnName, ColumnValue

So in other words for each row in my table that the OLE DB added my records to, I then have to split out each column for each row into a final table including the account D for every row.

I hope this makes sense, it's not as confusing as it seems.

|||

so expanding on my last post, this may give you a sense:

Let's say the OLE DB moves my records into a table initially for step 3. The table now looks something like this:
Acct # Zip Phone Addr
11223 23232 333-444-5555 6556 Duns Rd.
12345 34343 222-444-3333 1000 Aspire Blvd.
I need to create a record using the Acct # and column for each column as well as append some other values like this into a final table. That final table will be a flat file in the end, I just need to figure out how to get this done first.
11223 23232 othervalue1 othervalue2
11223 333-444-5555 othervalue1 othervalue2
11223 6556 Duns Rd. othervalue1 othervalue2
12345 34343 othervalue1 othervalue2
12345 222-444-3333 othervalue1 othervalue2
12345 1000 Aspire Blvd. othervalue1 othervalue 2

|||If you are using SSIS then there is really no reason to denormalize the data in SQL Server. You can just do it in SSIS. Look at the foreach loop container in SSIS. This should allow you to loop through each column. If you have more questions about SSIS please post in the SQL Server Integration Services forum.|||ok, so then if I use the for each, how do I add my sql statement and have it refer to each column for the row I'm on?|||No. You get the data from the table as is and then perform the transformation on the client side. This is easier to do. For example, if you get a datareader for the results then you can use the columns collection with foreach container and loop through each column. If you post the question in the SSIS forum you will get more solutions.|||thanks so much|||

I am following your advice on an SSIS package I have that must evaluate each record. The issue I am having is that the dataReader destination is far slower then the recordset destination. Problem is I can not figure out how to get data from the record set.

Loop through each record and then each field within each record

I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.
Something like this maybe using a cursor or something else:
For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next
So below, all I need to do is figure out how to loop through each column for the current record in the cursor

AS
DECLARE Create_Final_Table CURSOR FOR
SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1
OPEN Create_Final_Table
FETCH FROM Create_Final_EBN_Table INTO @.FieldName, @.AcctNumber, @.Screen, @.CaseNumber, @.BKYChapter, @.FileDate, @.DispositionCode, @.BKUDA1, @.RMSADD2, @.RMSCHPNAME_1, @.RMSADDR_1,
@.RMSCITY_1, @.RMSSTATECD_1, @.RMSZIPCODE_1, @.RMSWORKKPHN, @.BKYMEETDTE, @.RMSCMPNAME_2, @.RMSADDR1_2, @.RMSCITY_2, @.RMSSTATECD_2,
@.RMSZIPCODE_2, @.RMSHOMEPHN, @.BARDATE, @.RMSCMPNAME_3, @.RMSADD1_2, @.RMSADD2_3, @.RMSCITY_3, @.RMSZIPCODE_3, @.RMSWORKPHN_2
WHILE @.@.FETCH_STATUS = 0
BEGIN
@.Chapter = chapter for this record
For each column in current record <- not sure how to code this part is what I'm referring to
do some stuff here using sql for the column I'm on for this row

Next
Case @.Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record
Case 13
Insert Record
Insert Record
Insert Record
Case 11
Insert Record
Insert Record
Insert Record
Case 12
Insert Record
Insert Record
Insert Record
END
close Create_Final_Table
deallocate Create_Final_Table

Also, if you think there is a better way to do this, let me know.

Are you inserting from EBN_TEMP1 into multiple tables? If so then you can just use series of INSERT...SELECT statements. You need to reference the column you need in each SELECT statement.|||

I have to take every record from my select, cycle through each. So let's say I cycle to the first record in my cursor. I need to then cycle through each field in that row and take that field and do something with it.

Then move on to the next row, cycle through it's fields one by one and so on till I have done this for every row in my cursor. I just don't know how to cycle and reference each column in a unique row after each iteration of my cursor's rows

What I'll be doing wtih each colum is taking the value and inserting it into another table with some other values I'll specify in a select.

|||There must be a way to do a loop to go through each field in a cursor row, but I haven't come up with any and have searched internet forever. This is shocking that nobody has ever brought this up. All they talk about is looping through a cursor's rows or just rows in general, not how to take a row and loop through to do something with every single column (field) in the row. I have a good reason for this need so please don't ask why if you're tempted to.|||

I'm not trying to be rude whatsoever but to me that's inefficient to create multiple inserts and selects. But of course you probably didn't know that those selects and inserts would be inserting the same values, only the field value is changing in the statement at each iteration. So that's why I don't want to basically rewrite the same insert and select. I just need to loop through each and move in the value to a parameter in my insert statement

|||

SQL is not a procedural language so it is best to approach the problem with a set oriented mindset. And this is often hard to do. So if you can perform the operation efficiently using DMLs alone it is much more efficient for the engine and it is also easier for you to maintain the code. Let's take an example. (You have to provide some examples as to what you are doing in the insert. You didn't answer my question about whether you are inserting into multiple tables)

insert into t1 (f1, f2)

select f1, f2 -- any computations on the columns can be done here

from tbl

....

insert into t1 (f3, f4)

select f3, f4 -- any computations on the columns can be done here

from tbl

....

So there is nothing like looping through each column. There simply isn't any construct in TSQL or similar procedural languages in RDBMSes. On the other hand if you want to unpivot the results then you can do that using UNPIVOT operator in SQL Server 2005 or use SQL again. To use SQL to unpivot the operation of converting columns to rows then you can do something like below:

-- traditional SQL way

select f1

from tbl

...

union all

select f2

from tbl

....

-- another less obvious method

select case c.c when 1 then f1 when 2 then f2 end as f

from tbl

cross join (select 1 union all select 2) as c(c)

If you do not want to repeat the query multiple times then you can define a view or inline table-valued function or temporary table or table variables and use it instead. So there are many ways to avoid duplication of code. Best is to describe your problem rather than showing procedural code since there are many ways to perform the same set of operations in SQL much more efficiently and elegantly.

|||

My insert will look lik this and all go into one table because that table will end up being the flat file I create

Insert into table1 'a1', 'b1', @.ColumnName, @.ColumnValue, 'IO'

so for each column in the row, I have to insert it as a separate record into my final table.

Yes, this is inefficient but I have to do this for our stupid ERP system which whose UI only can map updates based on individual field records from a flat file....don't ask me why, it's retarted. they will take my flat file an use it in conjunctiuon with the ERP Import GUI to do so, I just have to create the flat file. Before the process was:

1) receive txt comma delimited file from our vendor

2) Parse it out into an MS Access Table

3) Create an individual record for each column in each row and include the AccountID with it and some other static values

4) save it as a fixed length flat file

Now I'm automating this process for them using SQL Server 2005 Integration Services. My flow is like this:

1) Use Flat File Source to import the comma delimmeted txt file (650,000 records)

2) Use Conditional Split to determine which records to filter out

3) Use OLE DB Destination Editor to move in the records to a table

4) Use a SQL TASK to code the splitting out of each field of each row into a new record in my final table. The final table will be used to create the fixed length flat file in the end.

#4 is what I'm trying to do. I have to include the following fields for each record in my final table:

AccountID, 'a1', 'b1', ColumnName, ColumnValue

So in other words for each row in my table that the OLE DB added my records to, I then have to split out each column for each row into a final table including the account D for every row.

I hope this makes sense, it's not as confusing as it seems.

|||

so expanding on my last post, this may give you a sense:

Let's say the OLE DB moves my records into a table initially for step 3. The table now looks something like this:
Acct # Zip Phone Addr
11223 23232 333-444-5555 6556 Duns Rd.
12345 34343 222-444-3333 1000 Aspire Blvd.
I need to create a record using the Acct # and column for each column as well as append some other values like this into a final table. That final table will be a flat file in the end, I just need to figure out how to get this done first.
11223 23232 othervalue1 othervalue2
11223 333-444-5555 othervalue1 othervalue2
11223 6556 Duns Rd. othervalue1 othervalue2
12345 34343 othervalue1 othervalue2
12345 222-444-3333 othervalue1 othervalue2
12345 1000 Aspire Blvd. othervalue1 othervalue 2

|||If you are using SSIS then there is really no reason to denormalize the data in SQL Server. You can just do it in SSIS. Look at the foreach loop container in SSIS. This should allow you to loop through each column. If you have more questions about SSIS please post in the SQL Server Integration Services forum.|||ok, so then if I use the for each, how do I add my sql statement and have it refer to each column for the row I'm on?|||No. You get the data from the table as is and then perform the transformation on the client side. This is easier to do. For example, if you get a datareader for the results then you can use the columns collection with foreach container and loop through each column. If you post the question in the SSIS forum you will get more solutions.|||thanks so much|||

I am following your advice on an SSIS package I have that must evaluate each record. The issue I am having is that the dataReader destination is far slower then the recordset destination. Problem is I can not figure out how to get data from the record set.

Loop through all User Tables

Hi,
I would like to execute a sql statement on all user tables of my db. Do you
know how to script that this statement loops through all user tables?
Thanks in advance
Graham SmithMay this *undocumented* proc will help
EXEC sp_MSforeachtable 'SELECT TOP 1 * FROM ?'
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Graham Smith" <graham.smith@.bbank.com> wrote in message
news:uEp8hj33FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I would like to execute a sql statement on all user tables of my db. Do
> you know how to script that this statement loops through all user tables?
> Thanks in advance
> Graham Smith
>|||You can generate one this way:
SELECT 'SELECT TOP 1 * FROM ['
+ TABLE_SCHEMA + '].['
+ TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'
AND OBJECTPROPERTY(OBJECT_ID('['
+ TABLE_SCHEMA + '].['
+ TABLE_NAME + ']'), 'IsMsShipped') = 0
"Graham Smith" <graham.smith@.bbank.com> wrote in message
news:uEp8hj33FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I would like to execute a sql statement on all user tables of my db. Do
> you know how to script that this statement loops through all user tables?
> Thanks in advance
> Graham Smith
>