How do I loop through dataset then replace?

How would I loop through the rows of a dataset, then replace certain character in a certain column?

I have a database which has a date field formatted 23/08/2007, I wish to loop through the dataset containing the results from the dataset, change the format of the date to 23.08.2007 then store the value back into the dataSet, which is called 'dataSet', and the table is called 'News'.

Using C# by the way.

Thanks in advance

I suggest you to do this in database level though stored procedure that is dynamic (receive your changing criteria via input parameters).
Looping in stored procedures can be done though cursor (I know it is not recomended to use cursor in database for performance issue, but I think in this case it will be much better than looping into your application).

Tip:
You can get many date format for your DATETIME column in database. For example try: Convert(varchar(25), MyDateTimeColumn,113) or Convert(varchar(25), MyDateTimeColumn,111) [Used SQL Books Online for more samples and examples].

Good luck.

|||

Thanks

I'm new to this, so I'll read up on stored procedures, infact, I'm sure I have a whole book on SQL somewhere.

|||

While I read up on stored procedures can someone tell me how to do this in the application? The dataset will only contain 3 rows so it shouldnt have too much of an effect on the application.

|||

If it is just 3 records, then it will not affect the performance that much!

Here is the idea:
Let yourSqlDataAdapterfillyourDataSet.
Now, you have the data in the DataSet, loop into those records in DataSet.
Use String.Replace for replacing issue you have.

http://msdn2.microsoft.com/en-us/library/system.string.replace(VS.71).aspx

Good luck.

|||

What I wanted to do was get data from a database in date order, then alter the format of the date once it was in the dataset.

Instead of faffing about with it, I simply set the date in the database twice, i.e. Date, then a display date.

Now the data is sorted using the Date, but it's the displayDate field which displays the date to the user.

Thanks for all the help

How do i limit number of rows in a table based on dates?

Hi,
My application is written in c# and uses sql Express to store
information relating to what time employees clock in/out of work each
day.
How can i restrict the amount of rows in my attendance database table
so that entries older than 3 months are automatically deleted from the
database?
Thanks in advance!
KHi
I'd create a stored procedure that does deletion and then create a
job(weekly...) that will run this SP
<kls.systems@.btinternet.com> wrote in message
news:1176719432.941536.219550@.o5g2000hsb.googlegroups.com...
> Hi,
> My application is written in c# and uses sql Express to store
> information relating to what time employees clock in/out of work each
> day.
> How can i restrict the amount of rows in my attendance database table
> so that entries older than 3 months are automatically deleted from the
> database?
> Thanks in advance!
> K
>|||Thanks for the help uri, I thought that sql express couldnt use
scheduled jobs though as it doesnt use sql agent?
Thanks again.|||Ah I see, did not read properly your post
Perhaps here you will find some info
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
<kls.systems@.btinternet.com> wrote in message
news:1176721669.824203.42940@.l77g2000hsb.googlegroups.com...
> Thanks for the help uri, I thought that sql express couldnt use
> scheduled jobs though as it doesnt use sql agent?
> Thanks again.
>|||How about using a .sql script file, execute it using SQLCMD (in a .bat file) and use the Windows
scheduler to execute that .bat file?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<kls.systems@.btinternet.com> wrote in message
news:1176721669.824203.42940@.l77g2000hsb.googlegroups.com...
> Thanks for the help uri, I thought that sql express couldnt use
> scheduled jobs though as it doesnt use sql agent?
> Thanks again.
>|||On 16 Apr, 12:22, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ah I see, did not read properly your post
> Perhaps here you will find some info
> Automating Database maintenance in SQL 2005 Express Edition Part Ihttp://www.sqldbatips.com/showarticle.asp?ID=27
> Automating Database maintenance in SQL 2005 Express Edition Part IIhttp://www.sqldbatips.com/showarticle.asp?ID=29
> <kls.syst...@.btinternet.com> wrote in message
> news:1176721669.824203.42940@.l77g2000hsb.googlegroups.com...
>
> > Thanks for the help uri, I thought that sql express couldnt use
> > scheduled jobs though as it doesnt use sql agent?
> > Thanks again.- Hide quoted text -
> - Show quoted text -|||Thanks very much for all of your help, I'll try setting up a windows
scheduled job to run an sql script to do this as suggested in the
articles you provided links for.
I just thought there would have been a quick and easy setting within
management studio express i could configure to do it but nevermind!
Thanks very much again for your help Uri, very much appreciated.

how do I keep a table from shifting items below the table

