How do I loop through the records in a temporary table

How do I loop through the records in a temporary table?
ThanksHi Tim
Processing rows is usually less efficent than using set based commands on
your data set, therefore if you can use a set based solution is should be
better. You can use a CURSOR on a temporary table for example:
SELECT LastName, FirstName
INTO #Employees
FROM Northwind.dbo.Employees
WHERE LastName like 'B%'
DECLARE @.LastName [nvarchar] (20) ,
@.FirstName [nvarchar] (10)
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName
FROM #Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor INTO @.LastName, @.FirstName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.FirstName + N' ' + @.LastName
FETCH NEXT FROM Employee_Cursor INTO @.LastName, @.FirstName
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
DROP TABLE #Employees
Check out information on DECLARING/OPENING/CLOSING/DEALLOCATING and FETCHING
from cursors in Books Online
John
"Tim Kelley" wrote:
> How do I loop through the records in a temporary table?
> Thanks
>
>

How do I know why some records were not uploaded

This is an upload from foxpro to SQL Server.

My package loads about a million records and reject about 5,000 records. The rejected records are routed to a foxpro table.

Is there a way to find out why the records were rejected and sent to the error table?. I need to determine the reason so I can fix the records and try to upload them again.

Any help is appreciated.

Thanks

Try removing the error flow on the destination (sql server) and see what the error message is.|||Turn on verbose logging, and make sure that your error code and error column from your sql destination are part of your error destination.|||

Thanks, for the help. I do I turn on verbose logging?

|||

SQL Server 2005 Books Online

Adding and Configuring Logging

|||

