Hi
I have a problem where I must compair an import table with a local datatable and import rows that are missing and correct the rows that are different.
How to best do this?
I was hoping to avoid cursors
thanks
Walter
Hi Frinds,
How can i find out the no of rows in each table. if the no of table are more than thousands(i.e. 1000+,2000+ etc..) are there. Certainly 'select count(0) from <table>' is not the good idea. Egarly awaiting for the solution.
thanks
Nageswara Rao Bomma
nrbomma@yahoo.com
nrbomma@hotmail.com
I try to find the number of rows in a table with this commands:
CountRec = New SqlParameterCountRec.ParameterName = "@countrec"CountRec.SqlDbType = SqlDbType.IntCountRec.Value = 0MyCommand = New Data.SqlClient.SqlCommand()MyCommand.CommandText = "select count(*) as @countrec from Customer;"MyCommand.CommandType = Data.CommandType.TextMyCommand.Connection = MyConnectionMyCommand.Parameters.Add(CountRec)MyCommand.Connection.Open()MyReader = MyCommand.ExecuteReaderiRecordCount = CountRec.Value
This is the result:
Incorrect syntax near '@countrec'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '@countrec'.Source Error:
Line 39:
Line 40: MyCommand.Connection.Open()
Line 41: MyReader = MyCommand.ExecuteReader
Line 42: iRecordCount = CountRec.Value
Line 43:
Source File: E:DevelopWebASPwebAccessTimberSalesUserEntry.aspx.vb Line: 41
What to do? I need a complete example to see how it works.
Thanks...
I need to find the rows that exist in one table but not in the otherwith this condition:(prod_name exist in table1 and not in table2.prod_name ) AND(prod_name exist in table1 and not in table2.'S'+prod_name )explanation:i want to know if the product not exit and if the combination of thecharachter "S" with the product Name also not exist at the othertableB.Ryuvi
View 2 Replies View RelatedHello All,
I have one table that contains information about invoices.
However I need to find out if invoice numbers have been used more than once.
I've tried to join the table to itself and find matching numbers, however I can't seem to get it to work properly.
Any help will be appreciated.
Thanks,
Can't seem to make this SQL query work!
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row
So, given the data
**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.
I have tried this and it doesn't work...
SELECT t1.Key1, t1.Key2, t1.Type
FROM Table1 t1
WHERE t1.Key2 IS NULL
AND t1.Type LIKE 'TypeA'
AND t1.Key1 NOT IN
(SELECT Key1
FROM Table1 t2
WHERE t1.Key1 = t2.Key2
AND t1.Key1 <> t2.Key1
AND t2.Type LIKE 'TypeB')
How to find the equal and opposite rows in a table.
E.g.:
book position
A 500
B -500
C -500
The output should return the rows having equal and opposite positions.
O/P:
A 500
B -500
I have tried self join on a table but in vain.Do we need to cursors for these row level handling?
which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query
View 10 Replies View RelatedHow can we monitor the all tables in all databases and send notifications to the team.Is there a way to check to find the no of rows and size of a table last month and find out growth % now
View 4 Replies View RelatedI have 3 database say
db1
db2
db3
I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)
Hi,
I forgot the syntax to to a self-join to find all rows that have
duplicate values. Could someone post the answer to the following example:
Column1
-----
x
y
z
x
After the select statment runs "x" would be the output.
Thanks,
Ken
Yesterday, I had an occurence where someone (one of our developers :) ) deleted many vital rows in a database. I was able to recover via a backup but I'd like to see what was run to delete those rows, when and who if possible? I have the transaction log and the _log.ldf.
Can I read those somehow to find out this information? I have since added auditing to said tables, but I'd really like to go back and see what happened?
I do know about Lumigent, but is there a different production/solution?
Thoughts?
Thank you
I would like to locate duplicate rows within a specific table. This table has 12 diffrent rows.
BASENO - POSITION - SEQ - PROD -STYL - DESCR - FIELD01 THRU FIELD05 - VALUE01 THRU VALUE05 - FORMTYPE - ANSWER
I have been playing with the following query but can't seem to get it perfect to locate my dups within sql. Can someone help me with the querry?
I'm playing with the following querry.
SELECT
<list of all columns>
FROM
tablename
GROUP BY
<list of all columns>
HAVING
Count(*) > 1
Can somone possible input my column names into this querry that would possibly get it to locate my dups? I'm missing somehting and not sure what.
Thanks for any help
SQL Newbie
I have this data as below. I need to find out the combination from the data and take a count of them
CREATE TABLE A
( nRef INT,
nOrd INT,
Token INT,
nML INT,
nNode INT,
sSymbol VARCHAR(50),
[code].....
if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed.
Hence the desired output will be
Symbol Count
Castorseed-Silver 8
How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.
I'm using this to find duplicates where a person has the same email but varying firstname and lastnames:
select distinct t1.booking_id, t1.first_name, t1.last_name, t1.email_add, t1.booking_status_id
from [aren1002].[BOOKING]
as t1 inner join [aren1002].[BOOKING]
as t2
on t1.last_name=t2.last_name and t1.booking_id<>t2.booking_id
where t1.booking_status_id = 330
order by last_name asc
Sample data:
3927 Greg Smith greg@emailno1.com 303
5012 John Smith greg@emailno1.com 303
6233 John Smith greg@emailno1.com 303
4880 Dulcie Abuud dulcie@theiremail.com 303
However it is listing the non duplicate rows, For example: The record with Abuud as the last name, doesn't have any duplicates in the table, so I don't want it listed.
The data should be like this:
3927 Greg Smith greg@emailno1.com 303
5012 John Smith greg@emailno1.com 303
6233 John Smith greg@emailno1.com 303
Given an ID (column B), I need to find which IDs have identical data.That is, given '200', I want the desired result to be:100The idea is that the system sees that id=200 has 5 records with theindicated data in cols C and D.It should then find any other ids with the exact same data for thosecolumns.Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,40:5) so they match. 300 and 400 should NOT be returned.Any bright ideas out there? Thanks!DECLARE @a TABLE(A int, B int, C int, D int)DECLARE @b TABLE(A int, B int, C int, D int)INSERT INTO @a (A, B, C, D) VALUES (1, 100, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (2, 100, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (3, 100, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (4, 100, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (5, 100, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (6, 200, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (7, 200, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (8, 200, 30, 3)INSERT INTO @a (A, B, C, D) VALUES (9, 200, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (10, 200, 40, 5)INSERT INTO @a (A, B, C, D) VALUES (11, 300, 30, 1)INSERT INTO @a (A, B, C, D) VALUES (12, 300, 30, 2)INSERT INTO @a (A, B, C, D) VALUES (13, 300, 40, 3)INSERT INTO @a (A, B, C, D) VALUES (14, 400, 40, 4)INSERT INTO @a (A, B, C, D) VALUES (15, 400, 40, 5)SELECT * FROM @a
View 7 Replies View RelatedHello,I need to find students that have 4 consecutive absences. When astudent is absent 4 times in a row, they can be dropped from the class.My class attendance file contains each attendance by date and whetherthey were present or not. When the student has 4 consecutive value 1(absent) for a given session and a given class the are considered to bedropped.If I needed to know the total number of absences, I know I could groupand summarize, but this one has the consecutive twist.Table:CREATE TABLE "dbo"."clsatt"("FULL_CLASS_ID" CHAR(15) NOT NULL,"STUDENT_ID" CHAR(20) NULL,"SESSION_ID" CHAR(10) NULL,"MEETING" SMALLINT NOT NULL,"PRESENT" CHAR(2) NOT NULL);Present value of 1 is absent, value of 2 is present (3 means holiday)Classes typically meet 12 times.I would want something likeFULL_CLASS_ID, STUDENT_ID, SESSION_ID, 'Dropped'as the output.Notice in the example the first student was absent the last 4 meetingsThe second student 5 absensesand the third student was totally absentIn these three examples, they are flagged as dropped.TIARobInserts:---------------------------------------------------------------------------------insert into clsatt values ('BUS100','1675812194','200203',1,'2')insert into clsatt values ('BUS100','1675812194','200203',2,'2')insert into clsatt values ('BUS100','1675812194','200203',3,'2')insert into clsatt values ('BUS100','1675812194','200203',4,'2')insert into clsatt values ('BUS100','1675812194','200203',5,'2')insert into clsatt values ('BUS100','1675812194','200203',6,'2')insert into clsatt values ('BUS100','1675812194','200203',7,'2')insert into clsatt values ('BUS100','1675812194','200203',8,'2')insert into clsatt values ('BUS100','1675812194','200203',9,'1')insert into clsatt values ('BUS100','1675812194','200203',10,'1')insert into clsatt values ('BUS100','1675812194','200203',11,'1')insert into clsatt values ('BUS100','1675812194','200203',12,'1')insert into clsatt values ('BUS100','1712400537','200203',1,'2')insert into clsatt values ('BUS100','1712400537','200203',2,'2')insert into clsatt values ('BUS100','1712400537','200203',3,'2')insert into clsatt values ('BUS100','1712400537','200203',4,'2')insert into clsatt values ('BUS100','1712400537','200203',5,'2')insert into clsatt values ('BUS100','1712400537','200203',6,'2')insert into clsatt values ('BUS100','1712400537','200203',7,'2')insert into clsatt values ('BUS100','1712400537','200203',8,'1')insert into clsatt values ('BUS100','1712400537','200203',9,'1')insert into clsatt values ('BUS100','1712400537','200203',10,'1')insert into clsatt values ('BUS100','1712400537','200203',11,'1')insert into clsatt values ('BUS100','1712400537','200203',12,'1')insert into clsatt values ('BUS100','1801704805','200203',1,'1')insert into clsatt values ('BUS100','1801704805','200203',2,'1')insert into clsatt values ('BUS100','1801704805','200203',3,'1')insert into clsatt values ('BUS100','1801704805','200203',4,'1')insert into clsatt values ('BUS100','1801704805','200203',5,'1')insert into clsatt values ('BUS100','1801704805','200203',6,'1')insert into clsatt values ('BUS100','1801704805','200203',7,'1')insert into clsatt values ('BUS100','1801704805','200203',8,'1')insert into clsatt values ('BUS100','1801704805','200203',9,'1')insert into clsatt values ('BUS100','1801704805','200203',10,'1')insert into clsatt values ('BUS100','1801704805','200203',11,'1')insert into clsatt values ('BUS100','1801704805','200203',12,'1')insert into clsatt values ('BUS100','1922287588','200203',1,'1')insert into clsatt values ('BUS100','1922287588','200203',2,'1')insert into clsatt values ('BUS100','1922287588','200203',3,'2')insert into clsatt values ('BUS100','1922287588','200203',4,'2')insert into clsatt values ('BUS100','1922287588','200203',5,'2')insert into clsatt values ('BUS100','1922287588','200203',6,'2')insert into clsatt values ('BUS100','1922287588','200203',7,'2')insert into clsatt values ('BUS100','1922287588','200203',8,'2')insert into clsatt values ('BUS100','1922287588','200203',9,'2')insert into clsatt values ('BUS100','1922287588','200203',10,'2')insert into clsatt values ('BUS100','1922287588','200203',11,'1')insert into clsatt values ('BUS100','1922287588','200203',12,'2')insert into clsatt values ('BUS100','2188469657','200203',1,'1')insert into clsatt values ('BUS100','2188469657','200203',2,'1')insert into clsatt values ('BUS100','2188469657','200203',3,'2')insert into clsatt values ('BUS100','2188469657','200203',4,'2')insert into clsatt values ('BUS100','2188469657','200203',5,'2')insert into clsatt values ('BUS100','2188469657','200203',6,'2')insert into clsatt values ('BUS100','2188469657','200203',7,'2')insert into clsatt values ('BUS100','2188469657','200203',8,'2')insert into clsatt values ('BUS100','2188469657','200203',9,'1')insert into clsatt values ('BUS100','2188469657','200203',10,'1')insert into clsatt values ('BUS100','2188469657','200203',11,'1')insert into clsatt values ('BUS100','2188469657','200203',12,'2')insert into clsatt values ('BUS100','2515197431','200203',1,'1')insert into clsatt values ('BUS100','2515197431','200203',2,'1')insert into clsatt values ('BUS100','2515197431','200203',3,'2')insert into clsatt values ('BUS100','2515197431','200203',4,'2')insert into clsatt values ('BUS100','2515197431','200203',5,'1')insert into clsatt values ('BUS100','2515197431','200203',6,'2')insert into clsatt values ('BUS100','2515197431','200203',7,'2')insert into clsatt values ('BUS100','2515197431','200203',8,'1')insert into clsatt values ('BUS100','2515197431','200203',9,'2')insert into clsatt values ('BUS100','2515197431','200203',10,'2')insert into clsatt values ('BUS100','2515197431','200203',11,'1')insert into clsatt values ('BUS100','2515197431','200203',12,'2')
View 6 Replies View RelatedI have rows of data that have a datetime stamp. I need to find rowsthat have a datetime stamp within 10 seconds of the top of each hour.I started with datediff(s,getdate(),LeadDate) but I am stumped on howto process for each hour.I.E. records between 9:00:00 and 9:00:10 , 10:00:00 10:00:10, and soforth for each hour.Ideas?
View 1 Replies View RelatedI have this data as below. I need to find out the combination from the data and take a count of them
CREATE TABLE A
( nRef INT,
nOrd INT,
Token INT,
nML INT,
nNode INT,
sSymbol VARCHAR(50),
nMessageCode INT
)
INSERT INTO A
( nReferenceNumber,nOrderNumber,nTokenNumber,nML,nNode,sSymbol,nMessageCode )
VALUES
(1, 101, 1001,0,2,'SILVER',13073),
[code]....
if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed. How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.
How Do we find how many ROWS REPLICATED to subscriber at specific interval ?
View 3 Replies View RelatedHow do I find the rows that have more than two decimal numbers after the decimal point for example 2.787686
I am trying :
select amount, LEN(AMOUNT) - CHARINDEX('.', amount) as DecimalCount from GL_REPORT
but is not working.
What would be the most economy solution for this WHERE, I see the code where we have covnert to 101 type on both sides of equation, which I tnink is not right.
So I'm thinking to put it on the left like this, just curiouse is this the best solution, I also heard that TSQL interpret between even better , here I'm really care abour performance.
declare @DATE DATE = '01/14/2014'
--A:
SELECT * FROM TT
WHERE CAST( TT.DATETIME AS DATE) = @DATE
--B:
SELECT * FROM TT
WHERE TT.DATETIME >= @DATE and TT.DATETIME < DATEADD(D,1,@DATE)
How would I compare 2 rows to find the difference in the columns?
Example:
ID Column1 Column2 Column3
1 Text1 Text4
2 Text1 Text2 Text3
Result:
Column1: Text2 New
Column3: Text4 Old Text3 New
In the FLAT FILE source, I have to find the duplicate rows based on the two fields say, "bill number" & "invoice date".
The rows within flat file has like "bill number" which is duplicated on the same "invoice date".
If duplicate rows found then move the duplicate rows into another Flat File.
If not found then move the rows into Sql Server Table.
Pls provide the solution. Thank you
I have a DB with some tables and, on certain tables, i've a column named "ID_COMPUTER".
I need to find all the rows where id_computer=<specific_value> from all the tables of my database, when column "ID_COMPUTER" exists.
Pseudo-code would be: select * from db.* where id_computer=<specific_value>
how to write this ?
write a query which retrieves only unique rows excluding some columns.
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19393Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
In the above table project number and id shouldn't be considered and I should get the unique rows considering rest of columns and sorted based on date. Expected result is
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
I have a table (represented by #Events) that holds modifications made to another table. I do have some control over the table structure and indexing. I want to pull all of the change records that were made between two dates.
The tricky part is to include the previous version of each record, which will usually be found prior to the start date in question.
The code that I have provided below works. So you can use it to easily see what should be returned. But it's very slow in production.
Any better method to pull this data together?
-- Production version of this table has 4.5 million rows (roughly 1,000 rows per day)
-- Primary key is on L4Ident (clustered)
-- nonclustered index on ProcessDate, LinkRL4
DROP TABLE dbo.#Events;
DROP TABLE dbo.#Results;
CREATE TABLE dbo.#Events (
L4Ident int IDENTITY(1,1) NOT NULL,
[Code] ....
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.
Any sort of help would be highly appreciated.
Thanks,
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
Any sort of help would be highly appreciated.
Thanks,
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
i have a table like below
create table staff_attendance
(
attendance_id int,
attendace_date datetime,
staff_id int,
working_year int,
hours int
)
values like
1 2014-06-30 00:00:00.0ST10121
2 2014-06-30 00:00:00.0ST10122
3 2014-06-30 00:00:00.0ST10122 ----same entry like previous one
4 2014-07-01 00:00:00.0ST10121
5 2014-07-01 00:00:00.0ST10122
6 2014-07-02 00:00:00.0ST10121
7 2014-07-02 00:00:00.0ST10122
8 2014-06-30 00:00:00.0ST10221
9 2014-06-30 00:00:00.0ST10222
10 2014-07-01 00:00:00.0ST1022 1
11 2014-07-01 00:00:00.0ST102 22
12 2014-07-02 00:00:00.0ST102 21
13 2014-07-02 00:00:00.0ST102 22
I Need to find the duplicate rows like same entries which is having more than 1 rows.... how do i find?
I have the table with the similar set of records which mentioned below, find the time difference between two rows of record. By Using the MsgOut column i have to find time taken b/w PS & PV and some record doesnt have PV .
LogID LocIDClientCert MsgType MsgOutMessageTimeStamp System
1151334934NOT SPECIFIEDQ_T12PS 2015-10-01 00:00:40.980AHR
1151335243NOT SPECIFIEDD_T12PV 2015-10-01 00:00:53.800AHR
1151342944NOT SPECIFIEDQ_T12PS 2015-10-01 00:05:40.957AHR
1151343281NOT SPECIFIEDD_T12PV 2015-10-01 00:05:53.670AHR
1151350046NOT SPECIFIEDQ_T12PS 2015-10-01 00:10:40.970AHR
1152760563759NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:29.617AHR
1152760739690NOT SPECIFIEDQ_T12PS 2015-10-01 15:28:33.633AHR
Hello All,I am trying to create a DTS package.I have two tables tbl_A and tbl_B with similar data/rows but noprimary keys.tbl_A is master.I would like this package to query tbl_A and tbl_B and find1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_Athat are not present in tbl_B and3)all rows in tbl_B that are not present in tbl_A, and then just showthose rows.Can this be done with a simple UNION?Perhaps this could produce a temp Table that can be dropped once theDTS package exists successfully.The 2nd part after all the above rows are retrieved is that I wouldlike to add an addional Column to the retrieved data called STATUSwhich has 3 possible values(letters) at the end of each row...M (modified) means that row exists in tbl_B but has 1 or moredifferent columnsA (add) means this row exists in tbl_A but not in tbl_BD (delete) means this row exists in tbl_B but not in tbl_AI'm hopping this DTS package would output a nice comma seperated TXTfile with only...1) rows from tbl_A that are different in tbl_B (STATUS M)2) rows from tbl_A that are not present in tbl_B (STATUS A)3) rows from tbl_B that are not present in tbl_A (STATUS D)Can a DTS package in MS SQL be used to perfom all of the above tasks?I would very much appreciate any help or any advise.Thanks in advance :-)
View 7 Replies View Related