I have a table on a report that will have the max of 6 rows and below that I
need to have other information on the report to print onto fields on a form,
when the rows change in the table the textboxes below get shifted. How do I
stop this from happening?
Thanks,
JimMake your textboxes as large as they should grow and turn off 'CanGrow'.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Ciotuszynski" <jimcio@.hotmail.com> wrote in message
news:uu2Kr%237WEHA.1128@.TK2MSFTNGP10.phx.gbl...
>I have a table on a report that will have the max of 6 rows and below that
>I
> need to have other information on the report to print onto fields on a
> form,
> when the rows change in the table the textboxes below get shifted. How do
> I
> stop this from happening?
> Thanks,
> Jim
>

How do I join two tables to get a row count?

I have two tables: Thread and Reply and they both have a field called UserID

I need to know the number of rows in both tables where UserID="Chris"

I can do this with two stored procedures and add the results together:

SELECT COUNT(*) FROM Thread WHERE Thread.UserID='Chris'

SELECT COUNT(*) FROM Reply WHERE Reply.UserID='Chris'

but there must be a better way. Can this be written as one stored procedure with some sort of join?

Thanks, Chris

Try something like this:

select
(select count(user_id) from table1 where userID = 'blah' )
+ (select count(user_id) from table2 where userID = 'blah' )
as totalCount

|||

you can do one stored proc, get the counts into 2 different variables and send them out as OUTPUT parameters, so you get all the data in 1 trip.

You can also do it in one query as follows:

SELECT COUNT(*) as Count1, (SELECT COUNT(*) FROM Reply WHERE Reply.UserID='Chris') as Count2

FROM Thread WHERE Thread.UserID='Chris'

|||

Darrell,

That worked great. Thanks,

Chris

How do I insert values into CE from a DataGridView row/rows?

Hi all... can someone tell me why this does not work and how to make it work?

SELECT SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM,
PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id,
create_date
FROM ProposalDetail
INSERT INTO temp
(
SQLKey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList,
PropDCost, PropDGMDollar, PropDGM, PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus,
create_timestamp, update_timestamp,update_originator_id, create_date
)
VALUES
(
'1', '3/20/2007 8:13:10 AM', 'SomeUser', 'b1', '4', '0761046', 'APP', 'Miscellaneous Options',
'54.000', '27.00', '27.00', '50.00', '54.00', '54.00', '54.00', '1', 'Active', '3/20/2007 8:13:10 AM', '3/20/2007 8:13:10 AM', '0',
'3/20/2007 8:13:10 AM'
)
WHERE (PropDNumb = 'b1') AND (PropDRevNumb = '1')

Thanks a ton!

figured it out and oddly enough this is solid and works great...

'SQL STATEMENT......

Dim conGridInsert As New SqlCeCommand

conGridInsert.CommandText = "INSERT INTO ProposalDetail" & _

"(SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, " & _

"PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id, " & _

"create_date)" & _

"SELECT SQLkey, CreateDateTime, Alias, PropDNumb, '" & ComboRevision.Text & "' as PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, " & _

"PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id, " & _

"create_date" & _

" " & _

" FROM ProposalDetail ProposalDetail_1 " & _

" " & _

" WHERE (PropDNumb = '" & txtProposalNum.Text & "') AND (PropDRevNumb = '" & OldRevNumber & "')"

conGridInsert.Connection = CnGridInsert

CnGridInsert.Open()

conGridInsert.ExecuteNonQuery()

CnGridInsert.Close()

How do i getting many Rows from a single XML column?

using SQL 2005...

I have a table called training. It has 2 columns ID and data.
The ID column is a INT.
The data column is an XML data type that contains XML looking like the following:
<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005<ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk<ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat<ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>

if we asume that the table has 3 rows with the ID field being 1-3 and all the XML is the same as above.
I'm trying create an SQL statement that outputs the following results

id	ProgramName	UrgencyLevel
1	Learn SQL 2005	3
1	Learn To Walk	1
1	Learn To Eat	1
2	Learn SQL 2005	3
2	Learn To Walk	1
2	Learn To Eat	1
3	Learn SQL 2005	3
3	Learn To Walk	1
3	Learn To Eat	1

please can some one help me?

You will need to use the nodes() to break your xml into multiple rows then CROSS APPLY to that. For example:

Dan

CREATE TABLE #t1(
id int,
data xml
)

INSERT INTO #t1 SELECT 1, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

INSERT INTO #t1 SELECT 2, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

INSERT INTO #t1 SELECT 2, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

SELECT ID, t.c.value('ProgramName[1]', 'NVARCHAR(MAX)') + ' ' + t.c.value('UrgencyLevel[1]', 'NVARCHAR(MAX)')
FROM #t1
CROSS APPLY
#t1.data.nodes('TrainingRequests/TrainingRequest') AS t(c)

