I'm migrating electronic records from a legacy system and the new system has strict requirements for ASCII characters in certain metadata fields. I wrote a UDF to display illegal characters, so I can work out how to map them.
The UDF used a while loop and to improve performance, I wrote the equivalent UDF using a tally table. The tally table version actually ran significantly slower. Query calling UDF using cross apply took 26 secsfor the while loop versus 119 secs for Tally table, for test data of 97000 rows
I would like to work out why, as I will use similar code to replace the illegal characters .
-- while loop version of UDF
CREATE FUNCTION [dbo].[DisplayIllegalChars](@strText VARCHAR(4000))
RETURNS @TableVariable TABLE (
Chr CHAR(1)
,AsciiValue INT)
AS
BEGIN
DECLARE @intCount INT
DECLARE @chrCheck CHAR
I have a query I developed and optimised as well as possible beforeconverting to a view.When running in query analyser the query runs in 15 to 18 seconds -which is acceptable.When "converting" into a view ( This is necessary for operationalreasons) and running with the same parameter it runs in 3 to 4minutes.Is there something I am unaware of ( well of course there is !!) -I was wondering why this occurs and how I can avoid / correct theissue.All advice gratefully received.Dave ( Still learning stuff about SQL Server every day!!)
We're on SQL 2005 SP1, IIS6, ASP.NET 2.0Data access is 3 tier (.xsd, BLL) similar to the data tutorials.From SQL Profiler:ASP.NETexec dbo.SpGetCatalogCategories @Show='Children',@ServiceId=31016,@BrandId=NULL,@CategoryId=NULL,@ParentId=10028,@ApplicationId=NULL,@ShowUniversal=1Event Class: RPC:Completed CPU: 5,109Reads: 613,174Writes: 0Duration: 5,133Management Studioexec dbo.SpGetCatalogCategories @Show='Children',@ServiceId=31016,@BrandId=NULL,@CategoryId=NULL,@ParentId=10028,@ApplicationId=NULL,@ShowUniversal=1Event Class: SQL:BatchCompletedCPU: 407Reads: 2,182Writes: 0Duration: 409I can post the SP if it will help.This is in a production enviroment and any help is greatly appreciated.kyle
I have a for each loop which steps through an ado recordset (approx. 5,000 rows), this passes two variables to an SQL statement which populates a second recordset (normally 8 to 10 rows). I use the second recordset in a dataflow task which was a simple Script which returns approximately 30 rows for inclusion in my destination table. The package runs for a while OK, although the loop appears to execute slowly, then I get the below message constantly repeated in the debug window.
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 174 buffers were considered and 174 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked. I have 2gb of virtual memory on my machine, and the recordsets are relatively small. Have I missed a seting some where?
I have to create a table like this across a bunch of servers. I'm thinking that I'm overlooking something with needing two additional CTEs, but maybe not. I have it at 17 seconds, which isn't much faster than a while loop solution that's currently in place.
I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...
IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL DROP TABLE #tTable CREATE TABLE #tTable
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
I have a SSIS package, and inside it I do something like below:
1. I have a SQL component, to give back a object to store the records. 2. I have a VB script component, I direct the object I got in 1 step into the script as a dataset.
My problem is: I run the package in the SQL SERVER 2005 Store Procedue like this:
do dtexec.exe package.dtsx loop untill i>t
I control the it runs 30 times. But I found that the speed is slower and slower. the first time, it takes about 600 s, but the last time, it takes the 1800 s.
Why? The package don't drop the object it create during the loop in the Store Procedue ? Thanks!
A series of readings were taken per person #. I need to create a tally from the person # column and the date column. I already have person # = to the individual readings.
I have a very complex Stored Procedure called by a Job that is Scheduled to run every night. It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.
So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.
Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.
Some more information: - It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node). - The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access. - When I connect to a Query Window I also use a Windows Account.
- There is no locks or process bloking or being blocked while the job is running. - Using the Task Manager the processor activity is ok, no more than 30 % in any processor.
I have a client that I provide financial modelling services to (using Excel). They have a requirement to start capturing subscriber movements in their SQL DB. how the table should be set-up and how to extract the necessary movements report. This is largely so that I may include these components in some of the financial models that I am working on.
Subscribers are reported as follows:
Opening subs (the prior periods closing balance; or the sum of new sales at point of 1st entry)
+ New Sales (new subs)
+ Upgrades (movement from a lower product package to the associated package)
- Downgrades (movement to a lower product package from the associated package)
- Churn (subscriber losses)
Closing Balance
All transactions are captured against a specific product package, on a specific date (ymd), and for an associated platform (e.g. digital TV, broadband TV, cable TV).
I believe we only need to capture new sales, upgrades, downgrades and churn. And then used a SP to compile the movements behaviour as described above.
So perhaps the table would appear as follows:
Platform Package Date Movement Value
DTV PROD 1 2014-11-02 New Sales 8
DTV PROD 1 2014-11-02 Upgrades 1
[code]....
So I am assuming that given a table such as the above, we could write a SP to produce an output such as (note, below looks at monthly total so will not agree back to sample above which contains only 2 days):
Platform Package Movement September October November
DTV PROD 1 OPEN 600 676 776
DTV PROD 1 New Sales 92 106 88
[code]....
how one is best to accumulate the balances given that the open date for any given reporting period is in fact an accumulation of all balances since day 1.
How does one typically capture this type of thing in SQL?
I have a stored proceedure (which I will tag on at the end for those interested) which is taking at least 15 minutes to run when executed, but completes in 1 minute when the tsql statement is run in Query Analyser. Why is this?
I suspect that it may be connected to table indexing, but why then is this bypassed when QA is used?
Any advice appreciated.
Derek
************************************************** *********************** IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_ValidateAIGL') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE dbo.sp_ValidateAIGL GO
-- *** Global Non Program Specific Data Errors *** -- CHECK - that there are records in the DEB_IGL_PAYROLL_OUTPUT file.....none and the routine failed... IF NOT EXISTS(SELECT * FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID) INSERT INTO #IGLErrors SELECT NULL, 100, 'No records were processed by the IGL run!'
SELECT * FROM #IGLErrors
-- CHECK - search for any records where the employee's EXPENSE_CODE is NULL INSERT INTO #IGLErrors SELECT DISTINCT NULLIF(EmpNo, ''), 2, 'Employee "' + COALESCE(NULLIF(RTRIM(EmpNo), ''), '<Missing Employee>') + '" (Organisation Unit - ' + COALESCE(RTRIM(OrgUnitCode), '<No Organisation Unit>') + ') does not have a EXPENSE_CODE Code.' FROM tbl_OUT_Payroll WHERE NULLIF(ExpenseCode, '') IS NULL ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors -- CHECK - check that the BALANCE of DEBITs match the balance of CREDITs IF (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 1 AND IGLProgramID = @IGLProgramID) <> (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 0 AND IGLProgramID = @IGLProgramID) INSERT INTO #IGLErrors SELECT NULL, 3, 'The total cash value for DEBIT elements does not match the total cash for CREDIT elements.'
SELECT * FROM #IGLErrors -- *** Program 1 and 2 errors *** IF @IGLProgramID IN (1, 2) BEGIN -- CHECK - search for any records where the employee's COST_CENTRE is NULL INSERT INTO #IGLErrors SELECT DISTINCT NULLIF(EmpNo, ''), 1, 'Employee "' + NULLIF(RTRIM(EmpNo), '') + '" (Organisation Unit = ' + RTRIM(OrgUnitCode) + ') does not have a COST_CENTRE Code.' FROM tbl_OUT_Payroll WHERE NULLIF(CostCenter, '') IS NULL ANDIGLProgramID = @IGLProgramID
SELECT * FROM #IGLErrors
-- Check for EMPLOYEEs that were not transfered to the PAYROLL output (usually caused by missing ORG_UNITs or not picked up in -- the DEB_VIEW_APPOINTEE view...) INSERT INTO #IGLErrors SELECT DISTINCT EMP_NO, 11, 'Employee "' + RTRIM(EMP_NO) + '" was excluded from the summary. Check their Organisation Unit codes!' FROM PSELive.dbo.COSTING_OUTPUT WHERENOT EMP_NO IN (SELECT DISTINCT EmpNo FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID) ANDPERIOD_NO = @PeriodNo ANDTAX_YEAR = @TaxYear
SELECT * FROM #IGLErrors
-- Check that there are no ELEMENTS in the COSTING_OUTPUT table that don't exist in the tbl_IGLElements table INSERT INTO #IGLErrors SELECT DISTINCT ELEMENT, 12, 'Element "' + RTRIM(ELEMENT) + '" does not exist in the IGL Interface Elements table!' FROM PSELive.dbo.COSTING_OUTPUT WHERE ELEMENT NOT IN ( SELECT DISTINCT Element FROM tbl_IGLElements ) ANDPERIOD_NO = @PeriodNo
SELECT * FROM #IGLErrors
END
-- *** Add a error to indicate the number of errors *** IF EXISTS (SELECT * FROM #IGLErrors) INSERT INTO #IGLErrors SELECT 0, 0, 'Warning, there are ' + CAST(Count(*) AS VarChar(5)) + ' recorded errors!' FROM#IGLErrors
-- Transfer the records to the ErrorsLog table ready for the user to view... DELETE FROM tbl_SYSErrorsLog INSERT INTO tbl_SYSErrorsLog (IGLProgramID, OutputLogID, KeyField, ErrorID, Description) SELECT@ProgramLogID, @IGLPeriodID, KeyField, ErrorID, Description FROM #IGLErrors ORDER BY ErrorID
DROP TABLE #IGLErrors
SELECT * FROM tbl_SYSErrorsLog ORDER BY ErrorID
--SET NOCOUNT OFF
GO GRANT EXECUTE ON dbo.sp_ValidateAIGL TO Public GO
I have a job scheduled that imports a table from a Oracle database. The job runs at 3am and reports success. But for some reason when i query the table to see how many records there are, I see the same row count as the day before (it should increase everyday- student enrollment). When i execute the package manually, the table updates fine.
I am copying a simple table from a Sql Server 2005 database to an *.sdf mobile database.
I am brand new to SSIS and I am probably doing something wrong. But after executing the SSIS package all the rows and all the fields are NULL in the destination database. I put a datagrid viewer between the OLE DB Source and the Sql Server compact edition destination and I can see the real data which is obviously not ALL NULL.
Does anyone have a clue as to why it would be doing this?
I have a table that stores a couple of tablenames in the same db.The tablenames in the table can change from time to time.Is it possible to loop through the tablenames in the table and run a query against each table name. I cannot hard code the table names in the query because they can change from time to time.
Is there a way to loop using a cursor in SQL-server so i can see if each columns of each tables that i loop through my DB have a specific string value and change it to something else, renaming the column if the match if correct.
I have a table called _phy_greenville. Its a table that was imported from an excel file. I need to take the values in this table, and pass them to the following stored procedure. This stored proc create the physician record correctly for me when doing one record at a time. What I need to do is pass ALL record in this table (_phy_greenville) into this stored proc. Is there anyway I can loop through, or do some sort of bulk insert?
I tried the following, but obviously this does not work
IF @PersonHCTrainedDate = '01/05/1900' SET @PersonHCTrainedDate = NULL
set nocount on
declare @PersonId int, @AddressTypeID int
insertPerson ( Active, PersonPrefix, PersonFirstName, PersonLastName, PersonSuffix, PersonEmail, PersonPassword, PersonOrganizationID, PersonDepartment, PersonRegistrationDate, PersonLicenseAgreement, PersonTitle, PersonPhone, PersonFax, PersonInfoRequested, PersonHCTrained, PersonHCTrainedDate ) values ( @Active, @PersonPrefix, @PersonFirstName, @PersonLastName, @PersonSuffix, @PersonEmail, convert(varbinary,@PersonPassword), @PersonOrganizationID, @PersonDepartment, getdate(), 0,/* TODO need to get this from form */ @PersonTitle, @PersonPhone, @PersonFax, @PersonInfoRequested, @PersonHCTrained, @PersonHCTrainedDate )
set @PersonID = IDENT_CURRENT('Person')
/* look up the default address type */ select@AddressTypeID = AddressTypeID fromAddressType whereAddressTypeDisplayName = 'Work'
execpInsertPersonAddress @AddressAddress1, @AddressAddress2, @AddressCity, @AddressPostalCode, @StateID, @PersonID, @AddressTypeID, 1/* this proc always inserts the default address */
/* - Schema change. Department now a varchar execpInsertPersonDepartment @PersonID, @DepartmentID, 1/* this proc always inserts the default department */ */ execpInsertPersonRole @PersonID, @RoleID
I have a table of CategoryIDs and I want to increment through it passing each categoryID as a parameter to a stored procedure that returns the TOP 1 row, and build a new table from the the result. I do not know how to pass the categoryID into the stored procedure. I do not know how to begin building the new table. public DataTable GetContractorCats() { // Generates table of all Contractor Category IDs no parameters SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings ["ConnectionString2"].ConnectionString); SqlCommand cmd = new SqlCommand("GetContractorCatIDs", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); try { da.Fill(ds, "ContractorCats"); return ds.Tables["ContractorCats"]; } catch { throw new ApplicationException("Data error"); } }protected void Top1EachCategory(){ // need to build a new table row by row using a stored procedure that finds the top result from a database query int RowIncrement; RowIncrement = 0; DataTable dt1 = GetContractorCats(); foreach (DataRow row in dt1.Rows) { I need to pass value of dt1 table ["CG_ID"] as a parameter to Stored Procedure called "GetTop1" Run the stored procedure which returns a single row if the CG_ID is found and use its field values to build each row in a new table. The stored procedure returns C_Name, Category_Name, C_Email, C_RCode and C_City RowIncrement++; }
I have a table with AmountSold and AmountLeftWith. I have to buy from the customers until the amount bought =250,000.
The max that user can buy is 250,000 so customers 1-3 get left with 0 (AmountLeftWith ) and customer 4 with 577 (AmountLeftWith ) after the update as user couldn't buy the entire amount as it would have exceeded 250,000. Preferably the query should stop afterwards and not proceed to check the other customers.
--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#tmpCustomerAmount','U') IS NOT NULL DROP TABLE #tmpCustomerAmount CREATE TABLE #tmpCustomerAmount ( [id] [int] IDENTITY(1,1) NOT NULL,
I'd like to loop through a database and get all the user table names and insert them into another table. What's the best way to do this without using a cursor? This gives me the last table only.
declare @table_name char(50)
select @table_name = object_name(id) from sysindexes where indid =0
INSERT INTO holding.dbo.tbl_inputfiles (IP_File, IP_Act, IP_Import) Values (@table_name,'Y','ADV')
I was writing a query to get the age and the retirement year for all the employees.And thought of using while loop so that I don't have to write IF conditions or case statements for all the ages.
I'm using the AdventureWorks2012 database.And the actual table looks like this.
SELECT * FROM HumanResources.Employee
*NOTE:- These tables are not the complete tables.
BusinessEntityID JobTitle BirthDate MaritalStatus Gender 1 Chief Executive Officer 1963-03-02 S M 2 Vice President of Engineering 1965-09-01 S F 3 Engineering Manager 1968-12-13 M M 4 Senior Tool Designer 1969-01-23 S M
[Code] ...
And after I wrote the query to get the age and the retirement year of all the employees I got 70 tables for all the ages from 30 to 70. As the starting age is 30 and the last age is 70 in the table.So,I just want to know how I can settle all the tables into a single table as a sinle result and not as multiple results.
The query for age and retirement year....
DECLARE @Counter INT DECLARE @Duration INT DECLARE @Result DATE SET @Counter=(SELECT MIN(DATEDIFF(YY,BirthDate,GETDATE()))FROM HumanResources.Employee) SET @Duration=30
[Code] .....
And the result tables.
BusinessEntityID JobTitle BirthDate AGE MaritalStatus Gender Retirement Year69 Production Technician - WC60 1985-05-07 30 S M 2045-08-25 22:36:38.160115 Production Technician - WC50 1985-07-01 30 S F 2045-08-25 22:36:38.160133 Production Technician - WC40 1985-02-04 30 S M 2045-08-25 22:36:38.160144
[Code] ....
And it goes like this for 70 times. So just want to know how I can merge those 70 tables into a single table.
Hi, how do I loop through a table in a store procedure? I need to check the all the record in a table and do some logic and then insert or update another table base on the logic?
I want to make a SP to update table Product with information I get from table Orderdetail.
Create Procedure UpdateVoorraad §OrderId (int) As Select ProductId, Tal From Orderdetail where OrderId = @OrderId
-- this query get info from table orderdetail : ProductId (integer) and Tal (smallint)
-- Tal = Number of Products
-- Here I want to loop through the query above
-- and for each record in the query I want to update
-- table Product.
Update Product Set Product.Voorraad = Product.Voorraad - Tal where ProductId = ProductId
To do this must I make a create a tempory table, store the query result in the table loop through the table and update table product, or can I try to create a function without a temporary table.
I want to, for each month of the year 2014 say, to create a loop that will enter data into a table.
Right now I have:
Select [Member Number], sum(case when [Receipt Date]='2014/01/01' then Amount else 0 end) as [Rec 2014/01/01] From [Receipts Table] Group by [Member Number] Insert into [Receipts 2014/01/01]
[Code] ....
Instead I would just like to do something like…
Declare i date For i=2014/01/01 to 2014/12/01
Select [Member Number], sum(case when [Receipt Date]=i then Amount else 0 end) as [Rec +i]
From [Receipts Table] Group by [Member Number] Insert into [Receipts + i]
Hi. It seems to be very simple, actually, but I don't know if it isfeasible in TSQL. I have a sproc which gathers in one place many callsto different other sprocs, all of them taking a 'StoreGroupe'parameter. I would like to add a case where if the call has NOStoreGroupe parameter, the sproc should LOOP thru all records in tableStoreGroupeTable, read the column StoreCode, and pass that value as aparam to the other sprocs, as in:CREATE PROCEDURE MySproc(@StoreGroupe nvarchar(6) = NULL)ASif (@StoreGroupe is not null)BeginExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............EndElseBeginA 'Group Code' has NOT been specifiedI want to take all the StoreGroups in tableStoreGroupeTable, in turn.I would like to do SOMETHING LIKE THIS:Do While not [StoreGroupeTable].EOFRead [Code] from [StoreGroupeTable]Set @StoreGroupe = The value I just readExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............LoopEndGOIs that feasible in a sproc, or do I have to do this in the client(ADO) ?Thanks a lot.Alex.
Hello,Does anyone know of a way to loop thru a SQL table using code in a storedprocedure?I need to go thru each record in a small table and build a string usingvalues from the fields associated with a part number, and I can't find anyway to process each record individually. The string needs to be initializedwith the data associated with the 1st record's part number, and I need tobuild the string until a new part number is incurred. Once a new part numberis found in the table, the string is written to a different table and resetfor this next part number in the table. Need to repeat until all records inthe table have been processed.I use ADO in access 2000 to work thru local recordsets, I just can't findanyway to do this in a stored SQL procedure.Thanks for any suggestions, Eric.
I would like to loop through a SQL Server table that contains the paths to all the reports we need to run and then execute the reports via SSIS. What task should I be doing to do this? Will the For Loop work for something like this?
Anyone have any suggestions on creating a query that will randomly select records from a table, but not use those records again. I have some code that does it, but it uses the same fields over again, and also throws in some blank records that I did not specify in the query. I am creating a test engine that has to randomly ask questions.
