Transact SQL :: Loop On Rows - Count Of Documents For Each Supplier

Mar 22, 2015

I have 3 tables: Suppliers, Documents and Verification, each document may have multiple verification where I need to get the last verification according to verification date. So I just need a "Select top 1 result from Documents, Verification where doc_iddoc=ver_iddoc and result='True' order by ver_date desc" so far I get the result of the last verification, but here's the problem:

I need to get a row with the count of documents for each supplier, I mean:

Supplier Name     Docs      NegativeVerification
Acme Co                10                     1

that is I would need to loop for each supplier and each document and get the last verification, if one of any of documents have negative verification then add it to negative results. Is it possible to achieve this with a query or do I have to do it through stored procedure?

View 11 Replies


ADVERTISEMENT

Transact SQL :: Total Count Of Table Rows

Sep 15, 2015

I have a question regarding the total count of the table rows

Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".

So now I have to store the count in 'nn'.

View 5 Replies View Related

Transact SQL :: Condition Failed Rows Count

Jul 3, 2015

I am using SQL Server 2008.Each stock item will have default 4 document type (1, 5, 6, 7) and each will have 3 zone's (1, 2, 3) to qualify. Each zone will be updated to 1 for that document type if the item successfully pass through it. If all zone are NULL means no transaction. How to retrieve only the failed rows which means not all zone are 1 or NULL.In the image GJ-00064 has one row failed. So how to get the count of failed rows for each item

Expected result:

Uniid <-> Stockcode <-> FailedRows
1670 <-> GJ-00064 <-> 1

View 8 Replies View Related

Count How Many Documents Are In The Table For Each Name (was Query Help)

Jan 16, 2007

Hello,

Brief overview. Got 2 tables, client table and document table. Both tables have client name as the primary key. Client table shows client info, address, phone, dob. Document table shows client name, document, document type.
I need to write a query that will count how many documents are in the table for each name.

This is attempt at it, please let me know whats wrong. Thanks.

SELECT count [client table].client name as cli_name, count ([document table].name as doc_qty)
FROM [client table] INNER JOIN [document table] ON [client table].id = [document table].ID
GROUP BY [client table].name
ORDER BY [client table].name

View 6 Replies View Related

Transact SQL :: Window Function To Count Number Of Rows In The Previous 24 Hours?

Nov 16, 2015

is it possible to use the window functions to count the number of rows in the previous 24hours from the current row.

I have a table of events like:

User, TimeStamp, etc...

I want to identify the row which is the event number 50 in the past 24 hours.

does the window functions can do this? and how?

the ROW PRECEDING etc... appear to not have enough feature to support time related function.

Stream-insight is able to create this type of query, but I have to do it directly in SQL.

View 6 Replies View Related

Excel 2007 Documents Not Displayed In Windows XP Professional Sp2 Start Menu Recent Documents List

Jan 26, 2007

Excel 2007 Documents Not Displayed in Windows XP Professional sp2
Start Menu's Recent Documents List:



Dear Microsoft Support:
I can't figure out how to get recently used Excel 2007 (new file formats) documents to show up in the Windows XP Professional (sp2) Start Menu's Recent Documents List. I checked the Internet, the knowledgebase, many parts of the MS web site, etc. for an answer but can't find one.
Are Excel 2007 documents supposed to show up in the XP Start Menu's Recent Document List? Is this a bug or do I have to do some sort of configuration to make it do so? If it's a bug, when will a fix be available?

Are these new Excel 2007 files filtered out like EXE files are which will not appear in the Recent Document List?

The Excel 2007 file types are listed in the Registry.

This question is also posted on Experts-Exchange...No solutions yet.

Thanks.


Jim.

View 1 Replies View Related

Inserted Rows Count From SSIS Not Like Table Rows Count

Jun 25, 2007

Hi all



i using lookup error output to insert rows into table

Rows count rows has been inserted on the table 59,123,019 mill

table rows count 6,878,110 mill ............................



any ideas

View 6 Replies View Related

Cursor Count Loop - Update Table

Jul 6, 2000

I am importing a text file that list invoice columns. The invoice detail table needs the line items to be listed with sequential numbers. I import the file to a temp table to do the work in and I know I need to have the cursor do loop through and count, however, I have no more hair to pull out.

The table looks something like this.

inv# SKU
1001 ABC123
1001 DEF456
1001 GHI789
1002 123DEF
1002 456GHI
1002 876HGT

I need the cursor to go through and number each line, something like this.

inv# SKU Line#
1001 ABC123 1
1001 DEF456 2
1001 GHI789 3
1002 123DEF 1
1002 456GHI 2
1002 876HGT 3

Any help is greatly appriciated.

Thanks

View 1 Replies View Related

Inserting Multiple Records Using A @start And @count With One INSERT (and No Loop)

Aug 22, 2007

Is there a way to insert multiple records into a database table when you're just given "count" of the number of rows you want? I want to do this in ONE insert statment, so I don't want a solution that loops round doing 100 inserts - that would be too inefficient.

For example, suppose I want to create 100 card records starting it card number '1234000012340000'. Something like this ...

declare @card_start dec(16)
set @card_start = '1234000012340000'
declare @card_count int
set @card_count = 100

drop table card_table

create table card_table (
card_number dec(16),
activated char default 'N'
)

insert into card_table
select
... ???? ....

But WITHOUT using a while-loop (or any other kind of loop). I'm looking for fast and efficient code! Thanks.

View 3 Replies View Related

List Of Items By Supplier

Jan 14, 2008

Hi,

I have the following table Descriptions:-

Item No. -- Supp1-- Supp2-- Supp3 .... Supp10


I want to have a list of items for each supplier on the tables showing the following:-

Header is the Supplier No.
Details of each item Item No


Thanks for your help

View 2 Replies View Related

Third Party Supplier Access To SQL

Apr 24, 2008



We have a hosted SQL server with various internal applications on it. It has been decided by on high that the same server will also have a database from a third party on the server. The third party have asked for full system admin rights on the SQL server.

Obviously I am not going to grant that access level as it gives them full control on the box. From my viewpoint read and write access to the data can be tolerated but anything else will be denied. Would you consider this standard and is there any documentation with recommendations for control of external suppliers ?

I am sure that they shouldn`t be doing any work on tables structure, sp`s etc on a live database. That work should be tested, scripted etc and then run on in a controlled environment by my company.

Anyone have any advice ?

Thanks

SI

View 1 Replies View Related

Loop Query To Update Output Depending Upon Count Of Distinct Entries

Jun 25, 2013

I need to update the result depending upon the count of distinct entries.

Example

ID Employee Region State
1 ABC AMEA MI
2 DEF AMEA MI
3 XYZ APAC TX

I want the result as below

ID Employee Region State
1 ABC AMEA MI-1
2 DEF AMEA MI-2
3 XYZ APAC TX

since the count of Region is 2

I tried using DECLARE @intFlag INT and stuff but wasn't able to get the solution.

View 2 Replies View Related

Transact SQL :: Table Trigger To Delete All Rows Before Inserting Rows

Jul 24, 2015

I have a SQL script to insert data into a table as below:

INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents

I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.