|||Thank you, you saved me ripping out the rest of my hair!!!

How do I get unique rows based on studentID

I am a beginner at SQL so thanks ahead of time....

How do I get unique rows based on studentID? Distinct and group by don't seem to work

DESIRED RESULTS

StudentID First Name Last Name Other Columns....................................
634565491 MARINA BALDERAZ
640484566 TERE BALDERAZ

CURRENT SQL AND RESULTS....

SELECT ClassRosterRecID, StudentDataRecID, StudentDataKey, StudentID, FirstName, LastName, CurrentGrade, Gender, Ethnicity, EconDisadvantaged, TitleI, Migrant, LEP, Bilingual, ESL, SpecialEducation, GiftedTalented, AtRisk, CareerTech, Dyslexia, LastName + ', ' + FirstName AS LastNameFirstName, EconDisadvantagedSort, TitleISort, MigrantSort, LEPSort, BilingualSort, ESLSort, SpecialEducationSort, GiftedTalentedSort, AtRiskSort, CareerTechSort, DyslexiaSort, DistrictID, CampusID FROM vClassDemographicsDetail WHERE (DistrictID = '057910') AND (CampusID = '057910101') AND (LastName LIKE '%BALDERAZ%')

StudentID First Name Last Name Other Columns....................................
634565491 MARINA BALDERAZ
634565491 MARINA BALDERAZ
634565491 MARINA BALDERAZ
640484566 TERE BALDERAZ
640484566 TERE BALDERAZ
640484566 TERE BALDERAZ

craigbtx:

How do I get unique rows based on studentID? Distinct and group by don't seem to work

GROUP BY should work, just remember to add some aggregation function on the columns not in the GROUP BY list. Try something like:

SELECT max(ClassRosterRecID), max(StudentDataRecID), max(StudentDataKey), StudentID, FirstName,
LastName, max(CurrentGrade), max(Gender), max(Ethnicity), max(EconDisadvantaged), max(TitleI), max(Migrant), max(LEP), max(Bilingual), max(ESL), max(SpecialEducation), max(GiftedTalented), max(AtRisk), max(CareerTech), max(Dyslexia), LastName + ', ' + FirstName AS LastNameFirstName, max(EconDisadvantagedSort), max(TitleISort), max(MigrantSort), max(LEPSort), max(BilingualSort), max(ESLSort), max(SpecialEducationSort), max(GiftedTalentedSort), max(AtRiskSort), max(CareerTechSort), max(DyslexiaSort), max(DistrictID), max(CampusID )
FROM vClassDemographicsDetail
WHERE (DistrictID = '057910') AND (CampusID = '057910101') AND (LastName LIKE '%BALDERAZ%')
GROUP BY StudentID, [FirstName],[LastName]

How do I get the row number?

I have tryed to find a way to include the row number in a querry but without result.

I have a table select some rows ordered and now I will add a column called rank

How do I get the rownumber in the field rank ?

Thanks in advance

Jan OThe way I'd suggest assigning a row number to your resultset is to INSERT your resultset into a temporary table or table variable which has an IDENTITY column, something like this:

-- set up a table variable to hold the resultset
DECLARE @.myTable (Rank int identity(1,1) primary_key,
 ColumnA varchar(20),
 ColumnB varchar(20)
 )
-- insert the resultset into the table variable
INSERT INTO
 @.myTable
(
 ColumnA,
 ColumnB
)
SELECT
 ColumnA,
 ColumnB
FROM
 someTable
ORDER BY
 someCriteria
-- return the resultset to the calling program in rank order, including the rank column
SELECT
 Rank,
 ColumnA,
 ColumnB
FROM
 @.myTable
ORDER BY
 Rank

Terri|||Thanks,

Have hoped not to do it so, a lot of other calculation dependences.

Jan|||Well, an alternative (which performs poorly) would be at the very bottom of this link:Returning a Row Number in a Query It involves using a subquery for every row in your resultset and could be horrendous performance-wise.

Can you just assign the rank in the front end?

Terri|||I agree with Terri. If possible, assign in the front end. I have used the temp table solution before, as well. It really depends on why you need to have the rownumber. If it is just for display purposes, it should be no problem to create on the front end when binding your resultset.|||Thanks again.

I have started to solve the "problem" in the front end.
I have a datagrid with paging so I have a few lines left.
Got it working whitout paging , but I had prefered asystem rownr from SQL.

But you cant get everything for free :)

Thanks
Jan

