Flatfilesource(s) In A Loop
Jul 9, 2007
I am trying to design a package to import the data of several .tx files into a table in sql server.
1) I created an execute task that truncates the sql server table i.e. truncate table tblContacts
2)
Placed a forrloop container with enumerator: foreach file enumerator
Folder points to the folder that holds the txt files
file: *.*
filename: fully qualified
variablemapping: User::FileName with Index 0
3)
placed a data flow task inside the forloop
this dataflow task has the following dataflow:
FlatFile Source: connection manager is pointing to one of the txt files
OLE DB Destination to place the txt data into tblContact in the database.
The question:
when the package is run, the tblContact gets populated only from the first txt file, i.e. the one which I placed in the flatfilesource connection manager.
How can I allow several files in the flatfilesource, instead of the one I have now...
Thanks
View 1 Replies
ADVERTISEMENT
Jul 26, 2007
Hi,
I am using a foreach loop to go through the .txt files inside a folder.
Using a variable I can pickup the filenames the loop is going through.
At present there is a sql task inside the foreach loop which takes the filename as a parameter and passes this filename to a stored procedure.
Now I would like to add one extra step before this sql task. Would like to have a dataflow with flatfile source which connects to oledb destination.
The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?
Please note, this is a different question to my other post.
Many Thanks
View 6 Replies
View Related
Jan 21, 2008
I cannot seem to get my code to work. I have read all of the samples I could find (about 30 of them), a chapter on SSIS programming in a SQL Server 2005 book, and all of the MS online documentation. I would like to programmatically create an SSIS package that can load data from a flat file source into an oledb destination. I am not doing any transformations.
For the test, my source file has now degenerated to two columns. The entire file looks like this:
hello,goodbye
here,there
That's it, two rows, two column, no headers.
My destination table is TestTable(col1 varchar(150), col2 varchar(150))
This ought to be trivial. Here is my code. If someone can point out what I have done incorrectly, I would be truly grateful.
//// Create the package
Package myPackage = CreatePackage("SSISPackage", "Sample CSV Import Package");
//// Events.
PackageEvents myPackageEvents = new PackageEvents();
ComponentEvents myPipelineEvents = new ComponentEvents();
//// Create the data flow task
TaskHost th = myPackage.Executables.Add("DTS.Pipeline") as TaskHost;
th.Name = "DataFlow";
th.Description = "The DataFlow task in the Onvoy CSV Import sample.";
MainPipe myDataFlowTask = th.InnerObject as MainPipe;
myDataFlowTask.Events = myPipelineEvents as wrap.IDTSComponentEvents90;
//// Add the flatfile source connection manager.
ConnectionManager cmFlatFile = myPackage.Connections.Add("FLATFILE");
// Set the stock properties.
cmFlatFile.Properties["ConnectionString"].SetValue(cmFlatFile, "c:\temp\testimport.txt");
cmFlatFile.Properties["Format"].SetValue(cmFlatFile, "Delimited");
cmFlatFile.Properties["DataRowsToSkip"].SetValue(cmFlatFile, 0);
cmFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(cmFlatFile, false);
cmFlatFile.Properties["Name"].SetValue(cmFlatFile, "FlatFileConnection");
cmFlatFile.Properties["RowDelimiter"].SetValue(cmFlatFile, "
");
cmFlatFile.Properties["TextQualifier"].SetValue(cmFlatFile, """);
//// Add the OLEDB destination connection manager.
ConnectionManager cmSubscriber = myPackage.Connections.Add("OLEDB");
// Set stock properties.
cmSubscriber.ConnectionString = @"Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=subscriber;Data Source=MyDataSource;Auto Translate=False;";
cmSubscriber.Name = "SubscriberConnection";
cmSubscriber.Description = "SubscriberConnection";
//// Add Flat File Source
// Add the component to the dataFlow metadata collection
IDTSComponentMetaData90 myFlatFileSource = myDataFlowTask.ComponentMetaDataCollection.New();
myFlatFileSource.ComponentClassID = "DTSAdapter.FlatFileSource";
CManagedComponentWrapper instFlatFileSrc = myFlatFileSource.Instantiate();
instFlatFileSrc.ProvideComponentProperties();
instFlatFileSrc.SetComponentProperty("RetainNulls", true); // Treat empty columns as null.
// Set the common properties
myFlatFileSource.Name = "FlatFileSource";
myFlatFileSource.Description = "Flat file source";
// Associate the runtime ConnectionManager with the component
myFlatFileSource.RuntimeConnectionCollection[0].ConnectionManagerID
= myPackage.Connections["FlatFileConnection"].ID;
myFlatFileSource.RuntimeConnectionCollection[0].ConnectionManager
= DtsConvert.ToConnectionManager90(myPackage.Connections["FlatFileConnection"]);
// Add columns to the FlatFileConnectionManager
// Hardcode the columns for now
List<string> srcColumns = new System.Collections.Generic.List<string>();
srcColumns.Add(""Column1"");
srcColumns.Add(""Column2"");
//srcColumns.Add(""Column3"");
//srcColumns.Add(""Column4"");
//srcColumns.Add(""Column5"");
//srcColumns.Add(""Column6"");
//srcColumns.Add(""Column7"");
//srcColumns.Add(""Column8"");
//srcColumns.Add(""Column9"");
//srcColumns.Add(""Column10"");
//srcColumns.Add(""Column11"");
//srcColumns.Add(""Column12"");
//srcColumns.Add(""Column13"");
// get the actual connection manager instance
//wrap.IDTSConnectionManagerFlatFile90 ff = myFlatFileSource.RuntimeConnectionCollection[0].ConnectionManager as wrap.IDTSConnectionManagerFlatFile90;
wrap.IDTSConnectionManagerFlatFile90 ff = cmFlatFile.InnerObject as wrap.IDTSConnectionManagerFlatFile90;
if (ff != null)
{
wrap.IDTSConnectionManagerFlatFileColumn90 column;
wrap.IDTSName90 name;
foreach (String colName in srcColumns)
{
// iterate
// now create a new column for the connection manager
column = ff.Columns.Add();
// if this is the last row
if (srcColumns.IndexOf(colName) == (srcColumns.Count - 1))
column.ColumnDelimiter = "
";// add the row delimiter
else
column.ColumnDelimiter = ",";
name = (wrap.IDTSName90)column;
name.Name = colName.Replace(""", "");
column.TextQualified = true;
column.ColumnType = "Delimited";
column.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
column.ColumnWidth = 150;
column.MaximumWidth = 150;
column.DataPrecision = 0;
column.DataScale = 0;
}
}
instFlatFileSrc.AcquireConnections(null);
instFlatFileSrc.ReinitializeMetaData();
instFlatFileSrc.ReleaseConnections();
//// End Add Flat File Source
//// Add OLEDB Destination
IDTSComponentMetaData90 myOledbDestination = myDataFlowTask.ComponentMetaDataCollection.New();
myOledbDestination.ComponentClassID = "DTSAdapter.OLEDBDestination";
CManagedComponentWrapper instOleDbDest = myOledbDestination.Instantiate();
instOleDbDest.ProvideComponentProperties();
// Set stock properties.
myOledbDestination.Name = "OLEDBDestination";
myOledbDestination.Description = "Destination for data";
// Associate the runtime connection manager
// The connection manager association will fail if called before ProvideComponentProperties
myOledbDestination.RuntimeConnectionCollection[0].ConnectionManagerID
= myPackage.Connections["SubscriberConnection"].ID;
myOledbDestination.RuntimeConnectionCollection[0].ConnectionManager
= DtsConvert.ToConnectionManager90(myPackage.Connections["SubscriberConnection"]);
// set custom component properties
instOleDbDest.SetComponentProperty("OpenRowset", "[dbo].[TestTable]");
instOleDbDest.SetComponentProperty("AccessMode", 0);
// Acquire Connections and reinitialize the component
//instOleDbDest.AcquireConnections(null);
//instOleDbDest.ReinitializeMetaData();
//instOleDbDest.ReleaseConnections();
//// Map the source to the destination
myDataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(
myFlatFileSource.OutputCollection[0], myOledbDestination.InputCollection[0]);
instOleDbDest.AcquireConnections(null);
instOleDbDest.ReinitializeMetaData();
instOleDbDest.ReleaseConnections();
//// Map the source columns to the destination columns
// Get the destination's default input and virtual input.
IDTSInput90 input = myOledbDestination.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual input column collection.
//foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
//{
// // Call the SetUsageType method of the destination
// // to add each available virtual input column as an input column.
// instOleDbDest.SetUsageType(
// input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
// instOleDbDest.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);
//}
IDTSVirtualInputColumn90 vColumn = vInput.VirtualInputColumnCollection[0];
instOleDbDest.SetUsageType(
input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
instOleDbDest.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection["col1"].ID);
vColumn = vInput.VirtualInputColumnCollection[1];
instOleDbDest.SetUsageType(
input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
instOleDbDest.MapInputColumn(input.ID, vColumn.ID, input.ExternalMetadataColumnCollection["col2"].ID);
instOleDbDest.AcquireConnections(null);
instOleDbDest.ReinitializeMetaData();
instOleDbDest.ReleaseConnections();
//// Validate and execute the package
DTSExecResult status = myPackage.Validate(null, null, myPackageEvents, null);
if (status == DTSExecResult.Success)
{
DTSExecResult result = myPackage.Execute(null, null, myPackageEvents, null, null);
}
View 8 Replies
View Related
Nov 8, 2006
Hi,
I am trying to programmatically in C# create FlatFileSource and OleDB Destination?
I would like your help.... How about column mapping.
I would appreciate your reply.....
View 1 Replies
View Related
May 28, 2010
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies
View Related
Feb 22, 2006
I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
View 6 Replies
View Related
Mar 3, 2006
I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.
I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.
Any solution to this? anyone experienced anything similar
View 1 Replies
View Related
Jul 8, 2006
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
View 18 Replies
View Related
Feb 23, 2006
I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...
My Lookp table is like the following...
Srn srctable desttable
1 SRC1 DEST1
2 SRC2 DEST2
3 SRC3 DEST3
Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....
How do I run the pacakge for each of the rows... ..............................
View 1 Replies
View Related
Aug 20, 2007
I used to loop through recordsets with ease in old classic .asp pages.
Please Help me understand how Professionals now loop through and update tables using JUST SQL Query Analyzer using my pseudo-code provided below.
I would love to learn how to do it to better develop my skills.
SELECT * FROM zz_2007_Booth_Visitors
WHERE COALESCE ([Product Interest - Other Actuator],
[Product Interest - Chillers],
[Product Interest - Other Chiller],
[Product Interest - Electronic Products],
[Product Interest - Other network interfaces],
[Product Interest - Fittings],
[Product Interest - High Vacuum],
[Product Interest - Other high vacuum actuators],
[Product Interest - Pick& Place and Transfer],
[Product Interest - Teflon Products],
[Product Interest - Training],
[Product Interest - Valves& Manifolds],
[Product Interest - Actuators]) Is Not Null
Order BY [Contact Name]
IF [Product Interest - Actuators] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Phuematic = 1 Where [Contact Name] = [Contact Name]
IF [Product Interest - Other Actuator] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = [Contact Name]
IF [Product Interest - Other Chillers] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Chiller = 1 Where [Contact Name] = [Contact Name]
View 24 Replies
View Related
Jan 12, 2008
Dear All.
Have a nice day.
I have db table, I need to update all fields of table.
Please can you write code," for loop " how can update all fields of my table by loop.
Thanks.
Zahyea.
View 3 Replies
View Related
Mar 3, 2008
Hello everyone,I've got this While loop here which is giving me a problem:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN DECLARE @ProdId int, @ProdSize varchar, @ProdQuan int SELECT @ProdId = ProductId, @ProdSize = ProductSize, @ProdQuan = Quantity FROM _ShoppingCart WHERE CartId = @CartId If @ProdSize = 'XL' BEGIN UPDATE _ProductBatches SET XL = '0' WHERE ProductId = @ProdId END DELETE FROM _ShoppingCart WHERE ProductId = @ProdId AND CartId = @CartIdEND The problem is that the IF statement isn't being executed. And I know for a fact that 'XL' is ProductSize in my _ShoppingCart database. Whats even stranger is that my delete statement is being executed. So @ProdId is Being set correctly, but when it gets to the IF @ProdSize = 'XL' it doesn't get executed for some reason. If @ProdId is being recognized correctly in my DELETE statement, why is my @ProdSize not being reconized correctly in my IF statement. I checked my _ShoppingCart database, and my ProductSize is definitely 'XL'. Can any one see what could be going on here. Thank you, Alec
View 7 Replies
View Related
Mar 4, 2008
Hello everyone...... I'm trying to do the following but am having issues:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN execute code with item......... erase itemEND In the while loop I want to execute code from each item in my _ShoppingCart and then erase them until there are no more items. However the above code gives me the error: "Subquery returned more than 1 value. This is not permitted........" It works fine when there is only one item. Does any one know what format to use when dealing with more that one entry? Thank you, Alec
View 2 Replies
View Related
Sep 21, 2000
hi,
I am trying to find a way of using a loop that won't be an endless loop because I have to insert parts of a string until the string reaches the end. I am unable to make the loop get to a point where the statement is false.
Is there anyway I can find out the length of the string so that I can tell the statement to loop while the statement is true?
Help!!!!!!!!!!!!1
View 1 Replies
View Related
Jan 26, 2004
HeaderLoop: for forHeader as curHeader dynamic scroll cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 do
set AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(AcctNum);
set Cntr=Cntr+1
end for;
The above is the sybase version of a 'for loop' . The query
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 results in 1000 results.
How do I change that in SQL?? Do we have a for loop ??
I checked in BOL but it is confusing with "browse" etc n some other options.
can I write like this?
for { Browse { declare curHeader dynamic cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 }
set @AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(@AcctNum);
set @Cntr=@Cntr+1
}
I duno its just my guess, can any one help me out. @Cntr and @Acctnum are declared
in the beginnning.
tks
View 14 Replies
View Related
Apr 11, 2008
I get the following results on a view.
Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 2 | A1102
99-999 | 4 | AB839
What I would like is the following.
Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 1 | A1102
06-324 | 1 | A1102
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839
View 2 Replies
View Related
Apr 18, 2008
I have a loop is running with no end point. What I'm trying to do is get the Grand total of each row where BudgetNodeID = 120. Your help is much appreciated.
AV
Set NoCount on
Declare @Amt as bigint
Declare @Cont as bigint
Declare @Mark as Bigint
Declare @Total as bigint
Declare @BudgetNodeID as Bigint
Declare @GTotal as bigint
Set @BudgetNodeID ='120'
Set @Amt = 0
set @Cont = 0
set @Mark = 0
set @GTotal = 0
While exists (Select * from xBudgetNodeCosts where BudgetNodeID =@BudgetNodeID) Begin
select @Amt = IsNull(xBudgetNodeCosts.Qty,0) * IsNull(xBudgetNodeCosts.CostRate,0)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
select @Cont = @Amt * (xBudgetNodeCosts.Contingency/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID ) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
select @Mark = @Cont * (xBudgetNodeCosts.Markup/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
-- compute the sell
select @Total = @Amt + @Cont + @Mark
-- add to grand total
Select @GTotal = Sum(@Total+ @GTotal)
select @GTOtal
end
View 5 Replies
View Related
Jun 17, 2008
Hi
I want to loop this command with a pause. It must run every 30 min to refresh my query. Can anyone assist me.
select * from BULKPICKXLOC
Thanks
View 4 Replies
View Related
Apr 11, 2006
Exist a funtion that I can use in a SP that do something like the for to next or Do while Loop do?
View 5 Replies
View Related
Jan 30, 2007
I need to keep the first 4 values above 80 or the first 2 values above 90. If there are not enough, I need to keep as many values as possible. Should this be done with a while loop, if so, how would it be done.
View 19 Replies
View Related
Mar 13, 2007
hello,
i have this SP to tally up my inventory tables.. im finding a way to loop through my table tblitemdetail to get necessary parameter to be insert into my other SP (SP_StkAdj_tbl_alignmt) that should accept this params (from the itemdetail) :-
@ItemID ='',
@ClientID='',
@CustomLotNo ='',
@UDF1=NULL,
@UDF2=NULL,
@UDF3 =NULL,
@UDF4 =NULL,
@UDF5=NULL,
@UDF6 =NULL,
@UDF7 =NULL,
@UDF8 =NULL,
@UDF9 =NULL,
@UDF10 =NULL,
@StockID ='0950-4388',
@RecvOwn ='OWN',
@ConsignorID ='JAB1MY'
EG:i will GROUP BY my itemdetail so it will give me the x records of data with :-
SELECT ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3, ownerstatus
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3,ownerstatus
ORDER BY ItemID
with the result then, i need to insert the param into the SP:SP_StkAdj_tbl_alignmt so that it perform the calculation.
so i guess this will need some looping from the result set i get from the group by and some Sp calling from main Sp
~~~Focus on problem, not solution~~~
View 20 Replies
View Related
Apr 18, 2007
hi all, ive no idea what's wrong with my while loop nested in IF .. it only work correctly when i remove the while from IF :-
IF @Picktype='FI'
BEGIN
-- Insert data into @Stage to play around
DECLARE@Stage TABLE (RecID INT IDENTITY(1, 1), ItemStorageID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))
WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0
INSERT@Stage (ItemStorageID, Qty, RecvDate, BB)
SELECT TOP 1t1.ItemStorageID,
t1.Qty,
t1.RecvDate,
t1.BB
FROM#DataList AS t1
LEFT JOIN@Stage AS s ON s.ItemStorageID = t1.ItemStorageID
WHEREs.ItemStorageID IS NULL
ORDER BYt1.RecvDate,
t1.Qty DESC
IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue
SELECTrecID,
ItemStorageID,
Qty,
RecvDate,
BB
FROM@Stage
ELSE
select * from #DataList
END
correct result after i remove the while from inside IF
DECLARE@Stage TABLE (RecID INT IDENTITY(1, 1), ItemStorageID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))
WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0
INSERT@Stage (ItemStorageID, Qty, RecvDate, BB)
SELECT TOP 1t1.ItemStorageID,
t1.Qty,
t1.RecvDate,
t1.BB
FROM#DataList AS t1
LEFT JOIN@Stage AS s ON s.ItemStorageID = t1.ItemStorageID
WHEREs.ItemStorageID IS NULL
ORDER BYt1.RecvDate,
t1.Qty DESC
IF @pickType='FI'
BEGIn
IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue
SELECTrecID,
ItemStorageID,
Qty,
RecvDate,
BB
FROM@Stage
end
~~~Focus on problem, not solution~~~
View 9 Replies
View Related
Sep 18, 2007
I have the following stored procedure which enters items bought by the usr in the database, inserting the user's ID, the item and the price. now every user has a unique id and every user can only buy three items; thus only three inputs must be inserted in the table, how can i do that? This is the current SP i have...
ALTER PROCEDURE [dbo].[spA_ALW_InsertIntoMLAGoods]
@MLAFormIDFINT,
@DescriptionNVARCHAR(50),
@AmountINT
AS
BEGIN
BEGIN TRY
--UPDATE MLAGoods
--SET
--MLAFormIDF=@MLAFormIDF,
--Description=@Description,
--Amount=@Amount
--WHERE MLAFormIDF = @MLAFormIDF
INSERT INTO MLAGoods
(
MLAFormIDF,
Description,
Amount
)
VALUES
(
@MLAFormIDF,
@Description,
@Amount
)
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS [ERRORMSG], ERROR_SEVERITY() AS [ERRORSEV]
RETURN @@ERROR
END CATCH
END
View 4 Replies
View Related
Sep 25, 2007
Hi i have a sql loop query which i have working in asp fine, i have altered it to try and get it working as a stored procedure.
but i am not sure what the syntax is.
can someone help please.
many thanks
DECLARE
CURSOR GetWebOrder_cur IS
SELECT O_R_ID, O_Name, O_Add_1, O_DB_Code, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, O_PostCode, O_CCode, O_Service, O_Instore, O_STC_Code, O_ID
FROM [newserver].dbo.X_TBL_ORDER
WHERE NewOrder = 0
BEGIN
FOR GetWebOrder_rec IN GetWebOrder_cur LOOP
-- SET ALL FIELDS
set R_ID34 = GetWebOrder_cur("O_R_ID")
set R_Name = GetWebOrder_cur("O_Name")
set R_Contact = GetWebOrder_cur("O_Add_1")
set R_Code = GetWebOrder_cur("O_DB_Code")
set R_Add_1 = GetWebOrder_cur("O_Add_2")
set R_Add_2 = GetWebOrder_cur("O_Add_3")
set R_Add_3 = GetWebOrder_cur("O_Add_4")
set R_Add_4 = GetWebOrder_cur("O_Add_5")
set R_Add_5 = GetWebOrder_cur("O_Add_6")
set R_Add_6 = GetWebOrder_cur("O_Add_6")
set R_PostCode = GetWebOrder_cur("O_PostCode")
set R_CostCode = GetWebOrder_cur("O_CCode")
set R_Delivery = GetWebOrder_cur("O_Service")
set R_Instore = GetWebOrder_cur("O_Instore")
set R_STCODES = GetWebOrder_cur("O_STC_Code")
set WebOrderID = GetWebOrder_cur("O_ID")
-- GET MAX ID
SELECT Max(O_ID) AS MAXOID FROM dbo.X_TBL_ORDER
-- INSERT ORDER INTO F4 ORDERS
INSERT INTO dbo.X_TBL_ORDER
(O_Date, O_R_ID, O_Name, O_DB_Code, O_Add_1, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, O_PostCode, O_CCode, O_Service, O_O_By, O_Instore, O_STC_Code)
VALUES
(getdate(), R_ID34, R_Name, R_Code, R_Contact, R_Add_1, R_Add_2, R_Add_3, R_Add_4, R_Add_5, R_PostCode, R_CostCode, R_Delivery, R_Contact, R_Instore, R_STCODES)
-- UPDATE VIRTUAL SERVER SET NewOrder = 1
UPDATE [newserver].dbo.X_TBL_ORDER
SET NewOrder = 1
WHERE O_ID = WebOrderID
-- SET CURSOR FOR ORDERLINES
CURSOR orderlines_cur IS
SELECT *
FROM [newserver].dbo.X_TBL_ORDER_LINE
WHERE OL_O_ID = @WebOrderID
-- OPEN LOOP THROUGH ORDERLINES
FOR orderlines_rec in orderlines_cur LOOP
-- SET ORDERLINE FIELDS
set B_St_Code = orderlines_cur("OL_St_Code")
set B_Description = orderlines_cur("OL_Desc")
set B_Qty = orderlines_cur("OL_Qty")
set B_dbcode = orderlines_cur("OL_DB_Code")
-- INSERT INTO F4 ORDERLINES
INSERT INTO dbo.X_TBL_ORDER_LINE
(OL_O_ID, OL_St_Code, OL_Desc, OL_Qty, OL_Allocated, OL_Despatch, OL_DB_Code)
VALUES
(B_preorderID, B_St_Code, B_Description, B_Qty, B_Qty, B_Qty, B_dbcode)
-- CLOSE LOOP THROUGH ORDERLINES
END LOOP;
END LOOP;
END;
View 9 Replies
View Related
Nov 21, 2007
Hi,
I have this scenario: I have a table one database in sql called facttable_Temp with columns CustomerName, ItemKey.The fields are varchar.
I have another table called Accounts in a different database. Accounts contains fields such as CustomerName,Account. The fields are varchar.
What I need to do is to check if the values for CustomerName from table FactTable_Temp exists in the field CustomerName in the table Accounts.
If it exists then I need to insert the entire row for that CustomerName including ItemKey into a 3rd table called FactTable.
What is the best way of accomplishing this in SSIS?
Thanks
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
View 1 Replies
View Related
Feb 14, 2008
hi i need to do a loop inside a stored procedure, just not sure how to go about it!
this is what i need
select count(TI_ID) AS REC_COUNT
FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12
CASE WHEN REC_COUNT > 1 THEN
===LOOP THROUGH AND DELETE 1000 AT A TIME===
DELETE top 1000 FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12
END CASE
View 7 Replies
View Related
Jul 23, 2005
What I am trying to do is to get balances at each month-end from Jan toDec 2004. Now I am doing it by manually changing the date for eachmonth, but I want to do all the months at one time. Is there a way toadd something like a do loop to achieve that goal? Please see my querybelow. Thanks so much!declare @month_date_b smalldatetime--B month beginning datedeclare @month_date_e smalldatetime--E month ending dateselect @month_date_b='9/1/2004'select @month_date_e='9/30/2004'select a.person_id, a.fn_accno, a.fn_bal, b.mm_openfrom fn_mm_fnbal as ajoin fn_mm_list as bon a.person_id=b.person_idand b.mm_open < @month_date_ewhere a.bal_date between @month_date_b and @month_date_egroup by a.person_id, a.fn_accno, a.fn_bal, b.mm_openorder by a.fn_accno, a.fn_bal
View 2 Replies
View Related
Aug 17, 2005
helloDoes exist in SQL language "for" loop ? If yes, what syntax does it has ?best wishesAdam
View 9 Replies
View Related
May 9, 2007
Hello,
Please, could someone explain me how "For Loop" funtions?
because I need to load data depending on a variable?
Can I use For loop to do that?
thanks
View 1 Replies
View Related
Jul 24, 2006
I have a table which has an identity column. I want to traverse through the table one row at a time using FOR Loop. Can someone help me with the syntax.
View 5 Replies
View Related
Jan 8, 2008
Hi Experts
Can anyone please guide me in getting the max and minimum rows in a set while looping through the set. I know there is a way to use a while loop and preserve the value in a variable to get the max and minimum rows. Here is what I have:
Code Block
CREATE TABLE TstDbinfo (DBNAME VARCHAR(60),DBSize INT,Sqno INT)
Go
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('ADB1',2400,1)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('ADB2',2200,2)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('ADB3',2000,3)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('ADB4',1400,4)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('ADB5',1300,5)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB1',1200,6)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB2',900,7)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB3',200,8)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB4',200,9)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB5',150,10)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB6',10,11)
INSERT INTO TstDbinfo (DBName,DBsize,Sqno) VALUES ('DDB7',5,12)
What I am looking is for a solution like this:
Code Block
DBName DBSize Sqno
ADB1 2400 1
DDB7 5 12
ADB2 2200 2
DDB6 10 11
ADB3 2000 3
DDB5 150 10
ADB4 1400 4
DDB4 200 9
DDB3 200 8
ADB5 1300 5
DDB2 900 7
DDB1 1200 6
As you can see I need to get one Max and one Minimum record. I want to avoid the use of cursors but was wondering if this can be easily done using a while loop and looping through it to get the next max and min values. If I have two dbs of the same size then both of them should be displayed. Sqno is unique in the table.
Any Solutions are appreciated.
Thank you very much
Ankith
View 7 Replies
View Related
Apr 4, 2006
Hi,
I have a dataset created in a data flow task. I want use a for each loop in the control flow area of the package to process every row individually in the dataset. How can I do this?
I am currently using a "Recordset Destination" because it says that it creates and populates in memory ADO dataset and I read somewhere that this could be used anywhere in the package. I have even assigned a variable DS which has an object datatype to the Recordset Destination task. How do I use this now in the for each loop container in the Control Flow task? I also need to know if I can use the DataReader Destination task instead as it uses ADO.NET vs just ADO for performance reasons?
Please help me with the above problem as I am working on a demo.
Thank you!!
View 1 Replies
View Related
Aug 14, 2007
HI FRIENDS
WHAT IS THE USE OF FOREACH LOOP AND GIVE ME THE ONE EXAMPLE
REGARDS
KOTI
View 1 Replies
View Related
