Changing Records In Tables
Jul 12, 2006
Hi,
I was just wondering... Is there any built-in function in MS SQL, which will
allow me to do rows' "capitalisation"?
Lets say that in database.table.name I've got:
"FOO BAR LTD.", which I want to change to "Foo Bar Ltd."
"Foo bar LTD.", which I want to be "Foo Bar Ltd.".
Is there any way of doing this or do I have to read it from database, change
in some script, and then insert it back into the table?
Hope it's all clear ;-)
Thank you,
Martin
View 2 Replies
ADVERTISEMENT
Dec 5, 2000
I have a SQL database (10000 records / 50 users) that stores information on
faxes that we receive. This will sound odd but the last few days some weird
things have happened. Chunks of data change arbitrarily - no script that
I have can make these
changes. Couple examples:
1) the number of pages will change from 1 to 3 - not every entry just a
group and usuall ones that have a particular status
2) the status will change from CTW to PFR - not every CTW will change just
a chunk
There appears to be no pattern to these changes.
Again, no script that I have can make these changes. This happened 6 months
ago and I reinstalled service packs (NT and SQL), reinstalled data components
and defragged the hard drive. The problem stopped either because of one
of these actions or either on it's own. I have done all of these things
this time but nothing has worked. Anyone have any ideas?? Any help is appreciated.
View 1 Replies
View Related
Jul 23, 2005
Greetings:I have a SQL 2000 database, in which about 1% of the records are inlower case. I need to make them UPPER CASE.Is there a function to determine and change the case of existingrecords, or will I have to re-write the records manually?Thanks,DW
View 6 Replies
View Related
Apr 22, 2004
Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
View 2 Replies
View Related
Dec 19, 2001
I need to drop and recreate indexes in some of my tables that are currently been replicated. I am not sure how this will affect my ongoing replication. Will this cause a problem for me? Please help
Bright
View 2 Replies
View Related
Mar 3, 2008
I'm trying to convert this statement to use left outer joins so it will be 2005 compatible.
select
convert(varchar(8),a.order_no),
a.order_ext,
a.line_no,
c.part_no,
a.order_seq,
a.customer_code,
b.date_shipped,
0,
a.seq_descript
from fm_orderline a, orders b, ord_list c, fm_ordhist d
where c.order_no = a.order_no
and c.order_ext = a.order_ext
and c.line_no = a.line_no
and c.order_no *= d.order_no
and c.order_ext *= d.order_ext
and c.line_no *= d.line_no
and b.order_no = c.order_no
and b.ext = c.order_ext
and d.line_no <> 999
and c.status = 'T'
group by
d.order_no,
convert(varchar(8),a.order_no),
a.order_ext,
a.line_no,
c.part_no,
a.order_seq,
a.customer_code,
b.date_shipped,
a.seq_descript
having d.order_no is null
I've tried this:
select
convert(varchar(8),a.order_no) as order_no,
a.order_ext,
a.line_no,
c.part_no,
a.order_seq,
a.customer_code,
b.date_shipped,
0,
a.seq_descript
from fm_orderline a, orders b,
ord_list c
left outer join fm_ordhist AS d on (c.order_no = d.order_no)
left outer join fm_ordhist AS d2 ON (c.order_ext = d2.order_ext)
left outer join fm_ordhist AS d3 ON (c.line_no = d3.line_no)
where c.order_no = a.order_no
and c.order_ext = a.order_ext
and c.line_no = a.line_no
and b.order_no = c.order_no
and b.ext = c.order_ext
and d.line_no <> 999
and c.status = 'T'
And various derivations of it(adding d2.line_no <> 999 and d3.line_no <> 999 to the where clause.) But it came up with different results than what I received when it was a *= query.
Can any gurus on here help me out?
View 4 Replies
View Related
Dec 13, 2001
How can I change a field size that is set to numerical 9,2 to 9,3? I need to allow 3 decimal places.
Thank you,
Kameron
View 2 Replies
View Related
Nov 21, 2006
Hello.
i have a database with 300 tables. All the data types of the fields of my tables are custom. Ex. IFGint:int and stuff like that.
I want to know how can i through a stored procedure, change in batch mode all the fields of my tables. I don't want to modify by hand everytable. It's a lot of work and i think that maybe there might be a way for this programatically.
thanks in advance
View 2 Replies
View Related
Apr 28, 2008
Hi all,
Apologies if this has been raised in the past, but 6 hours of web searching today hasn't turned up anything!
I'd like to use the Slowly Changing Dimension (SCD) Wizard to keep track of tables in my relational database. This means 200+ tables. I don't want to step through the UI Wizard for each table. Ideally I'd like to be able to create the SCD transformation in code, but I can find no good examples for doing this. The MSDN examples here are too brief and don't allow me to expand out to the level I need.
As in any database, columns come and (very rarely, go), and having a programmatic solution to this would mean that I could be flexible and cope with these situations.
So, my question is: Has anyone implemented SCD functionality in code, or have any code examples that do this, that I might learn from. Or, any tips/pointers if I'm barking up totally the wrong tree.
Thanks in advance,
-tom
View 2 Replies
View Related
Dec 11, 2007
hello,
I have several tables that have guids as their primary keys and the tables are related as follows:
Table1 - primary key = ServiceNo (Guid), Filter Key = CampaignNo
Table2 - primary key = CostBasisNo (Guid), Foreign Key = ServiceNo (from Table1)
Table3 - primary key = UserId, Foreign Key = ServiceNo (from table1)
Table4 - primary key = SourceServiceNo (Foreign Key from Table1), MemberServiceNo(Foreign Key from Table1)
what I need to do is copy all records from Table1 where CampaignNo = @CampaignNo and insert them into table1, this I can do easily but I will generate a new ServiceNo for each one and associated a new CampaigNo which is fine.
The problem comes in that I need to also copy the contents of Table2 = Table3 for all ServiceNos that have been copied from Table1 but insert the new Guid that will have been created when copying the rows in Table1
This is further compounded when I need to do the same to Table4 but this time I need to insert the newid's for SourceServiceNo and the related MemberServiceNo which all would have changed.
I haven't the first clue where to start with this task, do I need to use temporary tables, cursors? any help gratefully received, even if it's a pointer to the most efficient approach.
regards
View 4 Replies
View Related
Jul 2, 2004
Is it possible to determine the data size of records in a table. basically I would like to run a select query with a condition and the result will be xKB. I know that this is possible on a database level, but can it be done on a record level (or number of records).
Cheers
H
View 4 Replies
View Related
Sep 11, 2000
I am trying to update a field within one table with the values from another table. With the criteria that another field in each table are equal. What is the correct way to do this. My syntax is all wrong.
thanks
Jason
View 1 Replies
View Related
Feb 26, 2001
I report SQL Server table information (table names, field names, field datatypes, etc.) to my users using an ACCESS front end. Most of this information exists in the system tables. But I can't find a record count per table in the system tables. What's the fastest way to get a record count for all tables?
View 1 Replies
View Related
Jan 27, 2008
Hi!
I have 2 tables (both have the same structure):
ID -> bigint (identity, not for replication, primary key)
Url -> nvarchar(1000)
MainUrl -> nvarchar(1000)
Tbl1 cantains about 0,5 mln records, and tbl2 - 1 mln.
What I need, is to copy records from tbl2 to tbl1. But records in tbl1 are unique, and it can't change. (Unique must be only "Url"; (and ID, but it's automatic)). How can I do this in fast way? Now I'm using SELECT for each record in tbl2 to see if it exist in tbl1. But it's a bit slow... Is there any faster method? (One thing: I'm beginner in databeses, so I'm wrote VB application to transfer records. How can I do it using only Microsoft Sql server?)
--------------
I'm forgot to write, I'm using MsSql 2005.
View 7 Replies
View Related
Nov 15, 2006
I have two tables that have a common column (ID). Now, what i am trying to do is find what is not in one table that is in the other.
For instance:
Table A
ID NAME
1 Tom
2 George
3 Richard
Table B
ID NAME
1 Tom
3 Richard
4 Kevin
With this information, I am trying to write a query that would tell me that Kevin is the only record that doesn't exist in both tables ... like an outlier.
I have tried using something like the following:
SELECT distinct id, name
FROM Table 1 INNER JOIN Table 2
ON Table 1.id <> Table 2.id
Any help would be great. Thanks!
-L
View 3 Replies
View Related
Aug 7, 2005
HiI'm using Access 2002. I have 2 tables tblGroupContact,tblGroupPermission, both have 2 fields identical structure:ContactID GroupID (Both are Composite keys and both hold integers)tblGroupContact holds everybody and the groups they are members of.tblGroupPermission holds only those people who have permission to makechanges to another part of the DB.The SQL at the end of post works, but opens a dialogue box looking fora parameter value 'query1.ContactID'Clicking enter or cancel (without entering anything) works OK.What have I done wrong to cause this parameter request.Thanks ColinKSELECT tblGroupContact.ContactID, tblGroupContact.GroupIDFROM tblGroupContact LEFT JOIN tblGroupPermission ON(tblGroupContact.ContactID = tblGroupPermission.ContactID) AND(tblGroupContact.GroupID = tblGroupPermission.GroupID)WHERE (((tblGroupPermission.ContactI*D) Is Null) AND((tblGroupPermission.GroupID) Is Null));
View 3 Replies
View Related
Apr 13, 2006
I need some help with this. I was able to count all the records in ourdatabase using the user_tables and user_tab_columns tables afterrefreshing the statistics on this database.We are doing an upgrade of a system and I will not be able to refreshthe statistics during the upgrade. I need more of a manual process ofrunning these queries.Now I do:select A.table_name, round(A.num_rows,0) as rowcount,count(b.table_name) as ColumnCountfrom dba_tables A, dba_tab_columns Bwhere A.table_name = B.table_name and A.owner in ('PS','SYSADM')group by A.table_name, A.num_rowsorder by rowcount desc, columncount descBut I can't use the num_rows anymore so I was thinking more to do this:Select A.table_name from(select count(*) from A.Table_name B where A.Table_name =B.Table_Name)from user_tableThis does not work for me since I don't know how to pass the table_namefrom the first select to the second select. The logic is there but thesyntax is not.Please help.
View 1 Replies
View Related
Jul 31, 2007
Hi
I am trying for compare two tables records and if which records are not match insert in to both table and at end both table records will be same.
using stored procedure & I need also pass server name database name.
thanks
View 4 Replies
View Related
Jan 8, 2008
Hello,
I have the following problem:
2 tables: both have the same pk values.
one table must be deleted based on a filter (I mean the table is not delete completely but only some records), I would like to delete same records in the second table.
for ex:
table 1: pk: 1,2,3,4,5
table 2: pk: 1,2,3,4,5
table 1: deleting 1,2, 3 thus also in table 2 pk: 1,2,3 must be deleted.
At the and of process Table1 and Table2 must have the same records (always also in the case of failure, errors and so on ).
The target is avoid using triggers.
OUTPUT is not useful because it writes what is deleted (or may be useful but how to use it?).
How can I do?
Thank
View 1 Replies
View Related
Aug 29, 2006
I have searched in length and cant seem to find a specific answer.I have a tmptable to hold user "shoppingcart" ( internal supplies)What i want is to take when the user clicks order to take that table pull the records with that user and populate the order and order details tablesThe order table has a PK of orderID and the orderdetails has a FK of orderIDI know how to insert to the main table, but dont know how to populate the details at the same timeI have this.Insert into supplyordersselect requestor from tmpordercart where requestor = &name so how do i also take from the tmpordercart the itemno and quanity and put them into the orderdetails so that it links back to order table?
View 1 Replies
View Related
May 29, 2008
Hello all,
I have a DTS package set up to import a text file on a daily basis. I need to dump the data in 2 table after 7 days of the last import .this is the code that I have
Delete From TblTemp
date(Day(-7), CurrentStamp).
But for some reason it deleting the data right after it imports it. And it doesn't delete anything out of the other table.
Thanks in advance
View 2 Replies
View Related
Aug 5, 2000
Dear All,
I am having a problem in adding new records in SQL 7.0 Database.
I have two databases - one with table name DB1..WTEST and the other with
table name DB2..TEST. I want to update records in DB2..TEST table when
records are already in TEST table (by comparing primary key - with ignore
duplicate option) & secondly, INSERT records in DB2..TEST table from
DB1..WTEST table if the records do not exist in DB2..TEST table. The first
option which is Updation, works fine but with second option I have problem.
It works and records are INSERTED but I don't know the statistics of
inserted records. Instead it gives a warning message - Duplicate records
were Ignored -
Can somebody help me out or suggest some better solution for conditional
record insertion. I used CURSOR option but it is only for Updation &
Deletion.
Here is my Transact-SQL Script.
--------------------------------------------------------------
IF EXISTS (SELECT * FROM DB1..WTEST
INNER JOIN DB2..TEST
ON DB1..WTEST.Name1 = DB2..TEST.Name1)
BEGIN
UPDATE DB1..TEST
SET add1 = DB1..WTEST.Add1
FROM DB1..WTEST
Print 'Updated'
END
WHILE NOT EXISTS(SELECT * FROM DB1..WTEST
INNER JOIN DB2..TEST
ON DB1..WTEST.Name1 = DB2..TEST.Name1)
BEGIN
INSERT DB2..TEST
SELECT DB1..WTEST.Name1,
DB1..WTEST.Add1
FROM DB1..WTEST
CONTINUE
END
------------------ Alternate Script ---------------------
IF EXISTS (SELECT * FROM DB1..WTEST, DB2..TEST
WHERE DB1..WTEST.Name1 = DB2..TEST.Name1)
BEGIN
UPDATE DB2..TEST
SET add1 = DB1..WTEST.Add1
FROM DB1..WTEST
WHERE DB1..WTEST.Name1 = DB2..TEST.Name1
END
INSERT DB2..TEST
SELECT DB1..WTEST.Name1,
DB1..WTEST.Add1
FROM DB1..WTEST
WHERE NOT EXISTS(SELECT * FROM DB2..TEST
WHERE DB1..WTEST.Name1 =
DB2..TEST.Name1)
END
----------------------------------------------------------------------------
------------
Ibrar Ahmed
System Projects Controller
Olayan Saudi Holding Company
Ph:- +966-3-8871000 x 1122
Fax:- +966-3-8872000
E-Mail: i.ahmed@oshco.com
View 2 Replies
View Related
Jun 1, 2001
Attached is my select query, but it is returning the database values twice.
Can anyone tell me how I tell it to stop! A sub query I guess....
SELECT DISTINCT
dbo.Lead_Entry.Lead_ID, dbo.Lead_Entry.Lead_Source, dbo.Lead_Entry.Lead_Approved, dbo.Lead_Entry.Solution, dbo.Lead_Entry.Lead_Date,
dbo.Customer_Company.Company_Name, dbo.Customer_Contacts.Contact_Name
FROM dbo.Customer_Contacts INNER JOIN
dbo.Customer_Company ON dbo.Customer_Contacts.Company_ID = dbo.Customer_Company.Company_ID CROSS JOIN
dbo.Lead_Entry
View 4 Replies
View Related
Oct 29, 2006
How to write a sql to combine the 4 tables into one without repetitive records? The 4 tables have exactly the same fields.
The tables do not have primary key. The fields to identiry the rows is name and dob. In the case the name and dob is same for two records, the one with latest date_created is selected.
Thanks
View 9 Replies
View Related
Jan 30, 2014
I am trying to delete the records in 2 related tables. The 'child' table has a field called [SETA],
I want to delete all the records in this table that contain the same info, as well all the fields in the parent table that is related to this table. They share the [ID] field as key. This is my code:
Code:
DELETE FROM Student a full outer JOIN Qualification b on a.[ID] =b.[ID] WHERE b.[SETA] = @SETA
View 4 Replies
View Related
Apr 2, 2004
I need to update two tables. I have created a view and am using the code in the attached file to insert into the two tables.
The page loads without errors, but I get this message that the view is not updatable because the modification affects multiple base tables.
I thought this was the purpose of views?
Does anyone have any suggestions? I am using Dreamweaver MX and SQL Server.
Thanks!
N
View 11 Replies
View Related
Mar 3, 2014
There are four tables
1. Matter
MID, CID, RType
001, a, m
002, a, m
003, b, m
004, c, m
2. Category
CID. RType
a, T
b, T
c, T
3. Security assignmnet
RID, RType, GID
001, m, g01
002, m, g01
002, m, g02
002, m, g03
003, m, g01
003, m, g03
a, T, g01
a, T, g02
a, T, g03
b, T, g02
b, T, g03
b, T, g04
4. Group
GID
g01
g02
g03
g04
I'd like to find the record in table #1 "Matter" which has exact record of "GID" in table #3 "Security Assignment" compare with table #2 "Category"
In this case, it is record of "002" bacause "002" in table#1 "Matter" and the record "a" in table #2 "category" both has exact GID records(g01, g02, g03) in table #3, "Security Assignment"
How can I create qury to find all the possible record in the table #2?
View 9 Replies
View Related
Nov 8, 2013
i have 'Privious' table as
***Metrics Count***
AccessibilityFilter 1679
Archives 80
Corel 90
DateFilter 1680
and 'Current' table as
***Metrics Count***
AccessibilityFilter 1679
Archives 85
Corel 100
DateFilter 1680
I want to fetch only the mis matched records as below.
***Metrics PreviousCount CurrentCount***
Archives 80 85
Corel 90 100
View 3 Replies
View Related
Sep 22, 2005
Following is a stored procedure that currently runs on the system (compacted version). I need to combine this data with data from another Table .. tblAdjustments. The schema for this table is fairly close to tblShipmentDet.
tblShipmentHdr --> tblShipmentDet (Key = ShipmentID)
tblAdjustments --> standalone
Result: combine tblShipmentHdr + attached tblShipmentDet records with
tblAdjustments records.
Would the best approach be to use a UNION SELECT?
@XToDate datetime = '7/31/2005' ,@XBegDate datetime = '7/1/2005'
AS
SELECT
SHPH.ProductID,
SHPH.ReceivedDate,
SHPH.ShipmentNo,
SHPD.Vendor,
SHPD.Quantity,
QRecvdDate = CASE WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
THEN NULL ELSE SHPH.ReceivedDate
END,
QShipQty = CASE WHEN SHPD.TransCd = 'F'
THEN NULL
WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
THEN NULL
ELSE SHPH.ShippingQty
END,
PROD.ProductName,
QOpenAccrual = CASE WHEN MEND.OpeningAccrual is Null
THEN 0 ELSE MEND.OpeningAccrual
END
FROM dbo.tblShipmentHdr SHPH
LEFT OUTER JOIN dbo.tblProducts as PROD ON Left(SHPH.ProductID,7) = Left(PROD.ProductID,7)
LEFT OUTER JOIN dbo.tblShipmentDet as SHPD ON SHPH.ShipmentID = SHPD.ShipmentID
LEFT OUTER JOIN dbo.tblMonthend as MEND ON SHPH.ProductID = MEND.ProductID And MEND.MEPeriod = convert(varchar(40),@XBegDate,121)
WHERE ((SHPH.ReceivedDate >= '7/1/2005' AND SHPH.ReceivedDate <= '7/31/2005') OR (SHPD.DatePaid >= '7/1/2005' AND SHPD.DatePaid <= '7/31/2005'))
View 1 Replies
View Related
Feb 19, 2007
Hello, Everyone
I had 2 tables contain data:
1) old_data: 601,195 records
firstname
lastname
address
city
state
zip
zip4
2) current_data: 410,185 records
firstname
lastname
address
city
state
zip
zip4
3)Questions: How I write a query make to delete records? From table 'old_data' delete match records reference to table 'current_data' and keep the remain good records are from 'current_data' table.
****Here's below my test query that came up with if there are any error and please provide me any suggestion or new query. Very important task ....Thanks you to all.
/*******
Delete current_data
From old_data Cross Join current_data
Where old_data.FirstName = current_data.FirstName and old_data.LastName = current_data.LastName
and old_data.Address = current_data.Address
********/
RV
View 8 Replies
View Related
Feb 28, 2008
I would like to build a query that will return all the records in Table1 that will not match with records in table 2. All colums in table 1 have NULL values. Only one column is populated with state abreviations.
SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
-------------------------------------------
Result: Record count 3000.
Only field3 is populated everything else is null.
select field3 from tbl1 group by field2 - record count = 48
SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL2
-------------------------------------
Result Record count = 0
------------------------------------------------------
SELECT nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM TBL1
minus
SELECT
nvl(field1, 0),
field2,
field3,
nvl(field4, 0),
nvl(field5, 0),
nvl(field6, 0)
FROM tbl2
---------------------------
Result: Record Count = 48
I used the left join and it didn't work.
I would like to build a query that will display all 3000 records.
Any help will be appreciated.
thank you!
View 4 Replies
View Related
Oct 11, 2015
I want to know one small query..
id Name
1 hi
2 how
3 are
4 you
6 can
7 do
8 not
9 did
10 to
I deleted some records now my table have below mentioned rows..
id Name
1 hi
2 how
4 you
6 can
8 not
10 to
I want to know the missing records in my table.
OUTPUT IS. 3,7,9
how can i do that using sql query.
View 11 Replies
View Related
Sep 24, 2007
Hi,
I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.
How can I do this?
Thanks
View 6 Replies
View Related
