Getting @@ROWCOUNT from BULK INSERT with BATCHSIZE set

Hello!
I have a stored procedure that takes a file name as a parameter and
imports the data from there into the appropriate table using the
correct format file. I would like to save the number of rows inserted
at the end of my stored procedure. Since the file name is variable I
use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
generated.
Since some of these files are extremely large I have set the BATCHSIZE
for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
show the number of rows inserted in the last batch. Since the table
name is variable I don't have an easy way of getting the count(*) from
it.
Any suggestions?
Thanks!
-Tom.Aardvark (tom_hummel@.hotmail.com) writes:
> I have a stored procedure that takes a file name as a parameter and
> imports the data from there into the appropriate table using the
> correct format file. I would like to save the number of rows inserted
> at the end of my stored procedure. Since the file name is variable I
> use EXEC (@.cmd) where the @.cmd is a BULK INSERT command that I have
> generated.
> Since some of these files are extremely large I have set the BATCHSIZE
> for BULK INSERT to 1,000,000. However, this causes @.@.ROWCOUNT to only
> show the number of rows inserted in the last batch. Since the table
> name is variable I don't have an easy way of getting the count(*) from
> it.
> Any suggestions?
You could run SELKCT COUNT(*) on the table before and after, but that's
of course ont very appealing on a large table. (The fact that the
table is dynamic should not be a problem. You do know sp_executesql,
don't you? Else read
http://www.sommarskog.se/dynamic_sq...#sp_executesql.
The other option would be to have the client to catch those rows
affected messages for each batch committed.
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 wrote:
> You could run SELKCT COUNT(*) on the table before and after, but that's
> of course ont very appealing on a large table. (The fact that the
> table is dynamic should not be a problem. You do know sp_executesql,
> don't you? Else read
> http://www.sommarskog.se/dynamic_sq...#sp_executesql.
> The other option would be to have the client to catch those rows
> affected messages for each batch committed.
Thanks for the advice Erland. I had forgotten that sp_executesql
allowed for output parameters, so that should give me what I need using
SELECT COUNT(*). Not the most elegant way and it will suffer a
performance hit since some of the tables can be quite large, but it's a
process that runs for up to 12 hours once a month, so a couple minutes
to get a count from a table shouldn't be a problem.
Thanks!
-Tom.

Get top 20 from aggregated records??

Hello
I'm using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows NT 5.0 (Build 2195: Service Pack 4)
I have a big problem getting the right result from the this example
table...
AgentID | Destination
----
10 | BKK
10 | BKK
10 | BKK
10 | LON
10 | BEG
10 | BEG
10 | SJJ
10 | NYC
96 | BKK
96 | BKK
96 | BKK
96 | BKK
96 | BKK
96 | LON
96 | LON
96 | LON
96 | LON
96 | BEG
96 | BEG
96 | BEG
96 | SJJ
96 | SJJ
96 | MRU
96 | MRU
1000 | BKK
1000 | BKK
1000 | BKK
1000 | LON
1000 | BEG
1000 | HAN
1000 | HKG
1000 | ZAG
1000 | BLX
The result I need to get out of this table is top 5 destinations for
every agent id used here. Can someone please help with this?
Thx guys.
Miroslav Ostojicyou mean top 5 distinct destinations?
or just top 5. If just top5, then in what order?|||If you are searching for distinct destination, then you can do it this way..
select a.agentid, a.Destination, count(distinct b.Destination)
as con from tbl1 a, tbl1 b
where a.agentid = b.agentid and a.Destination <= b.Destination
group by a.agentid, a.Destination
having count(distinct b.destination) <= 5
order by a.agentid|||You left everyone guessing about a lot of things, particularly what
you are ranking on to determine what is top.
My guess is that this query
SELECT AgentID, Destination, count(*) as rows
FROM Example
GROUP BY AgentID, Destination
ORDER BY 1, 3 desc
which counts how many occurances of each Destination there are for an
AgentID, might be the basis for the ranking. If that is correct you
might see what this does for you:
SELECT AgentID, Destination, count(*) as rows
FROM Example
WHERE Destination IN
(SELECT TOP 5 Destination
FROM Example as X2
WHERE X1.AgentID = X2.AgentID
GROUP BY Destination
ORDER BY count(*) desc)
GROUP BY AgentID, Destination
ORDER BY AgentID, count(*) desc
Roy Harvey
Beacon Falls, CT
On 7 Jun 2006 02:02:06 -0700, miroslav.ostojic@.gmail.com wrote:

>Hello
>I'm using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
>14:22:05
>Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
>Windows NT 5.0 (Build 2195: Service Pack 4)
>I have a big problem getting the right result from the this example
>table...
> AgentID | Destination
>----
> 10 | BKK
> 10 | BKK
> 10 | BKK
> 10 | LON
> 10 | BEG
> 10 | BEG
> 10 | SJJ
> 10 | NYC
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | BEG
> 96 | BEG
> 96 | BEG
> 96 | SJJ
> 96 | SJJ
> 96 | MRU
> 96 | MRU
> 1000 | BKK
> 1000 | BKK
> 1000 | BKK
> 1000 | LON
> 1000 | BEG
> 1000 | HAN
> 1000 | HKG
> 1000 | ZAG
> 1000 | BLX
>
>The result I need to get out of this table is top 5 destinations for
>every agent id used here. Can someone please help with this?
>Thx guys.
>Miroslav Ostojic|||There is no field available in your table to order by.
You need to have some field by which we can sort it.
"miroslav.ostojic@.gmail.com" wrote:

> Hello
> I'm using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
> Windows NT 5.0 (Build 2195: Service Pack 4)
> I have a big problem getting the right result from the this example
> table...
> AgentID | Destination
> ----
> 10 | BKK
> 10 | BKK
> 10 | BKK
> 10 | LON
> 10 | BEG
> 10 | BEG
> 10 | SJJ
> 10 | NYC
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | BEG
> 96 | BEG
> 96 | BEG
> 96 | SJJ
> 96 | SJJ
> 96 | MRU
> 96 | MRU
> 1000 | BKK
> 1000 | BKK
> 1000 | BKK
> 1000 | LON
> 1000 | BEG
> 1000 | HAN
> 1000 | HKG
> 1000 | ZAG
> 1000 | BLX
>
> The result I need to get out of this table is top 5 destinations for
> every agent id used here. Can someone please help with this?
> Thx guys.
> Miroslav Ostojic
>

get string

hello!

I have a code folow:

public

string GetLink()

{

string ID=Request.QueryString["mabaihat"].ToString();

stringget;

SqlConnection con =newSqlConnection(strconnection);

DataSet ds =newDataSet();

con.Open();

SqlCommand

cmd =newSqlCommand("select link from song where songid="+ID+" ",con);

//

how can I get string of comlumn link?

//

con.Colse();

return get;//string link

}

Hi try this changed code

string ID = Request.QueryString["mabaihat"].ToString();string get;SqlConnection con =newSqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);DataSet ds =newDataSet();

con.Open();

SqlCommand cmd =newSqlCommand("select link from song where songid=" + ID , con);object obj = cmd.ExecuteScalar();if (Convert.IsDBNull(obj) || obj==null)

get =

"";else

get = obj.ToString();

con.Close();

return get;

I have retrived the link through execute scalar method.This will solve your problem

|||thank you very much. i'm will try

Get rows for latest date

Hello!

I have a table something like this:

ID INTEGER

Info VARCHAR (actually several columns but that is not important here)

DAT DateTime

For each ID there are several dates and for each of these dates there are several rows with different info. I would like to select the latest info for each ID. For example:

ID - DAT - Info

1 - 2007-02-01 - Info1

1 - 2007-02-01 - Info2

1 - 2006-02-01 - Info3

2 - 2007-05-05 - Info4

2 - 2007-02-01 - Info5

2 - 2006-02-01 - Info6

I would like to get:

Info1

Info2

Info4

This has to be done in one Query. Can anybody help me?

Here it is,

Code Block

Create Table #data (

[ID] int ,

[DAT] datetime ,

[Info] Varchar(100)

);

Insert Into #data Values('1','2007-02-01','Info1');

Insert Into #data Values('1','2007-02-01','Info2');

Insert Into #data Values('1','2006-02-01','Info3');

Insert Into #data Values('2','2007-05-05','Info4');

Insert Into #data Values('2','2007-02-01','Info5');

Insert Into #data Values('2','2006-02-01','Info6');

select main.info from #data main

join (select ID,max(dat) dat from #data group by ID) as latest

on latest.ID=main.ID and latest.dat=main.dat

|||

A couple of options:

Code Block

create table testdata

(ID int, Dat DATETIME, Nm CHAR(5))

INSERT INTO testdata

SELECT 1, '1 feb 2007', 'Info1'

UNION ALL

SELECT 1, '1 feb 2007', 'Info2'

UNION ALL

SELECT 1, '1 feb 2006', 'Info3'

UNION ALL

SELECT 2, '5 may 2007', 'Info4'

UNION ALL

SELECT 2, '1 feb 2007', 'Info5'

UNION ALL

SELECT 2, '1 feb 2006', 'Info6'

--SQL2005

WITH cte

AS

(SELECT ID, Nm, RANK() OVER (PARTITION BY ID ORDER BY Dat DESC) AS D

FROM testData)

SELECT Nm

FROM cte

WHERE D = 1

--SQL2000

SELECT Nm

FROM

(SELECT ID, MAX(Dat) AS Dt

FROM testData

GROUP BY ID) AS Bob

INNER JOIN testData t

ON Bob.Dt = t.Dat AND Bob.ID = t.ID

HTH!|||

One more trick..

Code Block

--SQL2005

;with cte

as

(select id, nm,dat,max(dat) over (partition by id) as latestdat from testdata)

select nm

from cte

where dat = latestdat

Code Block

--SQL Server 2000

select

main.info

from

#data main

where

exists

(

select * from

(

select

d

,max(dat) dat

from

#data

group by D

) data

where

data.d = main.d

and data.dat=main.dat

)

|||

Thank You guys!!

...for the fast and helpful response. I would never figure that out.