How do I get the next or previous rows in a database

Hi
I will be greatful for help with the expression.
Example:
'I currently requested 50 rows from a database and now I want the next 50
rows.
I know that the combination of field A, B, C and D makes the row unique.
What should my where clause be?
Is there some other way of doing this?'
select top 50 * from some_table
where <what should this be?>
order by A desc, B desc, C desc, D desc
Regards
Kjell Arne Johansen
Hi
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> Hi
> I will be greatful for help with the expression.
> Example:
> 'I currently requested 50 rows from a database and now I want the next 50
> rows.
> I know that the combination of field A, B, C and D makes the row unique.
> What should my where clause be?
> Is there some other way of doing this?'
> select top 50 * from some_table
> where <what should this be?>
> order by A desc, B desc, C desc, D desc
> Regards
> Kjell Arne Johansen
|||> I know that the combination of field A, B, C and D makes the row unique.
> What should my where clause be?
Below is an example that should work as long as the columns do not allow
nulls.
SELECT TOP 50 *
FROM dbo.some_table
WHERE
A < @.LastA
OR (A = @.LastA AND B < @.LastB)
OR (A = @.LastA AND B = @.LastB AND C < @.LastC)
OR (A = @.LastA AND B = @.LastB AND C = @.LastC AND D < @.LastD)
ORDER BY
A DESC,
B DESC,
C DESC,
D DESC

> Is there some other way of doing this?'
Uri provided some other common techniques. In my experience, the key based
pagination method is the fastest and ought to scale linearly regardless of
table size with the appropriate indexes (e.g. conposite primary key or
unique constraint). The downside with key pagination is that indexing can
quickly become complex with user-defined sorting and/or selection criteria.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> Hi
> I will be greatful for help with the expression.
> Example:
> 'I currently requested 50 rows from a database and now I want the next 50
> rows.
> I know that the combination of field A, B, C and D makes the row unique.
> What should my where clause be?
> Is there some other way of doing this?'
> select top 50 * from some_table
> where <what should this be?>
> order by A desc, B desc, C desc, D desc
> Regards
> Kjell Arne Johansen
|||Thank you very much.
It looks to me that this I can use both on MS Access and SQL Server.
If I want to 'page up' then I only change from less than '<' to higher than
'>' I assume.
Thanks.
Kjell Arne Johansen
software engineer at Kongsberg Maritime as
"Dan Guzman" wrote:

> Below is an example that should work as long as the columns do not allow
> nulls.
> SELECT TOP 50 *
> FROM dbo.some_table
> WHERE
> A < @.LastA
> OR (A = @.LastA AND B < @.LastB)
> OR (A = @.LastA AND B = @.LastB AND C < @.LastC)
> OR (A = @.LastA AND B = @.LastB AND C = @.LastC AND D < @.LastD)
> ORDER BY
> A DESC,
> B DESC,
> C DESC,
> D DESC
>
> Uri provided some other common techniques. In my experience, the key based
> pagination method is the fastest and ought to scale linearly regardless of
> table size with the appropriate indexes (e.g. conposite primary key or
> unique constraint). The downside with key pagination is that indexing can
> quickly become complex with user-defined sorting and/or selection criteria.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
> message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
>
|||Thank you very much.
A lot of good examples here.
I forgot to say that I would very much like the solution to work on MS
Access also, so it must probably be some kind of sql expression. -or I have
to make two different solutions for MS Access and SQL Server.
Kjell Arne Johansen
software engineer at Kongsberg Maritime as
"Uri Dimant" wrote:

> Hi
> http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
> message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
>
>
|||> If I want to 'page up' then I only change from less than '<' to higher
> than
> '>' I assume.
In addition to reversing the operators, you'll need to reverse the ORDER BY
so that TOP returns the previous page rows. You will also need wrap this
query in a derived table so that you can order the previous page data in the
original sequence (DESC):
SELECT *
FROM (
SELECT TOP 50 *
FROM dbo.some_table
WHERE
A > @.LastA
OR (A = @.LastA AND B > @.LastB)
OR (A = @.LastA AND B = @.LastB AND C > @.LastC)
OR (A = @.LastA AND B = @.LastB AND C = @.LastC AND D > @.LastD)
ORDER BY
A ASC,
B ASC,
C ASC,
D ASC
) AS previous_page
ORDER BY
A DESC,
B DESC,
C DESC,
D DESC
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:D2F2F4A6-523F-4333-8DFE-FD4B67E08551@.microsoft.com...[vbcol=seagreen]
> Thank you very much.
> It looks to me that this I can use both on MS Access and SQL Server.
> If I want to 'page up' then I only change from less than '<' to higher
> than
> '>' I assume.
> Thanks.
> Kjell Arne Johansen
> software engineer at Kongsberg Maritime as
> "Dan Guzman" wrote:

How do I get the next or previous rows in a database

Hi
I will be greatful for help with the expression.
Example:
'I currently requested 50 rows from a database and now I want the next 50
rows.
I know that the combination of field A, B, C and D makes the row unique.
What should my where clause be?
Is there some other way of doing this?'
select top 50 * from some_table
where <what should this be?>
order by A desc, B desc, C desc, D desc
Regards
Kjell Arne JohansenHi
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> Hi
> I will be greatful for help with the expression.
> Example:
> 'I currently requested 50 rows from a database and now I want the next 50
> rows.
> I know that the combination of field A, B, C and D makes the row unique.
> What should my where clause be?
> Is there some other way of doing this?'
> select top 50 * from some_table
> where <what should this be?>
> order by A desc, B desc, C desc, D desc
> Regards
> Kjell Arne Johansen|||> I know that the combination of field A, B, C and D makes the row unique.
> What should my where clause be?
Below is an example that should work as long as the columns do not allow
nulls.
SELECT TOP 50 *
FROM dbo.some_table
WHERE
A < @.LastA
OR (A = @.LastA AND B < @.LastB)
OR (A = @.LastA AND B = @.LastB AND C < @.LastC)
OR (A = @.LastA AND B = @.LastB AND C = @.LastC AND D < @.LastD)
ORDER BY
A DESC,
B DESC,
C DESC,
D DESC
> Is there some other way of doing this?'
Uri provided some other common techniques. In my experience, the key based
pagination method is the fastest and ought to scale linearly regardless of
table size with the appropriate indexes (e.g. conposite primary key or
unique constraint). The downside with key pagination is that indexing can
quickly become complex with user-defined sorting and/or selection criteria.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> Hi
> I will be greatful for help with the expression.
> Example:
> 'I currently requested 50 rows from a database and now I want the next 50
> rows.
> I know that the combination of field A, B, C and D makes the row unique.
> What should my where clause be?
> Is there some other way of doing this?'
> select top 50 * from some_table
> where <what should this be?>
> order by A desc, B desc, C desc, D desc
> Regards
> Kjell Arne Johansen|||Thank you very much.
It looks to me that this I can use both on MS Access and SQL Server.
If I want to 'page up' then I only change from less than '<' to higher than
'>' I assume.
Thanks.
Kjell Arne Johansen
software engineer at Kongsberg Maritime as
"Dan Guzman" wrote:
> > I know that the combination of field A, B, C and D makes the row unique.
> > What should my where clause be?
> Below is an example that should work as long as the columns do not allow
> nulls.
> SELECT TOP 50 *
> FROM dbo.some_table
> WHERE
> A < @.LastA
> OR (A = @.LastA AND B < @.LastB)
> OR (A = @.LastA AND B = @.LastB AND C < @.LastC)
> OR (A = @.LastA AND B = @.LastB AND C = @.LastC AND D < @.LastD)
> ORDER BY
> A DESC,
> B DESC,
> C DESC,
> D DESC
> > Is there some other way of doing this?'
> Uri provided some other common techniques. In my experience, the key based
> pagination method is the fastest and ought to scale linearly regardless of
> table size with the appropriate indexes (e.g. conposite primary key or
> unique constraint). The downside with key pagination is that indexing can
> quickly become complex with user-defined sorting and/or selection criteria.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
> message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> > Hi
> >
> > I will be greatful for help with the expression.
> >
> > Example:
> > 'I currently requested 50 rows from a database and now I want the next 50
> > rows.
> > I know that the combination of field A, B, C and D makes the row unique.
> > What should my where clause be?
> > Is there some other way of doing this?'
> >
> > select top 50 * from some_table
> > where <what should this be?>
> > order by A desc, B desc, C desc, D desc
> >
> > Regards
> > Kjell Arne Johansen
>|||Thank you very much.
A lot of good examples here.
I forgot to say that I would very much like the solution to work on MS
Access also, so it must probably be some kind of sql expression. -or I have
to make two different solutions for MS Access and SQL Server.
Kjell Arne Johansen
software engineer at Kongsberg Maritime as
"Uri Dimant" wrote:
> Hi
> http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
> message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> > Hi
> >
> > I will be greatful for help with the expression.
> >
> > Example:
> > 'I currently requested 50 rows from a database and now I want the next 50
> > rows.
> > I know that the combination of field A, B, C and D makes the row unique.
> > What should my where clause be?
> > Is there some other way of doing this?'
> >
> > select top 50 * from some_table
> > where <what should this be?>
> > order by A desc, B desc, C desc, D desc
> >
> > Regards
> > Kjell Arne Johansen
>
>|||> If I want to 'page up' then I only change from less than '<' to higher
> than
> '>' I assume.
In addition to reversing the operators, you'll need to reverse the ORDER BY
so that TOP returns the previous page rows. You will also need wrap this
query in a derived table so that you can order the previous page data in the
original sequence (DESC):
SELECT *
FROM (
SELECT TOP 50 *
FROM dbo.some_table
WHERE
A > @.LastA
OR (A = @.LastA AND B > @.LastB)
OR (A = @.LastA AND B = @.LastB AND C > @.LastC)
OR (A = @.LastA AND B = @.LastB AND C = @.LastC AND D > @.LastD)
ORDER BY
A ASC,
B ASC,
C ASC,
D ASC
) AS previous_page
ORDER BY
A DESC,
B DESC,
C DESC,
D DESC
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:D2F2F4A6-523F-4333-8DFE-FD4B67E08551@.microsoft.com...
> Thank you very much.
> It looks to me that this I can use both on MS Access and SQL Server.
> If I want to 'page up' then I only change from less than '<' to higher
> than
> '>' I assume.
> Thanks.
> Kjell Arne Johansen
> software engineer at Kongsberg Maritime as
> "Dan Guzman" wrote:
>> > I know that the combination of field A, B, C and D makes the row
>> > unique.
>> > What should my where clause be?
>> Below is an example that should work as long as the columns do not allow
>> nulls.
>> SELECT TOP 50 *
>> FROM dbo.some_table
>> WHERE
>> A < @.LastA
>> OR (A = @.LastA AND B < @.LastB)
>> OR (A = @.LastA AND B = @.LastB AND C < @.LastC)
>> OR (A = @.LastA AND B = @.LastB AND C = @.LastC AND D < @.LastD)
>> ORDER BY
>> A DESC,
>> B DESC,
>> C DESC,
>> D DESC
>> > Is there some other way of doing this?'
>> Uri provided some other common techniques. In my experience, the key
>> based
>> pagination method is the fastest and ought to scale linearly regardless
>> of
>> table size with the appropriate indexes (e.g. conposite primary key or
>> unique constraint). The downside with key pagination is that indexing
>> can
>> quickly become complex with user-defined sorting and/or selection
>> criteria.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote
>> in
>> message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
>> > Hi
>> >
>> > I will be greatful for help with the expression.
>> >
>> > Example:
>> > 'I currently requested 50 rows from a database and now I want the next
>> > 50
>> > rows.
>> > I know that the combination of field A, B, C and D makes the row
>> > unique.
>> > What should my where clause be?
>> > Is there some other way of doing this?'
>> >
>> > select top 50 * from some_table
>> > where <what should this be?>
>> > order by A desc, B desc, C desc, D desc
>> >
>> > Regards
>> > Kjell Arne Johansen