USE [BB]
GO
/****** Object:  Trigger    Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 3 Replies View Related

PL-SQL LOOP Equivilant In TRANSACT-SQL

Jul 20, 2005

Hi,I need to convert this anonymous pl-sql block into transact-sql to runin Microsoft SQL Server Query Analyser.Does anyone know what the conversion syntax would be?I know that to declare variables to use the @.PRINT for printing the output.No idea for the LOOP.Any suggestions greatly appreciated.Many thanks.Thiko!____________________________________________SET SERVEROUTPUT ONDECLARErecordcount NUMBER;BEGINDBMS_OUTPUT.ENABLE(5000000); -- Sets buffer size.recordcount := 0;LOOPINSERT INTO RecoverTest VALUES (SYSDATE - (recordcount / 4));COMMIT; -- frees up rollback segment;recordcount := recordcount + 1;DBMS_OUTPUT.PUT_LINE('INSERT: ' || recordcount || ' - 45000 ');EXIT WHEN recordcount > 45000;END LOOP;END;/

View 1 Replies View Related

Select Most Recent Record By Product &#043; Supplier

Aug 27, 2005

I am trying to pickup the most recent record by Product and Supplier.
eg.

Rec Prod Supp Date
1 1000 WES01 Jan 1/2005
2 1000 WES01 Apr 8/2005
3 1001 WES01 Apr 8/2005
4 1001 NAN04 Feb 15/2005
The Result I want is as follows:

Rec Prod Supp Date
2 1000 WES01 Apr 8/2005
3 1001 WES01 Apr 8/2005
4 1001 NAN04 Feb 15/2005
I have tried various methods with the TOP 1 and DISTINCT statements, but have not been able to get the results I am looking for. Help would be appreciated.

View 4 Replies View Related

Loop On All Rows In A View

Dec 11, 2007

Hi, i have a Stored Procedure that is calling a view. I need to pass on all the records of this view so i made another view that returns the rows count and then a for loop from 0 to count-1. But then how can i access the rows and columns of the view one by one? Any idea?
And if someone has a better logic that gives the same result please let me know. Thanks...

View 10 Replies View Related

Transact SQL :: Pass Filenames To A Loop

Aug 27, 2015

I’m working on building a file list from a directory I have and wish to loop through the filenames. I’ve worked out I can get the list using

EXEC
xp_dirtree @path, 10, 1

I’m just not sure out I pass this to my loop 1 element at a time.

E.g. Let’s say there are 10 files in my folder, I wish to loop through each name using my @Filename parameter

DECLARE @Path
varchar(50), @FileName
varchar(20)
SET @Path
= 'C:myFiles'
EXEC
xp_dirtree @path, 10, 1

[Code] ....

View 5 Replies View Related

Transact SQL :: Loop Though Row And Populate Missing Row

Oct 8, 2015

Col1 to Col9 represent the account information, Col10 amount, Col 11 represents the month and  Col12 represents year. According to the data there wasn't any activity for the month 1, 2 and 3.

Col1
Col2
Col3
Col4
Col5
Col6
Col7
Col8
Col9
Col10
Col11
Col12

[code]....

View 3 Replies View Related

Transact SQL :: How To Update Table Without Using While Loop

Nov 15, 2015

I have two tables i have to update table2 using table1 without using while loop.

example given below.

Table1 

rid
id
amt
firdate
lastdate

1
1
500

[code]....

View 7 Replies View Related

Transact SQL :: Can Pass A Variable Value Out While Loop

Nov 3, 2015

-- Create an Employee table.

CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30)  NOT NULL,
LastName  nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);

-- Populate the table with values.

INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)

[code]....

View 3 Replies View Related

Choose Main Or Preferred Supplier ( Was A Thing That Cannot Be Solved...?)

Jan 24, 2005

I have a table which contains suppliers that can supply certain articles.
There can be many suppliers to each article, and therefore there is a field that tells you if this supplier is the main supplier for the article, this field is called "arthuvudavt". However, you aren't forced to use this field in the table.
As a alternative you can use priority. Each supplier has given priority where
0 means "most preferred" and any other means "less preferred"
What I want to do is to pick the right supplier for a given article in one SQL-statement regardless of if you choose to use the priority or if you choose to use the main supplier field.

Abbreviated description of the table, called AL.

SuppNo Article Arthuvudavt Prio
10101 A-10 NULL 0
10202 A-10 1 0
10303 A-10 NULL 1
10101 B-10 NULL 0
10202 B-10 NULL 0
10303 B-10 NULL 1
10101 C-10 NULL 1
10202 C-10 NULL 0
10303 C-10 NULL 1

In the above case, I'd like the statement to return supplier 10202 for article A-10 (the one that has the arthuvudavt checked), and
only one row for B-10, which one doesn't really matter, since someone has been making a fault here, this should not happen, but my SQL-statement should break because of this scenario. For C-10 supplier 10303 should be returned.

Is there any way this could be done?

The reason I ask is that I try to learn new ways of solving things, and this problem was kind'a easily solved using variables, but I was wondering if it was possible to do it without it.

Thanks in advance
Jonas

Sorry for the poor formatting, I didn't have the time to make it right.

View 14 Replies View Related

Transact SQL :: Loop - How To Get Data For Each Of Active Users

Jun 9, 2015

My insert statement for #Data - I only need to process each @EmployeeID one time, which is why I thought a loop would be sufficient, but I let this process run for 2 hrs and it still had not completed, so I feel I must have set-up something incorrectly!

This is my syntax, I am creating a table of active users, then wanting to get data for each of those active users.  But only get the data for each active user 1 time.

Declare @EmployeeID varchar(50)
CREATE TABLE #ActiveUsers
(
ID INT IDENTITY NOT NULL
,EmployeeID varchar(50)
,processed int
)
Create Table #Data

[Code] ....

View 5 Replies View Related

Transact SQL :: Date Loop Returning Min And Max Datetimestamp

Oct 21, 2015

I have a requirement for the following query which uses a timestamp data type field

[eventdate]Select field1, fileds2, filed3, {min(eventdate) as max(eventdate) per day/date} from sometable

View 4 Replies View Related

Transact SQL :: How To Add Columns To Temporary Table In Loop

Aug 27, 2015

How to add columns to temporary table in loop with sample code.

View 12 Replies View Related

Transact SQL :: Passing Value In Where Condition Loop Wise

Jun 1, 2015

I want to calculate year to month data for month wise . Ihave start range and end range table like below

Start range and end range table

Start dateEnd date
01-04-201401-11-2014
01-04-201401-12-2014
01-04-201401-01-2015
01-04-201401-02-2015

[Code] ....

Then I want to pass this value one by one in below table for get value month wise

Main table

Branchamountperiod
Branch110201103
Branch22201104
Branch33201401
Branch44201402
Branch58201403
Branch610201404

[Code] ....

The below query is for november month: (Apr to Nov)

select * from maintable
where period between '01-04-2014' and '01-11-2014'
then for December month : (Apr to Dec)

So I want to pass second row

select * from maintable
where period between '01-04-2014' and '01-12-2014'
.....
....
select * from maintable
where period between '01-04-2015' and '01-12-2015'

Like wise I want to get month wise data of YTM data.

My expected output is

201411
201412
201501
201502
201503
201504
201505

[Code] ....

View 10 Replies View Related

Transact SQL :: Loop A Month In A Specified Date Range?

Jul 23, 2015

I am trying to query a code where i need to loop a month in a specified date range. Inside the loop I need to return a result of data each month and need to update the table of the returned data. How do I do the update a field inside the loop? Here's my query:

declare @table1 table (
YEAR_EFF int,
MONTH_EFF int,
IDNumber (8),
SUBS_CNT smallint,
MEM_CNT smallint)
declare @StartDate datetime,

[code]....

Others says I need to use exec sp_executesql N'' but how do I use it using my code above?

View 7 Replies View Related

Transact SQL :: While Loop Does Not End When Condition Is No Longer True

Dec 1, 2015

I have a Stored Procedure, wherein I need to use a while loop. essentially the loop looks like this.

Set @Kount = 1
--TestScript
Select 'TempReqTable', * from @TempReq
WHILE Exists(Select * from @TempReq WHERE TempID = @Kount)
BEGIN
--Do stuff with values from table
Set @Kount += 1
END

The test script confirms that there is only one row in the @TempReq table.  However the loop never stops running and @Kount keeps being incremented and incremented until I manually stop the execution of the SP.

Is there some rule that I am not aware of that does not allow the use of an Exists statement in the condition of a While loop?

View 12 Replies View Related

Transact SQL :: Creating Stored Procedure With Cursor Loop

Sep 18, 2015

I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten.  Below is the stored procedure I've created:

ALTER PROCEDURE [dbo].[ap_CalcGrade] 
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,

[Code] ....

And below is the "test query" I'm using: 

--  *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
        @Numeric   int,        -- Numeric Grade
        @Result    int         -- Procedure Status (0 = OK) 
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3, 

[Code] ....

This is resulting in an output of: 

A+ 97
A+ 97
C- 72

but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
 
A+ 97
No Find
C- 72

I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it.  Below is the assignment requirements:

Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:

1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2. Outputs the numeric grade and the letter grade back to the user
3. If the numeric grade is found, return 0, otherwise return 1
4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade

View 6 Replies View Related

Transact SQL :: Single Records - Loop To Only Insert Sum Total Of Each Day

Apr 22, 2015

I have the following query:

BEGIN TRAN

Declare @StartDt date = '2015-03-15'
Declare @EndDt date = DATEADD(M, 1, @StartDt)
declare @Days int = DATEDIFF(d, @StartDt, @EndDt)
declare @TBLSales as table(SaleDate date, Value money)
DECLARE @Today date
declare @TBLSalesCounts as table( StatusDesc varchar(100), Value money)

[Code] ....

I end up with the following result :

How would I alter my while loop to only insert the sum total of each day, instead of creating duplicates for each day.

E.g.
2015-04-22
1150.00
2015-04-21
 785.00
2015-04-20
 750.00

View 3 Replies View Related

Inserting Multiple Rows In Loop With A Sql Stored Procedures

Jun 4, 2008

I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
 
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String)
'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName)
Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand
Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable
objConn.Open()
command.CommandType = CommandType.Text
command.Connection = objConn
command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS")
adapter.Fill(DataTable)
'Sql DB vars
'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand
 sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn)
sqlServerCommand.CommandType = CommandType.StoredProcedure
sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows
sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
 
sqlServerCommand.ExecuteNonQuery()Next
 
sqlServerConn.Close()
objConn.Close()
End Sub
 
 
Thanks for eveones input in advance.

View 4 Replies View Related

Inserting Multiple Rows In Loop With A Sql Stored Procedures

Jun 4, 2008

I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
 
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String)
'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName)
Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand
Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable
objConn.Open()
command.CommandType = CommandType.Text
command.Connection = objConn
command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS")
adapter.Fill(DataTable)
'Sql DB vars
'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand
 sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn)
sqlServerCommand.CommandType = CommandType.StoredProcedure
sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows
sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
 
sqlServerCommand.ExecuteNonQuery()Next
 
sqlServerConn.Close()
objConn.Close()
End Sub
 
 
Thanks for eveones input in advance.

View 10 Replies View Related

Transact SQL :: When To Use Count And Sum

Jun 20, 2013

How do you know when you should use COUNT and when to use SUM functions?

View 12 Replies View Related

Transact SQL :: Want To Get A SUM Of COUNT

Jun 29, 2015

I have the following query and want the results within my query although I want to be able to get the SUM of my COUNT results. So basically I want the SUM of [Document Count] within my query/

DECLARE @Date date;
SET @date = '02/05/2015'
SELECT CONVERT(VARCHAR,CONVERT(Date, @Date, 103),101) AS [Date Scanned], (SELECT COUNT(*) FROM QUEUE WHERE date_scanned = @Date) AS [Total Scanned],
COUNT(dbc_state) AS [Document Count],
CASE
WHEN dbc_state = 1 AND ON_HOLD = '1' THEN 'On Hold'

[code]....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved