Missing 00's In My Zipcode Table
Mar 7, 2008
I have a zipcode table that is missing the leading 0's.
Currently Need
501 00501
1001 01001
How would I go about adding the missing 0's?
ADVERTISEMENT
Recommendations For Placement Of ZIPCode Table
Apr 30, 2007 We are creating a company-wide table of ZipCodes, States, GPS info, etc. This table can be used by our development and production servers (many of them.) We could place the table on a given server and use linked servers to grant access to that table to the other servers. But is there a better way to handle this globally-useful table?
Barkingdog
P.S. Clearly, we don't want to have multiple copies of this table scattered around on various servers. That introduces synchronization issues.
IDENTITY Problems Updating ZipCode Table
Aug 16, 2004I am having problems updating my zip code table that contains zip, city, state, long, lat, ect..
I have the latest CSV file, I tried to use the import wizard in SQL Server 2000 Enterprise Manager.
I set the ID field as <ignore> and specified the appropriate columns for the rest of the data matching from CSV to already designed and working zip code table. Also I checked the box that said "Delete Rows in Destination Table" as well as "Enable Identity Insert" was checked
I ran the wizard, and now I have empty table and it will not insert any records because the error said that the identity column can not accept NULL.
What do I do? I am not updating the identify column so Is it telling me it can't insert NULL into ID?
Any suggestions...
Thanks,
Lito
Know Of Any SQL Function That Would Chop-off The Hyphen From The Zipcode??
Dec 14, 2006Anyone know what's the MS-SQL function that look at the zipcode like "30296-6912" and chop off the "-6912" suffix by just looking at the hyphen part?
Thanks...
Need Help With GROUP BY And COUNT On 9 Digit Zipcode
Sep 3, 2007I have a table with a column 'zipcode' that contains a 9 digit zipcode. I am am trying to figure out how I can create a query that returns the count of the most popular zipcodes based only on the first 5 digits.
This is what I have
Code:
SELECT Customers.Zipcode, Count(Customers.Zipcode) AS CountOfZipcode
FROM Customers
GROUP BY Customers.Zipcode
ORDER BY Count(Customers.Zipcode) DESC;
which does what I want it to do except it uses the whole 9 digits.
Any help would be appreciated
Selecting Nearby Distinct City/zipcode Issue
Mar 12, 2004I have the following code, which returns a list of nearby cities based on a city name as input. Most cities have multiple zipcodes per city name, thus it can list multiple rows with the same city name, but with different zipcodes like below:
Zip | Cityname
111 belmont
112 belmont
113 belmont
114 san francisco
115 san francisco
---------------- etc----------------
I do not really care about each group of zipcodes. I only need one pair of zipcode/city name like the following:
ZIP | City name
111 belmont
114 San Francisco
How do I change my select to only return a distinct city name. I do not care which if the city/zipcodes it returns from the similar city.
The select statement is below:
CREATE PROCEDURE ZipSearchByCity
@city varchar(40),
@State varchar(5),
@distance int
AS
SELECT distinct o.City AS City, o.zip_code, o.State AS State,
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) dist
FROM zipcodes z,
zipcodes o,
zipcodes a
WHERE z.city = @city AND
z.State = @State AND
z.zip_code=a.zip_code AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) +
COS(z.RADlatitude) *
COS(o.RADlatitude) *
POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2)
)))) < @distance
GO
Thanks,
Christian
Stored Procedure To Retrieve Zipcodes Within A Specified Zipcode And Distance
Apr 28, 2008Hi All,
Does anyone have a Stored Procedure that works perfectly to retrieve all zipcodes within a specified zipcode and distance radius - a zipcode and radius is passed and the Store Procedure result shows all zipcodes that falls within that range.
Thanks in advance
Ade
How To Do-table 1 That Check Table 2 And Adding Missing Dates
Dec 20, 2007can sql server do this ?
table 1 that check table 2 and adding missing dates
this my employee table
table 1
table Employee on work
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
-----------------------------------------------------------------------------------
and i need to see the missing dates lkie this
in table 2
------------------------------------------------------
table 2 (adding missing dates with zero 0)
table Employee_all_month
------------------------
empid basedate shift
----------------------------
12345678 01/04/2007 1
12345678 02/04/2007 1
12345678 03/04/2007 1
12345678 04/04/2007 1
12345678 05/04/2007 1
12345678 06/04/2007 1
12345678 07/04/2007 1
12345678 08/04/2007 1
12345678 09/04/2007 1
12345678 10/04/2007 1
12345678 11/04/2007 0
12345678 12/04/2007 0
12345678 13/04/2007 0
12345678 14/04/2007 0
12345678 15/04/2007 0
12345678 16/04/2007 0
12345678 17/04/2007 0
12345678 18/04/2007 0
12345678 19/04/2007 0
12345678 20/04/2007 0
.................................and adding missing dates with zero 0 until the end of the month
.................................
12345678 31/04/2007 0
98765432 01/04/2007 0
98765432 02/04/2007 0
98765432 03/04/2007 0
98765432 04/04/2007 0
98765432 05/04/2007 0
98765432 06/04/2007 0
98765432 07/04/2007 0
98765432 08/04/2007 0
98765432 09/04/2007 0
..............................and adding missing dates with zero 0 only whre no dates in this month
.......................
98765432 20/04/2007 1
98765432 21/04/2007 3
98765432 22/04/2007 3
98765432 23/04/2007 5
98765432 25/04/2007 4
98765432 26/04/2007 4
98765432 27/04/2007 4
98765432 28/04/2007 4
98765432 30/04/2007 4
TNX
Why 2005 Is Missing PIN TABLE?
Apr 16, 2006I am shocked to read this document...
http://msdn2.microsoft.com/en-us/library/ms144262.aspx
DBCC PINTABLE, DBCC UNPINTABLE >>> No replacement
Are they serious?
How are we gonna use our memory?*
Iam confused....
*the way I like
TaskPad Missing Table Info
Apr 5, 2001Hi:
After I used "copy database" to upgrade a SQL 7 database to SQL 2000, all are fine except the TaskPad view. The table info section is blank. Though we know all tables are in the table object.
Any thought?
David
T-SQL (SS2K8) :: Get Missing Dates In Table
Aug 18, 2014I've a request: I've a table with a date column and an if numeric data type.
I've to check from now on 7 days ago if any value is missing in my date field.
So if I've f.i. in my table
ID DAYS
1 2014-08-11
2 2014-08-12
3 2014-08-13
4 2014-08-14
5 2014-08-17
then my output should be:
2014-08-15
2014-08-16
2014-08-18
How to do this?
SQL Server 2014 :: How To Add Missing IDs In Table
Jan 12, 2015I have a scenario where I would need to add +4 IDs with the existing IDs, below is an example:
IDWorkloadUnits
1EXO 3
7SPO 4
15LYO 10
Desired output should be as follows:
IDWorkloadUnits
1EXO 3
2
3
4
5
7SPO 4
8
9
10
11
15LYO 10
16
17
18
19
I am not worried about other attributes in the same table.
Total Missing In Part Of Table
Apr 30, 2015I want to add a new column in a table. In this column I want to include a Total Value. This Total Value exists already for dates after Sept 23, 2013. For dates before Sept 23, 2013 the total can be calculated using the following math:
Total = Total(t-1) - (Resource_Name1-Resource_Name1).
The Resource_Name column contains the Total field (after Sept 23) as well as the Resource_Name1 field. There is a third column called Direction which contains the values injection or withdrawal. The Resource_Name1 differes in value depending on whether it is injection or withdrawal. How do I create a new column with Totals for the full data set?
In Query Analyzer Some Table Are Missing
Apr 10, 2007Dear All
i have a problem In Query Analyzer .When I create two diffrent table with two diffrent schemas dbo and guest but when i log on query analyzer and run query from guest schema's tahle it gives error object not found i think its some rights problem ..
Table SUPPOSEDLY Missing From Sysobjects
Jan 18, 2008Reposted from 2000 area per suggestion. The site is running SQL 2000 build 2187.
I've got an 'accidental client' (sister company without a DBA at the moment) who has a problem that I've never seen before. They have a table that SQL claims to not exist in sysobjects. But it does exist in sysobjects as a User Table:
quote:select * from MyTable
Msg 604, Level 21, State 5, Line 1
Could not find row in sysobjects for object ID 55671246 in database 'MyTable'. Run DBCC CHECKTABLE on sysobjects.
dbcc checkdb ('MyDatabase')
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable2'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable3'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable4'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable5'. Check sysobjects.
DBCC results for 'routingengine_historical'.
DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 310 rows in 21 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 839 rows in 20 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 470 rows in 35 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 3 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 145 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 19 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 421 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 4 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
dbcc checktable ('MyTable') with ALL_ERRORMSGS
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.
dbcc checktable ('sysobjects') with ALL_ERRORMSGS
DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I've fully verified the correct existence of the table and how it links to the object ID by running statements like:
quote:select * from sysobjects where name = 'MyTable' and type = 'U'
select object_id('MyTable')
select object_name(55671246)
Odd that when selecting data from the table the error refers to the database by the table name. Running checktable on sysobjects shows no errors.
Running sp_help 'MyTable' works and shows the expected results as far as columns, indexes, and such. The table structure is fully visible in Management Studio and can be visually explored. Only when querying data is there an issue.
All tables are owned by dbo.
Ideally I need to recover these tables. If that is impossible, I'd like to remove the tables and cleanly free up all the related data pages.
Any thoughts?
Find Missing Data From Table
Jul 23, 2005Here is an issue that has me stumped for the past few days. I have atable called MerchTran. Among various columns, the relevant columns forthis issue are:FileDate datetime, SourceTable varchar(25)SQL statement:SELECT DISTINCTFileDate, SourceTableFROMMerchTranORDER BYFileDate, SourceTableData looks like this:FileDate DataSource-----------------------------------2005-02-13 00:00:00.000S12005-02-13 00:00:00.000S22005-02-13 00:00:00.000S32005-02-14 00:00:00.000S12005-02-14 00:00:00.000S22005-02-14 00:00:00.000S32005-02-15 00:00:00.000S22005-02-15 00:00:00.000S32005-02-16 00:00:00.000S12005-02-16 00:00:00.000S22005-02-16 00:00:00.000S32005-02-17 00:00:00.000S12005-02-17 00:00:00.000S22005-02-18 00:00:00.000S12005-02-18 00:00:00.000S22005-02-18 00:00:00.000S32005-02-19 00:00:00.000S12005-02-19 00:00:00.000S3We run a daily process that inserts data in to this table everyday forall 3 sources S1, S2, S3Notice how some data is missing indicating the import process for thatparticular source failed.Example: Missing record2005-02-15 00:00:00.000S12005-02-17 00:00:00.000S32005-02-19 00:00:00.000S2Can someone please help me with a SQL Statement that will return me the3 missing records as above.Thanks in advance for all your help!DBA in distress!Vishal
View 4 Replies View RelatedTransact SQL :: How To Get Missing Records From One Table
Apr 22, 2015I 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])
And the results would be:
4/3/2015 bld1
SQL 2012 :: Find Missing References Of Each Table
Oct 12, 2015I have a database of 900+ tables with around 3000 SPs, and views. Manually I reviewed few tables and found that tables are not referenced with FK and I applied few. There are lots of tables and SPs using them in join statement, Is there any way with which I can get each tables missing references, any DMV or other manual script which tells about this?
View 2 Replies View RelatedTemp Table - Missing A Command, Error
Jun 22, 2007Here I am creating a temp table with $ summations that I can later join with an employees table that I'm dumping into a flat file.
select
e.employee_no,
sum(p.fit) as sumfit,
sum(p.fica) - sum(p.medicare) as sumfica,
sum(p.medicare) as sumedic,
sum(p.fit_earnings) as pearnings,
sum(p.fit_earnings) as tearnings
into #earntable
from employees as e
left outer join pay_summary as p on e.employee_no =p.employee_no
where e.employee_no = 817 and
dateadd(d, 0, datediff(d, 0, p.dated)) between '20061231'and '20070401'
group by e.employee_no
select * from #earntable
When I go to look at the contents of the #earntable with the above select, I get this:
ODBC error 214 Procedure expects parameter '@handle' of type 'int'.
(42000)
The datatypes I'm trying to select into the temp table are all
numeric 12 except employee_no char 10.
What am I missing? A drop table statement?
How To Add A Missing Index To A Table In Merge Replication?
Jul 23, 2005One of the table that is in a merge replication somehow is missing anindex. Strangely, only the table in one of the subscriber of the mergereplication is missing the index; another subscriber and the publisherof the merge replication don't have this problem.How should I add the missing index back to that table? My understandingis that making structural change on a table that is inmerge-replication is different from making change on a table that isnot merge-replicated. For example, when we need to add a column into atable that is being merge-replicated, we must add the column bychanging the attributes in the properties of the published article(table) instead of simply using CREATE INDEX command. I am wonderingwhether there is a similar restriction on adding an index onto a tablethat is merge replicated.I have already added the index back to the table anyway. I am askinghere just in case doing this may get me into a problem later on.Thanks in advance for any info.Jay Chan
View 3 Replies View RelatedLocate Rows In Table Where Column Value Missing (I Think!)
Feb 8, 2007SQL 2k, DDL below.I have a simple table with the following data:fldYear fldCode1 fldCode22000 ABC1 ABC122000 ABC1 ABC132001 ABC1 ABC122002 ABC1 ABC122002 ABC1 ABC13I need to know, for every distinct combination of fldCode1 andfldCode2, if there are any years missing.For example,SELECT DISTINCT fldCode1, fldCode2 FROM MyTablereturnsABC1 ABC12ABC1 ABC13I need to know that in 2001 there was no entry for ABC1/ABC13Thanks!Edwardif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[MyTable]GOCREATE TABLE [dbo].[MyTable] ([fldYear] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldCode1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldCode2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO
View 2 Replies View RelatedMissing Table Data In SQL 2005 Standard
Oct 17, 2007Hello,
There has been a sudden loss of data in some (not all) of the tables in my database. I am using SQL Sever 2005 Standard with a simple recovery model for a single database. I can't find any specific hints in the logs to explain the loss, and this did not occur during a backup or scheduled maintenance routine. It seems the data just vanished at a specific point this morning, and I am not able to find out why. Do any of you have a suggestion that would help explain the loss of data in specific tables and not others in a given database without the known use of ROLLBACK or DELETE? Or do any of you know a way that I can look back in the server to identify what may have happened? Any help would be appreciated.
Thanks,
Billy
Insert Missing Primary Key Utilized By Another Table
May 6, 2015I am converting an old Access/Forms application to a .Net/SQL intranet site. The data was imported and most CRUD features are working. But, I have some data issues to contend with.For this example I have two tables, Cases and Parties. The Parties table includes a Column ReportID, which happens to reference the primary key of some unique Case. Lets say Bob, Sally, and Mary are all associated with ReportID = 2.Looking at the Case table, I see indexes (ReportID) 1,3,4,5, such that Bob, Sally, and Mary are orphaned with out a case to reference.
1. Is it possible to bypass the auto-increment for the primary key of Cases and manually inject the missing Case (ReportID) with a specific primary key of 2? See my image to fill in some of the details.It is too early to tell but perhaps 10% of the Parties are orphan without an associated ReportID in the Case table. This fact is one of the reasons it was decided to rebuild the application, which was also never able to produce any reports.
2. What is an easy way to find all the orphan Parties?
I suppose I could export the Cases table to excel, add the missing Rows, and then overwrite the defective Cases table data with the corrected data. I would rather learn to fix the data issues using the responses from this request.
Select Top (n) From &&<table&&> Missing From Management Studio
Sep 14, 2006In sql 2000 one could right-click a table (under Enterprise Manager) and have it return the "top n" rows in the table. That feature seems to be missing from Management Studio. Am I correct or is it just hiding somewhere?
TIA,
Barkingdog
SQL Server 2008 :: Check For Missing Rows In A Table
Oct 20, 2015I have a table of languages, identified by a lang_id column as follows:
LANG_IDLANG_NAME
deDeutsche
enEnglish
plPolski
trTurkish
czCzech
I also have a RESOURCES table of phrases (for translation purposes) similar to this:
res_id res_lang res_phrase
AccessDenied en Access Denied
For some rows in the resources table I do not have all language codes present so am missing some translations for a given res_id.My question is what query can I use to determine the RESOURCE.RES_IDs for which I do not have a translation for.
For example I might have a de, en, cz translation for a phrase but not a pl phrase and I need to identofy those rows in order that I can obtain translations for the missing RESOURCE rows.
Missing Fields In MSmerge_history Table Under SQL Server 2005
Sep 5, 2006Hi,
I have a query that uses the following fields from MSmerge_history:
MSmerge_history.start_time, MSmerge_history.runstatus and MSmerge_history.duration
Below is the query that I am using:
SELECT
MSmerge_agents.subscriber_name AS SubscriberName,
MSmerge_history.start_time AS SyncTime,
MSmerge_history.runstatus AS SyncStatusID,
MSmerge_history.comments AS Comments,
MSmerge_history.duration AS Duration
FROM distribution.dbo.MSmerge_agents MSmerge_agents INNER JOIN distribution.dbo.MSmerge_history MSmerge_history
ON MSmerge_agents.id = MSmerge_history.agent_id
WHERE MSmerge_history.runstatus IN (2, 6) AND publisher_db = DB_NAME()
AND MSmerge_agents.subscriber_name + CONVERT(nvarchar, MSmerge_history.start_time) NOT IN
(SELECT SubscriberName + CONVERT(nvarchar, SyncTime) FROM SyncActivities)
My query runs fine under SQL Server 2000 but when I run it in SQL Server 2005, it doesn't work any more. Looking at MSmerge_history table under SQL Server 2005, this fields have been removed. Does anyone know where I can access those fields? Is it in another table?
thanks
Romeo
What Am I Missing Here? Using A Stored Proc Output To Build Table Rows
Nov 27, 2007Hi all,
I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.
To continue with the set up, here is the stored procedure in question:
CREATE PROCEDURE [dbo].[HE_GetStks] AS
select top 15
Rating,
rank,
coname,
PriceClose,
pricechg,
DailyVol,
symbol
from
(selectf.rating,
f.rank,
s.coname,
cast ( f.priceclose as decimal(10,2)) as PriceClose,
cast ( f.pricechg as decimal(10,2)) as pricechg,
f.DailyVol,
f.symbol
from dailydata f, snames s
where f.tendcash = 0
and f.status = 1
and f.typ = 1
and f.osid = s.osid) tt
order by rating desc, rank desc
GO
The code in the calling bat file is:
REM *************************
REM BCP .WRK FILE
REM *************************
bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:TABLESINPUTHE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>
This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.
Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.
I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.
Any thoughts?
Transact SQL :: Insert Rows Into A Table For Missing Sequence Numbers
Jul 29, 2015In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[Code] ....
Inserted Records Missing In Sql Table Yet Tables' Primary Key Field Has Been Incremented.
Jun 18, 2007I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.
I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.
And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
Fix Legacy Data - Missing Primary Key + Duplication Record + Large Table
Nov 17, 2005We have a large table which is very old and not much ppl take care about, recently there is a performance problem from the report need to query to this table. Eventally we find that this table have primary key missing and there is duplicate data which make "alter table add primary key" don't work
Besides the data size of this table require unacceptable time to execute something like "insert into new_table_with_pk from select distinct * from old table"
Do you have any recommendation of fixing this? As the application run on oracle , sybase and sql server, is that cross database approace will work?
SQL Server 2012 :: Set Based Method To Insert Missing Records Into Table - Right Join Not Work
Apr 24, 2014I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)
Table A could look like the following where the multiple employees could have multiple types and multiple stages.
EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2
Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.
Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
Transact SQL :: Find Missing Months In A Table For The Earliest And Latest Start Dates Per ID Number?
Aug 27, 2015I need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth)
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'
For the above table, the answer should be:
ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01
1) don't include an ID column,
2) don't use the start date/end dates in the data or
3) use cursors, which are forbidden in my environment.
Add Row If Missing
Jun 6, 2008Hi i have a sorted procedure that create a @temp table that return strNomObjet,strNomDescripteur,strReponse,Percentage
I need to calculate the Percentage for strReponse for each strNomDescripteur. strReponse can be = to A,B,C, or D
To calculate the Percentage i have no problem my is that something it possible that a certain strNomDescripteur that will have only A,B and D and there no C i need to put 0%.
Here what i have
DECLARE @Temp table
(
strLastName varchar(50),
strFirstName varchar(50),
strReponse char(10),
strNomObjet nvarchar(200),
strDescripteurOrdre char(10),
strNomDescripteur nvarchar(200),
strObjetOrdre char(10)
)
INSERT INTO @Temp
Select te.stdlastn,te.stdfirstn,COALESCE(NULLIF(REPLACE(tr.T3,'*',''),' '),REPLACE(tr.T2,'*','')),tob.Nom,tob.Affichage,td.Nom,td.Affichage
fromBulletin.dbo.tblEleves as te
INNER JOIN Bulletin.dbo.tblBulletin as tb ON te.stdnumber=tb.NoEtud
INNER JOIN Bulletin.dbo.tblNomBull as tnb ON tb.NoBull=tnb.ID
INNER JOIN Bulletin.dbo.tblSection as ts ON ts.ID_Bull = tnb.ID
INNER JOIN Bulletin.dbo.tblObjetas tob ON tob.ID_Sec = ts.ID
INNER JOIN Bulletin.dbo.tblDescripteur as td ON td.ID_Obj = tob.ID
INNER JOIN Bulletin.dbo.tblReponse as tr ON tr.ID = tb.ID
whereintActif = 1 and
tb.NoBull not like '%EdPhy%'
and tnb.Nom not like '%EdPhy%'
and tnb.Date = '2006-2007'
and te.STDGRADeID = 3
and te.schoolid = '1202'
and ts.Nom = 'MATHÉMATIQUES'
and tr.[Desc] = td.ID
and tr.T2 <> 'NULL'
and tr.T3 <> 'NULL'
and (tr.T2 <> '' OR tr.T3 <> '')
ORDER BY tob.Affichage,td.Affichage
SELECT t1.strNomObjet,t1.strNomDescripteur,t1.strReponse,
t1.ReponseCount * 100.0/t2.PersonCount AS Percentage
FROM
(
SELECT strNomObjet,strNomDescripteur,strReponse,count(*) AS ReponseCount FROM @Temp
GROUP BY strNomObjet,strNomDescripteur,strReponse)t1
INNER JOIN (SELECT strNomObjet,strNomDescripteur,count(*) AS PersonCount FROM @Temp
GROUP BY strNomObjet,strNomDescripteur)t2
ON t2.strNomObjet=t1.strNomObjet
AND t2.strNomDescripteur=t1.strNomDescripteur
This will give this result :
As you can see it always A,B,C,D A,B,C,D
Except for "FORMES utilise les transformations géométriques pour" it only A,B,C there no D.
So is there a way that i could insert D = 0
So if i don't find any A,B,C or D for certain strNomDescripteur put that letter = 0
Thanks for your help in advance!!
FORMES utilise la mesure pour résoudre des problèmesA 47.222222222222
FORMES utilise la mesure pour résoudre des problèmesB 38.888888888888
FORMES utilise la mesure pour résoudre des problèmesC 8.333333333333
FORMES utilise la mesure pour résoudre des problèmesD 5.555555555555
FORMES utilise les figures géométriques pour résoudre A 61.111111111111
FORMES utilise les figures géométriques pour résoudre B 25.000000000000
FORMES utilise les figures géométriques pour résoudre C 11.111111111111
FORMES utilise les figures géométriques pour résoudre D 2.777777777777
FORMES utilise les transformations géométriques pour A 62.857142857142
FORMES utilise les transformations géométriques pour B 28.571428571428
FORMES ET ESPACE (géométrie)utilise les transformatC 8.571428571428
NOMBRE ET OPÉRATIONSdémontre une compréhension A 69.444444444444
NOMBRE ET OPÉRATIONSdémontre une compréhension B 16.666666666666
NOMBRE ET OPÉRATIONSdémontre une compréhension C 5.555555555555
NOMBRE ET OPÉRATIONSdémontre une compréhension D 8.333333333333