I think you grabbed the wrong url (at least it didn't link up right when I tried to click it)...

Here is the link I saw for adding and configuring logging:

http://technet.microsoft.com/en-us/library/ms167456.aspx

Here is a more in depth list of topics on logging:

http://technet.microsoft.com/en-us/library/ms141727.aspx

|||Yes, that is the one.... thanks Eric

How do I get a count of all records returned.

I'm trying to put the total number of records returned from from a query in the bottom of our report. I don't want to do a count(*) in my sql stmt.

thanks.

Hello,

Try this in your table footer:

=CountRows()

Hope this helps.

Jarret

|||putting =CountRows() in my footer give me 1. What may I be missing here?|||

Use =CountRows("DataSet1") where DataSet1 is the name of your dataset that is bound to your table.

Shyam

|||

Try this:

=countDistinct(Fields!name.Value)

It works for me.

How do I find the oldest record in a table

Hi
In one of my tables in the MS SQL database all records are time stamped.
I want to know what is the oldest record.
I have used SELECT TOP 1 * FROM <table> WHERE Time > May 12 1990...
-and it works because I know the data in the table is newer than 1990 but is
there a more intelligent way of doing it?
(I also think it work because the primary key has an ascending sorting
order).
Thanks for Your help.
Regards
Kjell Arne JohansenIs the time stamp unique in your table?
If so, use:
SELECT TOP 1 * FROM <table> ORDER BY ts DESC
Or
SELECT * FROM T1
WHERE ts = (SELECT MAX(ts) FROM T1)
If it's not unique, use:
SELECT TOP 1 * FROM <table> ORDER BY ts DESC, key DESC
Or
SELECT * FROM T1
WHERE key =
(SELECT MAX(key) FROM T1
WHERE ts = (SELECT MAX(ts) FROM T1))
BG, SQL Server MVP
www.SolidQualityLearning.com
"Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
news:E3jhe.10021$SL4.226180@.news4.e.nsc.no...
> Hi
> In one of my tables in the MS SQL database all records are time stamped.
> I want to know what is the oldest record.
> I have used SELECT TOP 1 * FROM <table> WHERE Time > May 12 1990...
> -and it works because I know the data in the table is newer than 1990 but
> is there a more intelligent way of doing it?
> (I also think it work because the primary key has an ascending sorting
> order).
> Thanks for Your help.
> Regards
> Kjell Arne Johansen
>|||Thank You for your examples.
The time is not unique. I will have to use a combination of time and two
other fields.
Regards
Kjell Arne
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> skrev i melding
news:%23OzlmXGWFHA.3540@.TK2MSFTNGP15.phx.gbl...
> Is the time stamp unique in your table?
> If so, use:
> SELECT TOP 1 * FROM <table> ORDER BY ts DESC
> Or
> SELECT * FROM T1
> WHERE ts = (SELECT MAX(ts) FROM T1)
> If it's not unique, use:
> SELECT TOP 1 * FROM <table> ORDER BY ts DESC, key DESC
> Or
> SELECT * FROM T1
> WHERE key =
> (SELECT MAX(key) FROM T1
> WHERE ts = (SELECT MAX(ts) FROM T1))
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
> news:E3jhe.10021$SL4.226180@.news4.e.nsc.no...
>|||Then order by all three columns, desc... and use Top 1
"Kjell Arne Johansen" wrote:

> Thank You for your examples.
> The time is not unique. I will have to use a combination of time and two
> other fields.
>
> Regards
> Kjell Arne
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> skrev i meldin
g
> news:%23OzlmXGWFHA.3540@.TK2MSFTNGP15.phx.gbl...
>
>

How do I dynamically change the "TOP X" portion of a SELECT

I'm sure I'm missing something. I am returning the TOP X number of customers by revenue and I'd like to change the number of records returned by passing a parameter but I keep getting an error.

@.TopX int ( or varchar)

SELECT @.TopX CompanyName, Amount FROM Sales Where....

Why will this not work?

Only works in SQL Server 2005 or SQL Express:

SELECT TOP (@.Topx) ...

|||

Only works in SQL Server 2005 or SQL Express:

SELECT TOP (@.Topx) ...

|||

DECLARE @.stmtvarchar(500)DECLARE @.top varchar(5)SET @.top ='10'SET @.stmt ='SELECT TOP ' + @.top +' * FROM [Products]'EXEC(@.stmt)

You can dynamically create a sql statement:|||

Upgraded to 2005 and that worked perfectly...

Thanks!

How do I do difference in this case?

I have a customers table that looks like this: custid, name, city
I have a temp table that looks like this: name, city
I want to find all the records in the temp table that do not exists in the
customers table.
There are no nulls or duplicate name/city fields in either table.
If I had a custid field in the temp table I could do a simple outer join and
look for the NULL customer table values to find the temp records that did
not exist in the customers table.
But I can't get this to work matching on the name/city fields.
Can someone help me out with this?SELECT * FROM TempTable AS a
WHERE NOT EXISTS (SELECT * FROM RealTable AS b WHERE b.Name = a.name and
b.city = a.city)
Andrew J. Kelly SQL MVP
"Dave" <dave@.nospam.ru> wrote in message
news:%2308WBS2JFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I have a customers table that looks like this: custid, name, city
> I have a temp table that looks like this: name, city
> I want to find all the records in the temp table that do not exists in the
> customers table.
> There are no nulls or duplicate name/city fields in either table.
> If I had a custid field in the temp table I could do a simple outer join
> and
> look for the NULL customer table values to find the temp records that did
> not exist in the customers table.
> But I can't get this to work matching on the name/city fields.
> Can someone help me out with this?
>

How do I do difference in this case?

I have a customers table that looks like this: custid, name, city
I have a temp table that looks like this: name, city
I want to find all the records in the temp table that do not exists in the
customers table.
There are no nulls or duplicate name/city fields in either table.
If I had a custid field in the temp table I could do a simple outer join and
look for the NULL customer table values to find the temp records that did
not exist in the customers table.
But I can't get this to work matching on the name/city fields.
Can someone help me out with this?SELECT * FROM TempTable AS a
WHERE NOT EXISTS (SELECT * FROM RealTable AS b WHERE b.Name = a.name and
b.city = a.city)
Andrew J. Kelly SQL MVP
"Dave" <dave@.nospam.ru> wrote in message
news:%2308WBS2JFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I have a customers table that looks like this: custid, name, city
> I have a temp table that looks like this: name, city
> I want to find all the records in the temp table that do not exists in the
> customers table.
> There are no nulls or duplicate name/city fields in either table.
> If I had a custid field in the temp table I could do a simple outer join
> and
> look for the NULL customer table values to find the temp records that did
> not exist in the customers table.
> But I can't get this to work matching on the name/city fields.
> Can someone help me out with this?
>

How do I do difference in this case?

I have a customers table that looks like this: custid, name, city
I have a temp table that looks like this: name, city
I want to find all the records in the temp table that do not exists in the
customers table.
There are no nulls or duplicate name/city fields in either table.
If I had a custid field in the temp table I could do a simple outer join and
look for the NULL customer table values to find the temp records that did
not exist in the customers table.
But I can't get this to work matching on the name/city fields.
Can someone help me out with this?
SELECT * FROM TempTable AS a
WHERE NOT EXISTS (SELECT * FROM RealTable AS b WHERE b.Name = a.name and
b.city = a.city)
Andrew J. Kelly SQL MVP
"Dave" <dave@.nospam.ru> wrote in message
news:%2308WBS2JFHA.3992@.TK2MSFTNGP15.phx.gbl...
>I have a customers table that looks like this: custid, name, city
> I have a temp table that looks like this: name, city
> I want to find all the records in the temp table that do not exists in the
> customers table.
> There are no nulls or duplicate name/city fields in either table.
> If I had a custid field in the temp table I could do a simple outer join
> and
> look for the NULL customer table values to find the temp records that did
> not exist in the customers table.
> But I can't get this to work matching on the name/city fields.
> Can someone help me out with this?
>

How do I display a count?

I want to display the number of records for each state in a database. I'm using a strongly types dataset. In my method, I have the following

SELECT COUNT(ID) AS iTotal, STATE
FROM members
WHERE (Suspend = 0)
GROUP BY STATE
ORDER BY STATE

In the code behind of my page I have

Dim mateAdapter As New WAPTableAdapters.membersTableAdapter
Dim mates As WAP.membersDataTable
Dim mate As WAP.membersRow

mates = mateAdapter.GetDataState

For Each mate In mates
Select Case mate.STATE
Case "AK"
LabelAK.Text = mate.ID.
End Select
Next

What shouldLabelAK.Text = mate.ID. be for me to be able to display the number of records that have 'AK' in the state field?

Diane

I think if you use

LabelAK.Text = mate.iTotal

you'll get what you're looking for.

|||

That's what i thought, but it doesn't like it.

Description:Anerror occurred during the compilation of a resource required to servicethis request. Please review the following specific error details andmodify your source code appropriately.

Compiler Error Message:BC30456: 'iTotal' is not a member of 'WAP.membersRow'.

But it is in the dataset, and i can get a preview of the data in the dataset. But it doesn't like it in the page.

Diane

|||

Try mate.iTotal.ToString

I had some state and zipcode data around so I put this together.

webform:

<%@. Page Language="VB" AutoEventWireup="false" CodeFile="Test.aspx.vb" Inherits="Test" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:Label id="LabelMO" runat="server" Text="Label"></asp:Label> </div> </form></body></html>

code behind:

Imports WAPTableAdaptersPartialClass TestInherits System.Web.UI.PageProtected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs) _Handles Me.LoadDim mateAdapterAs New ZipcodesTableAdapterDim matesAs WAP.ZipcodesDataTableDim mateAs WAP.ZipcodesRow mates = mateAdapter.GetDataStateFor Each mateIn matesSelect Case mate.StateCase"MO" LabelMO.Text = mate.iTotal.ToStringEnd Select Next End SubEnd Class

GetDataStat method:

selectcount(Zipcode)as iTotal, Statefrom ZipcodesGroup by StateOrder by State

|||

I have exactly what you do, and I'm getting the error that iTotal is not a member of the row. There must be something I'm overlooking that I've got wrong. But when i compare mine with yours, I don't see a difference.

Diane

|||

That is very odd, I know how you feel though. Sometimes what seems like it should be so easy is infuriatingly difficult.

Post your code so I can play around with it, or you can send it to me at eterry28 @. gmail . com

|||

Thank youeterry28

I did post my code in the first post.

Diane

|||

I resolved my problem by returning the state field in all rows and changing my code to Inmates.Compute("count(STATE)", "STATE='VT'").ToString

Thanks for your help

Diane