Count Rows returned by BCP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count Rows returned by BCP

Author

Topic

ra_pa_ta
Starting Member

13 Posts

Posted - 2011-08-30 : 14:36:33

How do I count the records returned by the BCP command and store it in a table?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-30 : 14:53:05

You would need to count the lines in the file via VBScript/Powershell or run the same query on the database server to get the count. If you aren't using a query, then just do a count(*).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ra_pa_ta
Starting Member

13 Posts

Posted - 2011-08-30 : 15:24:35

Can you please explain to me in more detail ?
Here is my code :
Set @strSQL = 'bcp ' + @strDatabaseName + '.dbo.' + @strTableName + ' ' + @strINorOUT + ' ' + @strLocalPath + 'HSO_'+@strTextFile +
@strFieldInfo + @strRowTerm + @strErrorFile + ' -S ' + @@servername + ' -T -c ' + @strFirstRow + @strLastRow
Exec @intError = master.dbo.xp_cmdshell @strSQL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-30 : 16:02:13

declare @sql nvarchar(500)
set @sql = 'select count(*) from ' + @strTableName
exec (@sql)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ra_pa_ta
Starting Member

13 Posts

Posted - 2011-08-30 : 22:09:18

How do i collect the row count and store it in a table ?Please correct my syntax below

declare @sql nvarchar(500)
declare @rowcount int
set @sql = 'select count(*) from ' + @strTableName
Update dbo.tbltest t set intRowCount =
exec (@sql)where @strTableName = t.strTableName

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-30 : 22:41:29

You'll need to use an output parameter of sp_executesql and then use that in your update statement.

Does this really need to be dynamic though?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ra_pa_ta
Starting Member

13 Posts

Posted - 2011-08-30 : 23:31:30

No it does not have to be .
I have views when queried everyday would return a different row count which will be bcp to generate flat files. So to begin with I was trying to generate the row count of the flat files . If that is not possible then I can still try to count the rows of the result of these views and update the log table which stores one record for each file extracted everyday .
Do I need to write a cursor to loop through all the views and select the rowcount ?

ra_pa_ta
Starting Member

13 Posts

Posted - 2011-09-01 : 12:17:16

Thanks for your help . Today I resovlved my issue with a work around . I built a view that would select the counts of records from each view that I am bcp from like this :

create view [dbo].[uvwFixMan](DataFileName, DataSourceName, RunDate, RecordCount,[Version],Comments)
as
/* Creates count of records for extract .
*/
select 'Table_'+ CONVERT(varchar,getdate(),112) + '.dat' as DataFileName,
'uvwChannel' as DataSourceName,
CONVERT(varchar,getdate(),101)as RunDate,
Cast(COUNT(*) as varchar) as RecordCount,
'01' as Version,
'NoComments' as Comments
from dbo.uvwChannel
UNION
select 'Table'+ CONVERT(varchar,getdate(),112) + '.dat' as DataFileName,
'uvwCustomerPriceChoice' as DataSourceName,
CONVERT(varchar,getdate(),101)as RunDate,
Cast(COUNT(*) as varchar) as RecordCount,
'01' as Version,
'NoComments' as Comments
from dbo.uvwCustomerPriceChoice
UNION
etc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources