The below query should produce all those customers who are in 'orginalerrorfiles' but not in 'hopefullyworks2'. 3 fields will be used as the check to make sure that the rows, that r identical won't be displayed but those that r different will be displayed.
Instead of doing that it just display everything in the table called 'orginalerrorfiles'.
Note 'orginalerrorfiles' consist of 141455 rows
'hopefullyworks2' consist of 134784 rows
select o.card_no
,o.ref_no
,o.tran_val
from OriginalErrorFiles o
where exists (select h.card_no
,h.ref_no
,h.tran_val
from hopefullyworks2 h
where( h.card_no = o.card_no and
h.ref_no=o.ref_no and
h.tran_val = o.tran_val
))
I have just installed SQL Server 7 on a server. Everything was installed fine without any errors. The only problem occurs when I use Enterprise Manager, I right click on a table in the Northwinds database to open table and return all rows. The form pops up and no rows appear. Nothing in the table form works. I can view the records from Query Analyzer, and can also view them through Enterprise Manager when using a workstation client. This problem only occurs at the server. Any thoughts.
i have 2 columns like Col1====col2 username=======saif====mr@hotmai.com email=======saif@hotmail.com name======saifullah I want to get these records from sql like username email name saif saif@hotmail.com saifullah ________________________________- how can I achive this
I have two tables A and B, A has 8000 and B has 8122 records. I want to see what records are missing. I tried EXCEPT and it returned zero rows. I used where non exists also still no records.
I am interested in changing the way that data is displayed in my result set.Essentially I want to display a selection of rows (1 to n) as columns, the following diagram explains my intentions.Perhaps one of the greatest challenges here is the fact that I do not have a concrete number of rows (or BIN numbers). Each stock item could be stored in one or more BINS, which I will not know until running my query.
In my C# asp.net website I am using a qurey select GPM.propertyId,GURM.userName,GURM.phone from Gatepropertymaster1 GPM,GateUserMaster GURM where GPM.userId=GURM.userId and GPM.propertyTypeId='" + PropertyTypeId + "' and GPM.transationTypeId='" + TransationTypeId + "' and GPM.cityId='" + CityId + "' order by GPM.regDate descin some cases input PropertyTypeId may be 0 or transationTypeId may be 0 or cityId may be 0, How can I use the same qurey even though these Ids(propertyId,TransationTypeId,cityId) may be 0 in some cases Thanks in advanceAnu Palavila
I am working on a multi level payout system with a parent child table for referrers. I am performing multiple In Lists to get to level 3 of the pyramid as you can see by running the code below.
I would like if this is the best way to refer to these folks down on level 3 and is there a better way to go down the levels.
Any help would be greatly appreciated. Thanks.
Here is my code:
-- Make the Table CREATE TABLE [dbo].[x_Test__Member_Refer]( [ID] [int] IDENTITY(1,1) NOT NULL, [CustomerID] [int] NOT NULL, [MemberID] [int] NOT NULL, [dtmTimeStamp] [datetime] NOT NULL CONSTRAINT [DF_MemberRefer_dtmTimeStamp] DEFAULT (getdate()), CONSTRAINT [PK_Member_Refer] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
-- Add some Data Insert Into x_Test__Member_Refer ( CustomerID, MemberID ) Values ( 59108, 59107 )
-- Run our Queries Select * From x_Test__Member_Refer
-- Set Top Member Declare @MemberID int Set @MemberID = 59107
-- Select Level 1 Select * From dbo.x_Test__Member_Refer Where MemberID = @MemberID
-- Select Level 2 Select * From dbo.x_Test__Member_Refer mr Where MemberID In ( Select CustomerID From dbo.x_Test__Member_Refer Where MemberID = @MemberID ) -- Select Level 3 Select * From dbo.x_Test__Member_Refer mr Where MemberID In ( Select CustomerID From dbo.x_Test__Member_Refer Where MemberID In ( Select CustomerID From dbo.x_Test__Member_Refer Where MemberID = @MemberID ) )
-- Clean Up Shop! Drop Table x_Test__Member_Refer
JBelthoff • Hosts Station is a Professional Asp Hosting Provider › As far as myself... I do this for fun!
I need to change a display value for a database field when its value is NULL, Exampl I wrote the query like this CASE MyTable.MyFiled When 'IS NOT NULL' THEN ' ' Else MyTable.MyFiled END But it works well in sql query screen, but not in Sql panel, can any one help me. Thank you
I've created a cube and it processed fine. The calculate command is there. The measure that I'm attempting to run in the SSAS/Visual Studios browser is simply a count rows measure. When I drag the measure to the window, it says no rows available. If I click on the filter that allows nulls, the only change it makes is that it goes from no rows available to "NULL".
Hi All, I have a report which has two subreports in a table.First Sub report1 ,then a detail section and sub report 2. Sub report1 runs fine.But sub report2 gives error in the main report"
Query execution failed for data set 'DataSet_azshdsd30'. For both the sub reports parameters passed from the main report are same(2 parameters). When I run sub report2 indivisually it gives right result.But not in the main report. Also when I put Subreport 2 in place of subreport 1 I do get the results in the main report. Any idea,any body?? Thanks.
i have a table inwhich there are three columns heading1, heading2, heading3...now this table is linked to a tree table which has columns headingid, headingname, and parentid
The link between the table1 and table 2 be heading1 = headingid OR heading2= headingid OR heading3= headingid
is there a way I can show this on my database diagram??? (I know this is against normalisation, but the database was written by someone else...)
Does anyone know where I can get a digram showing the database schemas for all of the system database. I need to know the realtionship between tables in the system databases. Would like to download something from the internet preferably, or if someone has something they can e-mail?
my table does not have primary key so i created a seperate index on each of the table.
I used the recommended tablediff utility and it works successfully. But its only show the difference of records in each table and does not copy rows from source to destination and destination to source table. I was expecting database1.dbo.table1 contains same records as in database1.dbo.table2.
C:Program FilesMicrosoft SQL Server90COM>tablediff /sourceserver kashif-pcs qlexpress /sourcedatabase AB /sourcetable table1 /destinationserver kashif-pcsq lexpress /destinationdatabase CD /destinationtable table2 Microsoft (R) SQL Server Replication Diff Tool Copyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values: /sourceserver kashif-pcsqlexpress /sourcedatabase AB /sourcetable table1 /destinationserver kashif-pcsqlexpress /destinationdatabase CD /destinationtable table2
Table [AB].[dbo].[table1] on kashif-pcsqlexpress and Table [CD].[dbo].[table2] on kashif-pcsqlexpress have 5 differences. Err Sno Src. Only 101 Src. Only 102 Dest. Only 103 Dest. Only 104 Dest. Only 105 The requested operation took 0.466767 seconds.
Can you write a short script for my problem, just like comparison of database1.dbo.table1 compares in database2.dbo.table2 and which ever records not present it should copy those and vice-versa.
It means Database1.dbo.table1 contains 5 records Database2.dbo.table2 contains 5 records
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.
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.
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
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
Using the the NumId from TitleData, I would like to delete thecorresponding row in Bookdata using pure SQL. I want it to delete allrows in bookdata where the Titledata.NumID is a match to bookdata.idThe two tables are linked in that the NumId of table Titledata isidentical to the Id of table bookdata. I can, using ADO, loop thrudeleting one by one but I would like to do this in a pure SQLstatement. Is this possible? Any help is appreciated.I was thinking something like this way :"Delete from Bookdata where Titledata.NumID = Bookdata.id"But of course it will error.My current code is:(frmlogon.tablename is really Titledata)Dim rstry As New ADODB.RecordsetDim values As VariantSQLQuery = "Select Numid from " & frmLogon.TablenameSet rstry = frmLogon.cnConnection.Execute(SQLQuery)values = rstry.GetRowsSet rstry = Nothing'now loop thruDim xx As Integerxx = 0Do Until xx > UBound(values, 2)SQLQuery = "Delete from Bookdata where bookdata.Id = '" & values(0,xx) & "'"frmLogon.cnConnection.Execute (SQLQuery)xx = xx + 1Loop'create statements for 2 tables involved areconn.Execute "CREATE TABLE TitleData" & _"(Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY," & _"NumId INT DEFAULT 0 )"conn.Execute "CREATE TABLE BookData" & _"(Id INT IDENTITY (1, 1) NOT NULL," & _"Titles TEXT DEFAULT ''," & _"GeneralNote TEXT DEFAULT ''," & _"Author VARCHAR(100) DEFAULT ''," & _"Imprint VARCHAR(100) DEFAULT ''," & _"ISBN VARCHAR(100) DEFAULT ''," & _"Description VARCHAR(100) DEFAULT ''," & _"CallNumberPre VARCHAR(5) DEFAULT ''," & _"CallNumber VARCHAR(25) DEFAULT '',LOCNumber VARCHAR(30) DEFAULT '',"& _"Accession VARCHAR(25) DEFAULT ''," & _"Bibliography VARCHAR(100) DEFAULT ''," & _"Series VARCHAR(100) DEFAULT ''," & _"MyStatus VARCHAR(70) DEFAULT ''," & _"Barcode VARCHAR(50) DEFAULT ''," & _"LocalData VARCHAR(100) DEFAULT ''," & _"CheckoutPeriod VARCHAR(10) DEFAULT ''," & _"CatalogCard TEXT DEFAULT ''," & _"Summary TEXT DEFAULT ''," & _"MyCount VARCHAR(10) DEFAULT ''," & _"ItemDate DATETIME DEFAULT ''," & _"MyUser VARCHAR(50) DEFAULT ''," & _"MarcData TEXT DEFAULT ''," & _"SdlsRecord TEXT DEFAULT '', LOSC VARCHAR(5) DEFAULT '', LOSNDecimal(14,6) DEFAULT 0," & _"Edits Char(1) DEFAULT '', TitleDuplicate VARCHAR(50) DEFAULT '')"
Dear Friends, Is there any way to display a table data separately like odd rows and even rows?I dont know this is possible or not?If it is possible means how can i achieve it?Please guide me a proper way. Thanks all!
I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:- GO ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details] @Class_Id int AS BEGIN
Table B: Name Data UserA xxx UserB asdasd UserB ewrsad UserC dsafasc UserA sdf UserB dfvr4
I want to count the total entries in Table B for every user in Table A. The output would be:
Name Count UserA 2 UserB 3 UserC 1
I can use a Select Count statement, but I will have to make a SQL call for every user in Table A. Also, Table A is dynamic, so the users are always changing. Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?
i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.
I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.
need help how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute) i need whan i insert to the another table add 2 more fields (unique number , date_time )
this is the table 1 i select from ID fname new_date val_holiday ----------------------------------------------------
this is the table 2 i insert into ---------------------------------- ID fname new_date val_holiday unique number date_time --------------------------------------------------------------------------------------------------------------------
for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)
next insert ...... ID fname new_date val_holiday unique number date_time --------------------------------------------------------------------------------------------------------------------
next insert ...... ID fname new_date val_holiday unique number date_time --------------------------------------------------------------------------------------------------------------------
I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.
Here is a basic breakdown of what I am trying to do:
Table1 (the table where the rows need to be deleted from) Column_x (Holds the id that is unique to the various rows of data - User ID)
Table2 (Holds the user information & has the associated ID) Column_z (holds the User ID)
I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.
Code:
SELECT Column_z FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z <> Column_x
This did not seem to do what I needed, it did not delete any rows at all.
I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2
Then I tried another scenerio that I also needed to do:
Code:
SELECT Column_z, Column_a FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z = Column_x AND Column_a='0'
'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.
Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.
