UDF With Tally Table Runs Slower Than While Loop
Aug 24, 2015
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
[Code] .....
View 9 Replies
ADVERTISEMENT
Jul 23, 2005
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!!)
View 1 Replies
View Related
May 5, 2007
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
View 12 Replies
View Related
Apr 2, 2007
Hi
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?
View 17 Replies
View Related
Apr 23, 2014
I have a table that has the following structure:
EntryID int,
Categories varchar(200)
values look like:
541,'A,B,C'
345,'B,C'
234,'A,C'
657,'D,E'
435,'D'
what I want to do is extract the Categories column to a normalized separate table:
541,'A'
541,'B'
541,'C'
345,'B' ....
I found the split using the tally table useful to split one-by-one, but how can it be applied when you are referring to a table?
View 2 Replies
View Related
Dec 27, 2014
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.
DECLARE @START DATETIME,
@msg NVARCHAR(MAX) = N''
USE tempdb
SELECT @START = GETDATE()
CREATE TABLE dbo.EulerSource ( [SID] INT, Euler BIGINT )
[Code] ....
View 9 Replies
View Related
Dec 23, 2014
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
[Code]....
View 2 Replies
View Related
Jul 20, 2005
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
View 15 Replies
View Related
Mar 1, 2008
hi, friends, please look at this:
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!
View 11 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
Jan 12, 2007
Person # date tally
1 1/2/06 1
1 1/2/06 2
1 1/2/06 3
1 1/2/06 4
2 3/4/06 1
2 3/4/06 2
2 3/4/06 3
3 2/3/07 1
3 2/3/07 2
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.
View 5 Replies
View Related
Apr 23, 2008
Performance issue.
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.
View 15 Replies
View Related
Nov 14, 2014
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?
View 4 Replies
View Related
Oct 9, 2001
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
CREATE PROCEDURE dbo.sp_ValidateAIGL
@IGLPeriodIDInt,
@IGLProgramIDInt
AS
/* ************************************************** ************************
Name:sp_ValidateIGL
Author:CL
Date:19-Jan-2001
Notes:
************************************************** ************************ */
--SET NOCOUNT ON
DECLARE@TaxYearChar(5),
@FrequencyChar(1),
@PeriodNo Int,
@ProgramLogIDInt
SELECT@TaxYear = TaxYear,
@PeriodNo = PeriodNo,
@Frequency = Frequency
FROMtbl_IGLPeriods
WHEREIGLPeriodID = @IGLPeriodID
SELECT @ProgramLogID = (SELECT ProgramLogID FROM tbl_SYSProgramLogs WHERE IGLProgramID = @IGLProgramID AND IGLPeriodID = @IGLPeriodID)
CREATE TABLE #IGLErrors
(
KeyFieldChar(24),
ErrorIDInt,
DescriptionVarChar(255)
)
-- *** 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
View 2 Replies
View Related
Jun 25, 2015
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.
SQL Server 2014/Win7 x64.
View 7 Replies
View Related
Jul 21, 2007
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?
Any help would be much appreciated.
Thanks...
View 1 Replies
View Related
Jan 22, 2012
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.
View 1 Replies
View Related
May 18, 2004
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.
any threads that i can read from or website..
thanx !!
View 3 Replies
View Related
Jun 4, 2008
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
BEGIN TRANSACTION
EXEC pInsertPersonEX SELECT 1, 'Dr.', FirstName, LastName, Suffix, Email, CAST((LEFT(FirstName, 1) + LastName)as varbinary), 31, PrimarySpecialty, 'Student', REPLACE(REPLACE(REPLACE(OffPhone, '(',''),')',''),'-',''), NULL, 0, 0, NULL, Street, NULL, City, 41, Zip,3 FROM _Phy_Greenville
SELECT * FROM Person WHERE PersonOrganizationID = 31
ROLLBACK TRANSACTION
Here is the whole stored procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[pInsertPersonEx]
(
@Active bit,
@PersonPrefix varchar(5),
@PersonFirstName varchar(50),
@PersonLastName varchar(50),
@PersonSuffix varchar(20),
@PersonEmail varchar(100),
@PersonPassword varchar(20),
@PersonOrganizationID int,
@PersonDepartment varchar(50),
@PersonTitle varchar(100),
@PersonPhone varchar(10),
@PersonFax varchar(10),
@PersonInfoRequested bit,
@PersonHCTrained bit = NULL,
@PersonHCTrainedDate DateTime = NULL,
@AddressAddress1 varchar(100),
@AddressAddress2 varchar(100),
@AddressCity varchar(100),
@StateId int,
@AddressPostalCode varchar(10),
--@DepartmentID int,
@RoleID int
)
as
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
INSERT INTO tblHospitalsCoordinated
(
intPersonID
,intHospitalID
,dtmCreatedDate
,dtmModifiedDate
,strModifiedBy
)
VALUES
(
@PersonID
,@PersonOrganizationID
,GetDate()
,GetDate()
,''
)
select @PersonID
View 4 Replies
View Related
Feb 20, 2008
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++; }
}
View 1 Replies
View Related
Sep 10, 2012
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,
[Code] ......
View 3 Replies
View Related
Mar 12, 2008
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')
View 7 Replies
View Related
Aug 25, 2015
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.
View 2 Replies
View Related
Feb 18, 2015
I am trying to loop through a table which has table metadata and create a:
- 'STORED PROCEDURE'
- This will SELECT all the data in the table and add a hashed column at the end
- Each table has a unique ID
-
Instead of a cursor would there be a set-based approach to achieving this?
METADATA TABLE :
IDTableNameColumnNameHashByteCalculation
111dbo.TableAColA CAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),'NA') AS varchar) + '|' +
111dbo.TableAColBCAST(ISNULL(LEFT(CONVERT(VARCHAR,ColB, 120), 10),'NA') AS varchar) + '|' +
111dbo.TableAColCISNULL(ColC,'NA') + '|' +
111dbo.TableAColDISNULL(ColD,'NA') + '|' +
222dbo.TableBColAACAST(ISNULL(LEFT(CONVERT(VARCHAR,ColAA, 120), 10),'NA') AS varchar) + '|' +
222dbo.TableBColBBISNULL(ColBB,'NA') + '|' +
222dbo.TableBColCCISNULL(ColCC,'NA') + '|' +
From the above data I want to generate:
SELECT
ColA
,ColB
,ColC
,ColD
, (CAST(ISNULL(LEFT(CONVERT(VARCHARColA, 120), 10),'NA') AS varchar) + '|' +
[Code] ....
View 2 Replies
View Related
May 17, 2008
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?
View 4 Replies
View Related
Sep 20, 2013
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.
View 3 Replies
View Related
Feb 6, 2015
I wanted to insert values in columns as explained in below ex.
I am having a table that contains Column1,Column2,Column3,......,Column10.
Inside my for loop, i am getting Column1 value then Column2 then Column3 values and so on till Column10.
My requirement is that on each iteration,I wanted to insert value of Column1 in field Column1, value of Column2 in field Column2 and so on.
View 3 Replies
View Related
Feb 19, 2015
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]
Don’t know if this is at all possible?
View 2 Replies
View Related
Sep 21, 2005
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.
View 4 Replies
View Related
Jul 20, 2005
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.
View 1 Replies
View Related
May 1, 2007
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?
View 9 Replies
View Related
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
Jul 31, 2007
Hello,
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.
View 5 Replies
View Related
