I have a stored procedure that attempts to INSERT @BatchSize number of records at a time into a table. Currently, I have @BatchSize set to load 50,000 at a time. The table I am inserting from has a little over 67,000 records.
When I execute the procedure with NOCOUNT left off, the procedure seems to run indefinitely, and the count of records returned surpasses what I have in the source table. However, only 50,000 records are inserted into the table.
Below is my code:
begin try
--error catching variables:
declare @Error_NumberLocal int
,@Error_MessageLocal varchar(4000)
,@Error_SeverityLocal int
,@Error_StateLocal int
,@Error_ProcedureLocal varchar(200)
,@Error_LineLocal int
,@User_NameLocal varchar(200)
[code]....
What is the problem with the looping structure that would cause this issue?
I've got a table with a unique column, "id". I've got the id values of about 300,000 records. These records need to be DELETEd from this table. Is there a way to do this in batch? I can't imagine the only way to do it is:
DELETE FROM Table WHERE id = 1 OR id = 2 OR id = 3... OR id = 300000
SET NOCOUNT ON DECLARE @RowCount int; SET @RowCount = 0; Begin Try Begin Transaction -------------------------------------------------------- -----Table Name: AlertsStaticRecord_Archive -----Column Name: AlertID -------------------------------------------------------- ALTER TABLE [AlertsStaticRecord_Archive] ALTER COLUMN [AlertID] int NOT NULL;
[Code] .....
But, when I execute these batch, I am getting error:
Msg 8111, Level 16, State 1, Line 11 Cannot define PRIMARY KEY constraint on nullable column in table 'AlertsStaticRecord_Archive'. Msg 1750, Level 16, State 0, Line 11 Could not create constraint. See previous errors.
Because, the first ALTER statement is not getting executed.
During the execution of SSIS Package which is populating huge data into OLEDB Destination from OLEDB Source, then some of the records are getting rejected. Again if we are executing it twice or thrice, the rejected records are getting inserted.
Wants to know, why the records are getting rejected? Target table does not contain any constraints.
I’m looking for clearity on partition switching. The idea is to use many BULK INSERT statements into table dbo.X_n in parallel and when BULK INSERT for table dbo.X_n is completed, switch dbo.X_n into dbo.bigdaddy. I think this is the fastest way to upload a couple hundred GB of data.
In learning about partition switching (in part) from The Data Loading Performance Guide under Partition SWITCH, I hear the instructions to say copy the main table exactly to become a target. But in that same step (#1), I read that we need to change the default file group of the target (dbo.X_n) from the default file group. Then it says I need to match indexes and lists the filegroup as something we need to match with the main table.
As an overview of the partition switching strategy, I think the whole point of BULK INSERT with partitioning is to have seperate files (in same group) to enable concurrent uploading where each table has its own file. Once the upload is completed to a table (dbo.X_n) then we do the partition switch into the main table (dbo.bigdaddy). The data we just uploaded doesn’t actually move, just the metadata for it.
When I read the instructions linked above, I hear “Don’t have the same filegroup on your target as the main table. You must have the same filegroup on your target as the main table.”
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT Student.StudentSurname + ', ' + Student.StudentForename AS Fullname, HomeworkLogLine.HomeworkLogLineTimestamp, HomeworkLog.HomeworkLogDescription, ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
I have a big table which has fields(ID, RecordDate, Value). The records were 15 minutes interval, start at yyyy-mm-dd 00:00:00 and end at yyyy-mm-dd 23:45:00 for each day. Some records may be missing.how can I do the query:if the every 15 min records exists in the day, do sum(value) for the day else do something else.I would like see difference between SQL 2008 and 2012.
I have the following store procedure which list a certain amount of product
SELECT P.Name, P.ProductCode, P.Quantity, P.ItemUnitWeight, P.Weight, P.WeightUnitMeasureID, P.IsDeleted, UNIT.UnitMeasureCode, P.StoreID, dbo.v_ProductItemWeight.TotalWeight, dbo.v_ProductItemWeight.UsedWeight, Production.ProductCategory.Name AS CategoryName, Production.ProductCategory.ProductCategoryID, Production.ProductItem.Quantity AS UnitQuantity, Production.ProductItem.UsedQuantity FROM Production.Product AS P INNER JOIN
[Code] ....
This store procedure produce the following result set :
Result Set
From the result set above, in fact what I need to get is a single records for each ProductCode and adding 2 fiels which are the Sum(UNitQuantity) and the Sum(UsedQuantity)
How can I add those 2 field in my querry and group it by ProductCode ?
I will need to join the #InsuredLoc table to the #PolicyEndorsement table using PolGk and PolEdrGk and get the min(BkgDt) and min(PolEdrRowUpdateDt) for the distinct list of InsuredLocType, InsuredLocStatus, and InsuredLocAddress fields from the #InsuredLoc table above.
I will also need the min(InsuredLocEffDt) and the max(InsuredLocUpdateDt) from the #InsuredLoc table for those records. So after the first run, i should get the following:
I tried to use CTE's with ranking, but some records are dropping off and I'm not sure why.
In my database I have an Audit table, that keeps track of teams worked upon the same record in a workflow.I need to find out how many records have been returned to the first team for correction ?The column 'Status' is numbered from 1 to 6 and Column 'EditTime' saves the time when record has been edited.how many records have been returned for correction and identify those records.
IF OBJECT_ID('dbo.AuditLog','U') IS NULL BEGIN CREATE TABLE [dbo].[AuditLog]
[code]....
[AuditLogId] [bigint] IDENTITY(1,1) NOT NULL, [DateCreated] [datetime] NOT NULL, [TableName] [nvarchar](200) NULL,
i have the Audit_log table and the trigger above on Account table..it is working fine and i have some 1000 records in Audit log table , but it did not fire for some customers and i found trigger did not fire for 60 plus customers any possible reasons ??? that trigger did not fire ? Below are the timings account have been created but trigger did not log them
Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.
ID PersonID Date Type Qty 1 1 01/01/2011 Accept 5 (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis same) 3 2 02/01/2010 Accept 10 (show Accept row=3,ignore deny row)
I would like to use the following code for querying summary records with paging.
DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 1 SET @RowspPage = 10 SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY create_date) AS NUMBER, * FROM summary ) AS TBL WHERE NUMBER BETWEEN ((@PageNumber - 1) * @RowspPage + 1) AND (@PageNumber * @RowspPage) ORDER BY create_date
Paging is implemented for fast response since the data pool is very large up to 10000000.
The above query works fine in testing. However, in reality, since new records are keep inserting to the tables, I have concern about the accuracy of viewing another page of result.
E.g. At 12:00pm, the result of page 1 (5 per page) is R20, R19, R18, R17, R16
After 2 mins, 12:02pm, the user press next page button Since records R21, R22, R23, R24, R25, R26 are inserted page 2 result would be R21, R20, R19, R18, R17
So the result is showing many records same as page 1 which has already been seen. Could this situation be improved?
I have one table with many records in the table. Each time a record is entered the date the record was entered is also saved in the table. I need a query that will find all the missing records in the table. So if I have in my table:
ID Date Location 1 4/1/2015 bld1 2 4/2/2015 bld1 3 4/4/2015 bld1
I want to run a query like
Select Date, Location FROM [table] WHERE (Date Between '4/1/2015' and '4/4/2015') and (Location = bld1) WHERE Date not in (Select Date, Location FROM [table])
I have following query which is created dynamically as -
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1889147436' AND SEND_DATE = '1941-03-04'; UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1115509374' AND SEND_DATE = '1991-09-01'; UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1515579671' AND SEND_DATE = '1941-05-24'; UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1795509670' AND SEND_DATE = '1958-01-14'; UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1915508672' AND SEND_DATE = '1961-09-07';
Here till " UPDATE BUILDTABLE SET BUILD_ID = '984137'" is the same clause for all queries, but "where" condition is different for all queries. I have to update more than 500 UPDATE statements(like above) in one call. Currently I am concatenating all the queries in string Builder which is time consuming.I want to increase performance of application.Any other class like BulkCopy ?
Am witnessing a very strange issue when i try to insert some records . I get the message in the SSMS like 5,10 etc rows affected. When i check for the same records in the table none of them is present.
This problem then automatically goes away after some time and all get backs to normal. I only have the access to that database no other user have the access. Totally confused about the all of a sudden new behavior of the database.
My table is test and I have an ID and DateTest columns
I would like to count the weeks with more then one record.
So far I got this and return the weeks with 1 record per week. How can I count the weeks with more then one record
select sum(c) from ( select c = count( id) over (partition by id, datepart(week, DateTest)) from test where id = '1' and DateTest >= '7-7-2015' ) a where c = 1
I want to perform a query that should display the upcoming birthday of all students according to today.Lets say, today is 2015-10-02, so the query should display the result according to today's date just like below
ID StudentName BirthDate 1 ABC 2002-12-25 2 VWX 2002-01-01 3 STU 2002-02-03 4 PQR 2002-03-05
Means If ID is same for two or more than two records then difference between first row's EndDate and second row's StartDate is 1 day then we should get one record as output. How can we built this logic in T-SQL ?
I need to compare records between two tables. There is no ID in the tables to do a simple join between them. So, what I'm looking for is: get the first record from table1 and read all record from table2 and give me back the most similar record. The String Distance is a predefined function.
Select a.table1 ,b.table2 from table1 a, table2 b where StringDistance (''a.table1,'b.table2') >90
I would like to use sp_send_dbmail, but I only want to send mail if there are any records returned.
I have found some solutions, but you must always first check if there is any record and later you can call "sp_send_dbmail" and within you must again query database for results.
What I want to do is to query database just once, because I dont want to use server performance two times. Query is bit complicated.
Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)
If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.
Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.
pn = 4donot have sdate and edate and status values dut not fall conditon
select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate order by case when pr.status=2 then 1 when pr.status=4 then 2 when pr.status=3 then 3 when pr.status=1 then 4 end ,pr.sdate )pr
but this query not given expected result.here when dos not fall between sdate and edate that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.
I am trying to get count of records by month wise when they select year .It was showing the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...
SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month FROM CPTable where year(ReportDate) = '2015' GROUP BY Standard, Standard , month(ReportDate)
I have a table that "Geography" that has the following columns: city, state, zip
There are tons of duplicate cities in this table. I ran this query and it shows me the number of occurrences of each city. I want to delete all the duplicates except for 1. I don't want to do this manually as there are a lot of records.
What would the SQL look like to delete the duplicate records but keep at least one?
