Hello... I'm real new with SQL Server Express and I need to do the following.
I have two tables...
Table 1: Column EmailAddress
Table 2: Column Cust_ID, EmailAddress, FirstName, LastName
I want to search Table 2 EmailAddress, with Table 1 EmailAddress, and if any of the Email Addresses in Table 1 are found in Table 2, delete the rows in Table 2.
Thanks,
Car54
Hello people
Can somebody tell me if and then how to remove a column from a table. (I know how to add a column.)
Kind regards
De Waal
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
Hi,
I am developing a search engine for my application and I need to grab the entire row from the table if I found the search field in any of the columns and the search is not defined to one table. Please let me know how to search all columns in table without using column names indivdually.
Hi,
Can you search a column of a database table to find all the rows that have a wor in it?
example: I have a row that contains 'adventure st north', there are other columns in that table that are suppose to be the same but read 'adventure street N.' or 'adventure st. N.' or 'North Adventure st.'
could I search for rows that contain 'adventure' in the column searched (lets call it columnA). I tried:
select * from tbl_test where columnA LIKE 'adventure' and got no results.
what is the way to do this?
Thank-you,
Eric
i have a table date column with smalldatetime format. data looks like
2004-05-10 00:00:00
i can search specify date but i want to retrieve all records for a month for e.g
April
how can i do this?
i tried
select * from mytable
where datecolumn like '%05%'
doesn't seem to work
Hi there, newbie here.
I'm building a web application that allows for tagging of items, using ASP.NET 2.0, C# and SQL Server.
I have my USERS, ITEMS and TAGS separated out into three tables, with an intersection table to connect them.
Imagine a user has found an item they are interested in and is about to tag it. They type their tag into a textbox and hit Enter.
Here's what I want to do:
I want to search the TagText column in my TAGS table, to see if the chosen tag is already in the table. If it is, the existing entry will be used in the new relationship the user is creating. Thus I avoid inserting a duplicate value in this column and save space. If the value is not already in the column, a new entry will be created.
Here's where I'm up to:
I can type a tag into a textbox and then feed it to a query, which returns any matches to a GridView control.
Now I'm stuck... I imagine I have to use "if else" scenario, but I'm unsure of the code I'll have to use. I also think that maybe ADO.NET could help me here, but I have not yet delved into this. Can anyone give me a few pointers to help me along?
Cheers!
Hello all,
I am using the ContainsTable function to search a database from my (c#) app. This works relatively well and all fields of the table are indexed and searched. That is, any column, but per record only one column.
What I mean is this: when searching for "chris 2007", I want to retrieve all items where author contains chris and year contains 2007. Currently, a search for chris brings up all items where author (or any other field) contains chris, a search for 2007 works as well, but chris 2007 fails as there is no -one- field where chris and 2007 are located.
Can anybody help me achieve this? My code is:
Code Block
SELECT FT_TBL.ID, FT_TBL.Type, FT_TBL.Author, IsNull(FT_TBL.Author, FT_TBL.Editor + ' (Ed.)') AS CorrectedAuthor, FT_TBL.Editor, FT_TBL.Title, FT_TBL.Abstract, FT_TBL.Comments, FT_TBL.Year, FT_TBL.City, FT_TBL.Publisher, FT_TBL.ISBN, FT_TBL.Pages, FT_TBL.Journal, FT_TBL.Issue, FT_TBL.Hyperlink, FT_TBL.Tags, KEY_TBL.RANK
FROM Sources AS FT_TBL
INNER JOIN CONTAINSTABLE(Sources, *, @searchQuery) AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
What am I doing wrong?
Thanks in advance,
Chris
I have a DB named zCIFRecord with a column named CIFUpdateDate which is of datatype varchar. The data is a date MM/DD/YYYY 01/30/2008, this is al that is in this column. I can search this colum for individual dates and for a range of dates. My problem is with a range of dates that is not within the same year, such as;
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE [CIFUpdateDate] between '12/01/2007' and '01/30/2008'
will return nothing because it seems to only search on the 12 then the 01 then the 2008. this search can be performed properly on dates within the same year such as;
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE [CIFUpdateDate] between '01/01/2008' and '01/30/2008'
will return the proper values because now all the numbers are in correct order. How can i create a search that lets me perform the first query as well as the second query. I tried to convert to a float but you cant convert a varchar to a float.
Hi,I've the following problem. I must delete a column DEFAULT from a table,but I must do it with a script, independently from the server where it'llbe executed.Locally I've tried with:ALTER TABLE [dbo].[PlanningDettaglio]DROP CONSTRAINT [DF__PlanningD__OreSt__688C6DAC]GOALTER TABLE [dbo].[PlanningDettaglio]ALTER COLUMN [OreStraordinario] varchar(5)GOALTER TABLE [dbo].[PlanningDettaglio]ADD DEFAULT ('00.00') FOR [OreStraordinario]GOit works, but only locally.I've tried with:ALTER TABLE PlanningDettaglio ALTER COLUMN OreStraordinario DROP DEFAULTErr.: Incorrect syntax near the keyword 'DEFAULT'.Can someone help me please?Thanks in advance,GiacomoP.S.We're using SQL Sever 2000 (version 8.00.194)
View 6 Replies View RelatedHello all. I have a table with two coulmns CODE and DESCRITPION. Can anyone suggest how i can go about deleteing the entire record where two or more codes are the same?
Thanks.
How can we delete the column from SSIS file using ssis?
View 3 Replies View RelatedHi all,
(I am using SQL Server 2005)
I have created a new 'CUSTOMERS' table and created a colum 'CustomerID' as an Identity column.
Now, a problem I find is that when I delete a particular record, its Identity value is used automatically for the New record I insert later!
I do not want to re-use the already used Identity value.
I just want to have the last CustomerID to be higher that all the previous ones.
Is there any way to do this?
Thanking you in advance,
Tomy
I have a table tblFriends:UserCodeOwner FriendCode5 545 1235 4785 49054 5123 5478 5478 500490 5490 500500 478500 490500 600600 500As you can see I store each relation twice, i've done that because now I can create a clustered index on UserCodeOwner for faster searching.What I want is the following:I want to count how many steps it takes to get from one user to another via their relations and also via which usercode it goes.so: from 5 to 123 is one step.5-123from 5 to 500 is 2 steps via either 478 or 490I want to get a result like this:5-478-500and5-490-500from 5 to 600 is 3 steps:5-478-500-6005-490-500-600Does anyone have a good start for me on how im able to return such results and how I can search through the table most effecively?
View 3 Replies View RelatedHi everybody,
is it possible using SQL Server search for a word on each
table on each field?
Thanks
Fabio
Hi,
I have 4 tables :
Code Block
Create Table #Request ( [requestid] int , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);
Create Table #RequestDetail(reqdetailid int, [customername] Varchar(20), [customerage] int, requestid int);
Create Table #Call(Callid int,Calltype int,callDetailid int )
Create Table #CallDetail(callDetailId int,empid int)
Insert into #CallDetail VALUES(12123,1)
Insert into #CallDetail VALUES(53423,1)
Insert into #CallDetail VALUES(6532,1)
Insert into #CallDetail VALUES(82323,1)
Insert into #CallDetail VALUES(124235,1)
Insert Into #Call VALUES(111,1,12123)
Insert Into #Call VALUES(112,1,53423)
Insert Into #Call VALUES(114,1,6532)
Insert Into #Call VALUES(123,2,6532)
Insert Into #Call VALUES(134,1,124235)
Insert Into #request Values('324234','SA023',12,111,0);
Insert Into #request Values('223452','SA023',12,112,0);
Insert Into #request Values('456456','SA024',12,114,0);
Insert Into #request Values('22322362','SA024',44,123,0);
Insert Into #request Values('22654392','SA023',12,134,0);
Insert into #requestdetail values(1, 'Bill',23,'324234')
Insert into #requestdetail values(2, 'Tom',25,'223452')
Insert into #requestdetail values(3, 'Bobby',27,'456456')
Insert into #requestdetail values(4, 'Guck',29,'22322362')
Insert into #requestdetail values(5, 'Bobby',33,'22654392')
1. My stored proc will take the CallDetailID.
2. I have to find out the requests made on this calldetail.
3. After getting the request, i want to take the Customername, cityno of the request/requestdetail and pass it again to #request table to search for any duplicates within the request.
4. If found return the details of the original record :
[A similar requirement was solved earlier, but the structure has changed. This is a seperate requirement with different table strucure. Hence a new post. ]
thanks
I have a stored procedure that takes 5 parameters. Based on what parameters are passed i want to return a result set to the calling code. Not all of the parameters are required so there will be some combinations on the entered parameters.
Do i need to dynamic build the query with the IF statements to check if the input parameter is NULL or not ?
Thanks
Relative SQL newbie here......this is probably easy, but....Lets say I have a table (MainTable) that stores a list of input table names,a primary key (PKey), and a field called "Configured" for each one. Each ofthese input tables also contain a field called "Configured", which is set totrue or false in another process based on an OrderNumber. (So an order'sinputs are stored in several input tables, and the MainTable is a summarytable that shows which input tables have been configured for any givenOrderNumber).What I need to do is open each input table, and look for a record containinga specific OrderNumber and where Configured=true. If a record is found, Ineed to update the Configured field for that table in the MainTable, andthen move on to the next sub-table.The way I'm doing it now is with simple SQL and loops. Here is the basiccode (ASP):*****************************************OrderNumber = "562613" ' the current order that is being processed' reset all configured flagssql = "UPDATE MainTable SET Configured = 0"conn.execute sql, , &H00000080' get list of all tablenamessql = "SELECT InputTableName, PKey FROM MainTable WHERE InputTableName <>'---'"set rsTableNames = conn.execute(sql)while not rsTableNames.eof' test each input table for configured flagsql = "SELECT Configured FROM " & rsTableNames("InputTableName")& _" WHERE Configured = 1 AND OrderNumber = '" & OrderNumber &"'"set rs = conn.execute(sql)If Not rs.EOF Then' update the main tablesql = "UPDATE MainTable SET Configured = 1 WHERE PKey='" &rsTableNames("PrimaryKey") & "'"conn.execute sql, , &H00000080end ifset rs = nothingrsTableNames.movenextwend*****************************************There has to be a faster way.. I think.... maybe something that could bewritten as a stored procedure? I use a similar technique in a couple ofother places and it's a bit of a performance hit, especially as the numberof input tables grows.TIA!Calan
View 6 Replies View RelatedI want to create a function that searches for allowed characters within a table range (that contains the allowed characters) and replace any characters outside this range with a space.
For example -
'Bill123?', 'Jones12.z-'
'John&12/', 'QWERT123&4'
Wanted results – the single quotes are there to show the space for the replaced characters.
'Bill123 '
'Jones12.z '
'John&12 '
'QWERT123 4'
Example SQL data
CREATE TABLE [Common].[AllowedCharacters] (
[Character] [varchar](1) NOT NULL,
[Replacement] [varchar](10) NULL,
[AlwaysInclude] [bit] NOT NULL)
GO
SET ANSI_PADDING OFF
[code]....
The function will wrap around the column names and I know it can be done without a table validate the characters but it must be done this way.
i have a table that i think is bad -
how do i delete the table?
also i want to copy this same table from another database -
can i use enterprise manager to copy the same table in place of the deleted table?
Hi guys,
I have a table which consist of 6,185 rows and from this table I want to delete 427 rows. I have placed the 427 rows in a separate table. So how do I delete these 427 from the original table(6185 rows).
I should also mention that the table does not have a primary key so I was thinking about something like this but it didn't work
delete o.CardNumber
,o.ref_no
,o.tran_date
,o.tran_val
from test6185 o
where exists (select h.CardNumber
,h.ref_no
,h.tran_date
,h.tran_val
from test427 h
where( h.ref_no=o.ref_no and
h.CardNumber = o.CardNumber and
h.tran_date =o.tran_date and
h.tran_val =o.tran_val
))
order by cardnumber
Thank much for any help
I am trying to query the SharePoint database to get a list of how many times a table name is being used in our SSRS reports.Here is my current query:
DECLARE @TEXTTOSEARCH AS VARCHAR(200)
SET @TEXTTOSEARCH = 'tablenamehere'
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)
[code]...
This works for some tables since the names are unique and I can just do a search for that. However, some of the table names are simple and can also be used as aliases.if there was some way to get the list of table names easier.
I am trying to delete from a SQL table using two arguments, I currently use the following on the aspx page: <ItemTemplate><asp:LinkButton ID="cmdDeleteJob" runat="server" CommandName="CancelJob" Text="Delete" CssClass="CommandButton" CommandArgument='<%# Bind("Type_of_service") %>' OnClientClick="return confirm('Are you sure you want to delete this item?');"></asp:LinkButton></ItemTemplate> and then I use the following on the aspx.vb page: If e.CommandName = "CancelJob" Then Dim typeService As Integer = CType(e.CommandArgument, Integer) Dim typeDesc As String = "" Dim wsDelete As New page1 wsDelete.deleteAgencySvcs(typeService) gvTech.DataBind() I need to be able to pick up two CommandArguments.
View 4 Replies View Relatedin sql server 2000, I have a table that I have to write a script for to delete several columns in this table. I am finding that I have to use alter or drop keywords or a combination of the two but not sure because I have not done this before. I am googling this but finding all kinds of other information that I dont' need to know.
I dont have rights on this table so I cannot do this manually. I have to create the script and send it on to someone else.
If anyone can provide a good script example that I can use to delete unwanted columns it would be a great thing. Thanks.
Hello everybody,
I have problem deleting row from same table using below trigger
CREATE TRIGGER [tDeleteDomain] ON dbo.iMS_Domains
FOR DELETE
AS
IF @@RowCount > 1
BEGIN
ROLLBACK TRAN
RAISERROR ('You can only delete one domain at a time.', 16, 10)
END
DECLARE@DomainID int
SELECT@DomainID = ID
FROMDELETED
DELETE FROMiMS_Domains
WHEREAliasFor = @DomainID
If I am using this trigger I am getting the below error
Server: Msg 217, Level 16, State 1, Procedure tDeleteid, Line 16
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
Can anybody give good suggestiong regarding this.
Thanks,
ravi
hello there,
what is the code to Delete all rows in a Table????
thanks in advance
I need to delete some specific rows in Table1
int1 must have the value 1
int2 must have the value 1
int3 must have the value 0
and now we get to the difficult part - first character in the field [Cost No] have to be different from the letter 'B'. [Cost No] have the datatype varchar(20)
I expected the code to look something like this:
DELETE Table1
FROM Table 1
WHERE ([Int1] = 1) AND ([Int2] = 1) AND ([Int3] = 0) AND ((LEFT([Cost No]), 1) <> 'B')
But I get this error message:
The left function requires 2 arguments
What am I doing wrong and what should the right code look like?
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.
Hi, i need the suggestion here in very familiar db situation ..i have a main table and a primary key of that table is used in many other table as foreign key.If i am deleting a record in a main table,how do i make sure that all the corresponding record in the associated tables,where that foreign key is used, gets deleted too?What are my options?Thanks
View 5 Replies View Relatedi have 6 read only tables that every night all the data gets dumped and a new updated copy of the data is copied over. the tables range from 500,000 rows to almost 4 million. i have indexes set up on the fields i use to query against. my questions are
1.since i dump all the data every night and replace it, do i need to rebuild the indexes every night or is that done after the data is reentered?
2.i want to use a fill factor on the table since it is read only, but will dumping the data every night and reinputting it have adverse affects with a fill factor?
3.should i be shrinking the database or defragging it everynight cause of my data dumps and reloads?
thanks in advance
spiral
How can I quickly delete thousands of rows in a table (SQL2000) according a query and without blowing up the log file? For instance executing the query:
Delete from transactions
WHERE transactiondatestamp < DATEADD (m,-4,GETDATE())
increases my log file to almost 6GB before job was done an normal size was re-obtained. In addition it took a long to time to get the job done.
With the command truncate table I cannot use query unfortunately but this would be faster.
Anyone has an idea?
mipo
I have a table where I want to delete some data from but I get this error.
You might have a record that has a foreign key value related to it, or you might have violated a check constraint.
What to do????
I have an SQL tables [Keys] that has various rows such as:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External
3 Key1 Key1 InHouse
4 Key1 Key1 InHouse
5 Key1 Key1 InHouse
Obviously IDs 1,3,4,5 are all exactly the same and I would like to be left with only:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External
I cannot create a new table/database or change the unique identifier (which is currently ID) either. I simply need an SQL script I can run to clean out the duplicates (I know how they got there and the issue has been fixed but the Database is still currently invalid due to all these duplicate entires).
Any help would be greatly appreciated.
Thanks,