how do I get a trigger just to copy the inserted row.

HI I have a trigger which I want to copy any rows in the MSmerge_history into a archieve table. I am using this trigger:

CREATE TRIGGER History_replication ON
[dbo].[MSmerge_history]
FOR INSERT
AS
INSERT [dbo].[MSmerge_history_archive]
(
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
)
SELECT
agent_id,
runstatus,
start_time,
[time] ,
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp],

How this copy the entire contence of the table into the archive each time someone insert a row. How do I get it to only insert the row which had triggered the insert? EdCREATE TRIGGER History_replication ON
[dbo].[MSmerge_history]
FOR INSERT
AS
INSERT [dbo].[MSmerge_history_archive]
(
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
)
SELECT
agent_id,
runstatus,
start_time,
[time] ,
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp]
FROM inserted

Inserted is virtual table used by triggers.|||Excellent thanks I've got that working now, Ed

How do I find out whether a PK is referenced by any FK?

I need to perform regular maintenance on a table who is referenced through F
K from many
other tables.
Instead of attempting to bluntly delete the rows and let SQL report RI error
s , I would
like to first check whether the row is currently being referenced without ha
ving to check
each referencing table.
How can I accomplish that?
thanks.Do you mean whether an FK exists at all, or whether any relevant data exists
in the FK tables? Note that if the FK is defined as nullable then the mere
presence of rows in the FK table does not mean that those rows depend on any
rows in the PK table.
"Gaetan" <someone@.somewhere.com> wrote in message
news:ten1n1h9al41tjats6o3lheo1i91rl40kg@.
4ax.com...
>I need to perform regular maintenance on a table who is referenced through
>FK from many
> other tables.
> Instead of attempting to bluntly delete the rows and let SQL report RI
> errors , I would
> like to first check whether the row is currently being referenced without
> having to check
> each referencing table.
> How can I accomplish that?
> thanks.|||The FK constraints are defined in the other tables. I want to determine whet
her any
relevant data exists in the referencing tables. NULL values are not allowed
in the FK
values.
On Tue, 8 Nov 2005 12:31:39 -0500, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Do you mean whether an FK exists at all, or whether any relevant data exist
s
>in the FK tables? Note that if the FK is defined as nullable then the mere
>presence of rows in the FK table does not mean that those rows depend on an
y
>rows in the PK table.
>
>
>"Gaetan" <someone@.somewhere.com> wrote in message
> news:ten1n1h9al41tjats6o3lheo1i91rl40kg@.
4ax.com...
>|||Hello, Gaetan
If your primary key has is composed of a single column and the number
of referencing tables is not too big (less than 20...30 tables), you
can try to dynamically construct an SQL statement to check that tables,
based on the informations from the system tables (or the
information_schema views, if you preffer).
The code might be something like this:
DECLARE @.TableName sysname
DECLARE @.PKColumn sysname
DECLARE @.Condition nvarchar(100)
DECLARE @.SQL nvarchar(4000)
SET @.TableName = 'SomeTable'
SET @.Condition = 'SomeColumn=123'
SELECT @.SQL=ISNULL(@.SQL+CHAR(13)+'UNION ALL'+CHAR(13),'')
+'SELECT '''+ft.name+''' WHERE EXISTS ('
+'SELECT * FROM '+QUOTENAME(ft.name)
+' WHERE '+QUOTENAME(fc.name)+' IN ('
+'SELECT '+QUOTENAME(pc.name)
+' FROM '+QUOTENAME(@.TableName)
+ISNULL('WHERE '+@.Condition,'')+'))'
FROM sysreferences r
INNER JOIN sysobjects fk ON r.constid=fk.id
INNER JOIN sysobjects ft ON r.fkeyid=ft.id
INNER JOIN sysobjects pt ON r.rkeyid=pt.id
INNER JOIN syscolumns fc ON r.fkeyid=fc.id AND r.fkey1=fc.colid
INNER JOIN syscolumns pc ON r.rkeyid=pc.id AND r.rkey1=pc.colid
WHERE fk.xtype='F' AND pt.name=@.TableName
PRINT @.SQL
EXEC(@.SQL)
IF @.@.ROWCOUNT>0 BEGIN
PRINT 'There are some rows in other tables referencing our row(s)'
END
Be carefull what you put in the @.Condition variable, because this code
is vulnerable to "SQL Injection" attacks. Also, the maximum length of a
nvarchar string (4000 characters) may be easily exceeded if you have
many foreign keys for referencing your table.
Razvan|||On Tue, 08 Nov 2005 17:23:04 GMT, Gaetan wrote:

>I need to perform regular maintenance on a table who is referenced through
FK from many
>other tables.
>Instead of attempting to bluntly delete the rows and let SQL report RI erro
rs , I would
>like to first check whether the row is currently being referenced without h
aving to check
>each referencing table.
>How can I accomplish that?
Hi Gaetan,
You can't. The only way to know if a row is referenced or not is to
check all referencing tables.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Wonderful,
This is exactly what I was looking for. I will have less than 15 tables refe
rencing a main
table so there is no problem with the string size. Injection will not proble
m since there
will be no user input.
Thanks again Razvan.
On 8 Nov 2005 12:57:16 -0800, "Razvan Socol" <rsocol@.gmail.com> wrote:

>Hello, Gaetan
>If your primary key has is composed of a single column and the number
>of referencing tables is not too big (less than 20...30 tables), you
>can try to dynamically construct an SQL statement to check that tables,
>based on the informations from the system tables (or the
>information_schema views, if you preffer).
>The code might be something like this:
>DECLARE @.TableName sysname
>DECLARE @.PKColumn sysname
>DECLARE @.Condition nvarchar(100)
>DECLARE @.SQL nvarchar(4000)
>SET @.TableName = 'SomeTable'
>SET @.Condition = 'SomeColumn=123'
>SELECT @.SQL=ISNULL(@.SQL+CHAR(13)+'UNION ALL'+CHAR(13),'')
> +'SELECT '''+ft.name+''' WHERE EXISTS ('
> +'SELECT * FROM '+QUOTENAME(ft.name)
> +' WHERE '+QUOTENAME(fc.name)+' IN ('
> +'SELECT '+QUOTENAME(pc.name)
> +' FROM '+QUOTENAME(@.TableName)
> +ISNULL('WHERE '+@.Condition,'')+'))'
>FROM sysreferences r
>INNER JOIN sysobjects fk ON r.constid=fk.id
>INNER JOIN sysobjects ft ON r.fkeyid=ft.id
>INNER JOIN sysobjects pt ON r.rkeyid=pt.id
>INNER JOIN syscolumns fc ON r.fkeyid=fc.id AND r.fkey1=fc.colid
>INNER JOIN syscolumns pc ON r.rkeyid=pc.id AND r.rkey1=pc.colid
>WHERE fk.xtype='F' AND pt.name=@.TableName
>PRINT @.SQL
>EXEC(@.SQL)
>IF @.@.ROWCOUNT>0 BEGIN
> PRINT 'There are some rows in other tables referencing our row(s)'
>END
>Be carefull what you put in the @.Condition variable, because this code
>is vulnerable to "SQL Injection" attacks. Also, the maximum length of a
>nvarchar string (4000 characters) may be easily exceeded if you have
>many foreign keys for referencing your table.
>Razvan|||Understood.
Performance wise, I wonder whether if it is preferable to attempt to delete
a referenced
row and let SQL report an error and handle it in the code or run SQL stateme
nt(s) to scan
each referencing tables!
Do you have an idea which approach is faster? The majority of the cases I wi
ll have less
than 5 referencing tables.
Regards, Gaetan.
On Tue, 08 Nov 2005 22:16:20 +0100, Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_
fo> wrote:

>On Tue, 08 Nov 2005 17:23:04 GMT, Gaetan wrote:
>
>Hi Gaetan,
>You can't. The only way to know if a row is referenced or not is to
>check all referencing tables.
>Best, Hugo|||> Performance wise, I wonder whether if it is preferable to attempt to
> delete a referenced
> row and let SQL report an error and handle it in the code or run SQL
> statement(s) to scan
> each referencing tables!
> Do you have an idea which approach is faster? The majority of the cases I
> will have less
> than 5 referencing tables.
Well, what is the end game here?
I'm not sure I understand the purpose. If there are no referencing rows,
allow them to just delete everything from the PK table. If there are
referencing rows, then what?
A|||On Tue, 08 Nov 2005 21:48:01 GMT, Gaetan wrote:

>Understood.
>Performance wise, I wonder whether if it is preferable to attempt to delete
a referenced
>row and let SQL report an error and handle it in the code or run SQL statem
ent(s) to scan
>each referencing tables!
>Do you have an idea which approach is faster? The majority of the cases I w
ill have less
>than 5 referencing tables.
>Regards, Gaetan.
Hi Gaetan,
That depends.
If most cases will not be referenced, deleting without checking will be
fastest. If you do check, you're duplicating the work (since SQL Server
will have to re-run the check as part of the DELETE command).
On the other hand, if many cases will fail, checking first might be
faster. First, because you can control the order in which tables are
checked (making sure the table most likely to be the offender is checked
first). And second, because you eliminate the need for a ROLLBACK
operation (which is often slower than the original data manipulation).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 8 Nov 2005 16:57:41 -0500, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Well, what is the end game here?
>I'm not sure I understand the purpose. If there are no referencing rows,
>allow them to just delete everything from the PK table. If there are
>referencing rows, then what?
>A
>
The goal is simple; I have a core table that need to be refreshed at some in
tervals. That
core table is refreshed (insert / update / delete) from data elements locate
d somewhere
else.
Inserts and updates of the core table are easy ... just do it. However, for
the deletes,
other tables may still be referencing rows of the core table. Performance is
not
imperative but since I have yet to write the code, I would rather do it the
way that makes
most sense without going heels over head, hence the original question; Shoul
d I just
delete the core table row and catch the RI errors or scan the referencing ta
bles and skip
the delete operation when a core table row is still being referenced. I must
not delete
the row when it is still referenced ... I have to wait until yet another pro
cess deletes
the rows from the referencing tables.
Gaetan.

How do I extract data from selected rows from my excel source file?

The columns in my excel source contain data of different types with the column name being a string and the data in those columns being integers. Is there any way to only extract numeric data , in short I want column names to be omitted. Also the data is distributed unevenly , beggining at various rows in each column.

Thanking in advance :)

You can try to create a named range in the Excel sheet and then import only the range.

HTH.

|||Is there any other way to do it , like writing a query or so? I need help as i'm new to SSIS , any help would be appreciated