Reseting The Unique Id If A Table.

Jul 26, 2007

I'm building a web site. there is a database.

 

I've set a primary key and unique of a field.also I set it to auto numbering.

 Everytime I insert a record , that field will increase 1 (type bigint , start from 1).

 

After lots time of inserting record , the id going to be larger number.. I wondering how can I reset that to zero?

View 1 Replies


ADVERTISEMENT

Reseting The Auto-Number For A Table Back To Zero And Compact/Repair Or Unload/Reload For SQL Server 2000.

Mar 6, 2007

After deleting all the test data from all tables in a SQL 2000 database, is there a way to reset all the auto-incrementing fields back to zero in one shot? In Access, you can run the Compact and Repair option. Also, in Sybase SQL, there was an "unload/reload" option to reduce the database size. Is there a similar function in SQL2000? Thanks for all the help

View 1 Replies View Related

Reseting Identity Column Using SQL

Nov 11, 2006

Hi, Can anyone write me a script, how can I reset identity on column? ( I want to start records from 1 again)
Thanks, radco

View 1 Replies View Related

Reseting Select Permissions For Public Role

Sep 13, 2004

Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.

What can I do so that users are able to select from these tables after they are created?

Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?

All help is appreciated.

View 2 Replies View Related

Reseting Users After Restoring SQL Server 2005

Jan 9, 2008

I am restoring a production copy of db XYZ to the development server db XYZ. I want to retain the original users on the development server for db XYZ. How do I create a script that will reset the users on the development server with the same permissions and access they had before the production copy was restored on development? I have searched and searched and cannot find the answer. I can use a third party tool - Embarcadero - to script out the create and update stmts but it is not 100% correct. I end up having to do alot of manual work.

any assistance would be greatly appreciated.
thanks

View 5 Replies View Related

Integration Services :: Load Incremental Data Into Fact Table When Source Table Not Have Timestamp And Unique Key

Sep 24, 2015

I have a transaction table having about 40 crore rows in source. It don't have timestamp and unique key columns. It have only Bill_month and Bill_Year columns. Actually for loading this table into staging I have added a new datetime column by adding default bill_date as 01. Then

* First we delete last 3 month data from staging tables.
* Get last 3 months data from source table.
* Load that 3 months data from source to staging table. 

We do this because we only get update for last three months data. Now I have to include this transaction table as Fact table in DW. What will be the best practice for loading the fact table by picking data form staging table. Also we have to look up with dimensions for Foreign Keys. 

* Should I implement the same method of deleting last 3 months records and loading them again. 

View 3 Replies View Related

How To Archiv Table To Another Table With Unique Number For All Rows Once + Date

Apr 28, 2008

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
----------------------------------------------------

111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1

222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3

333 ccc 03/04/2008 4
333 ccc 04/04/2008 4

this is the table 2 i insert into
----------------------------------
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 666 15/04/2008 17:03
111 aaaa 16/03/2008 1 666 15/04/2008 17:03
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 666 15/04/2008 17:03
111 aaaa 20/03/2008 1 666 15/04/2008 17:03
111 aaaa 21/03/2008 1 666 15/04/2008 17:03

222 bbb 02/05/2008 3 666 15/04/2008 17:03
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3 666 15/04/2008 17:03
222 bbb 05/05/2008 3 666 15/04/2008 17:03
222 bbb 06/05/2008 3 666 15/04/2008 17:03
222 bbb 07/05/2008 3 666 15/04/2008 17:03
222 bbb 08/05/2008 3 666 15/04/2008 17:03
222 bbb 09/05/2008 3 666 15/04/2008 17:03

333 ccc 03/04/2008 4 666 15/04/2008 17:03
333 ccc 04/04/2008 4 666 15/04/2008 17:03

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
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 667 15/04/2008 17:15
111 aaaa 16/03/2008 1 667 15/04/2008 17:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 667 15/04/2008 17:15

.........................
.....................................................................667 15/04/2008 17:15


next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------

111 aaaa 15/03/2008 1 668 15/04/2008 08:15
111 aaaa 16/03/2008 1 668 15/04/2008 08:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 668 15/04/2008 08:15

.........................
.....................................................................668 15/04/2008 08:15



TNX

View 3 Replies View Related

Unique PK In Each Table

Oct 30, 2006

Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate.  Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks 

View 13 Replies View Related

Unique Id In More Than One Table

Jun 6, 2007

Hello,



I have a little problem with DB, I'm using MS SQL 2005 Express edition.

I have a few tables in my DB, every table contains a list of objects and I need unique ID for every object, I can do it for one table, but not for more then one.



Is there any solution for this problem?



Thanks

John

View 2 Replies View Related

Unique Identifier Or Int For Table ID

Feb 29, 2008

I am working on some tables and would like to know which is best way to go when deciding what Type to use for Unique ID in my tables please. Int  Or uniqueIdentifier? Or is it all the same??

View 11 Replies View Related

Help Me Getting A Unique Combination From Table

Jun 18, 2004

Hi, I have following table

BetId GameId
==== ======
500 108
500 109
501 108
501 109
501 110
502 108
502 109

My query would have the form: select BetId where GameId in(108,109)
from Bets then it has to get me BetId : 500 and 502.
Not 501,since this is different combination(108,109,110) ;)

View 3 Replies View Related

How Can We Have UNIQUE Constraint To The Table...

May 19, 2008

Hello...
I was confused with UNIQUE key. Its easy to create PRIMARY KEY to the Table. Now how to alter the Table to have UNIQUE key...

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

View 3 Replies View Related

A Table With A Unique Field

Jul 10, 2007

Hello,

I would like to store some frequently asked questions in a database.
As those questions/answers could be translated in several languages, I was thinking of using two tables :

The first table would contain a unique field : the key wich is an id for each faq.

The second table would contain those fields :
FAQ_ID : foreign key
LANG : language
QUESTION : the question translated in the good language.
ANSWER : the answer translated in the good language.

What I find embarrassing, is that first table just contains a unique field : the id. But it allows :
- to have a table in which a line correspond to an object. It is convenient for object relational mapping.
- it is more evolutionary. If, in the future, I have to add some statistics that concerns a faq, I will have the possibility to store them in the first table, avoiding in this way to duplicate data.

But, one more time, I find a bit embarrassing having a table with a unique field...

What is your opinion about that ?

Thank you in advance for any suggestion.

mathmax

View 20 Replies View Related

How To Construct Table With Unique PK

Oct 31, 2006

Let say I have 6 tables. I want to autogenerate the PK for each table and that is unique for each table and cant be duplicated on other tables. Let say I have table with PK of 1, so table2 to table6 wouldnt have a PK of 1. If table2 have a PK of 2, table1, table3 to table6 wouldnt have a PK of 2. Same for others. Identity will not be appropriate. Will 'uniqueidentifier' data type suffice? How bout guid? Or what must be my datatype? Or what will I do to implement this? Any links? Thanks

View 8 Replies View Related

Append Only Unique Records In SQL Table

Nov 23, 2007

I have a stored procedure that appends data from a temp table to a destination table.  The procedure is called from an aspx web page. The destination table has an index on certain fields so as to not allow duplicates.
The issue I'm having is if the imported data contains some records that are unique and some that would be duplicate, the procedure stops and no records are appended. How can I have this procedure complete it's run, passing over the duplicates and appending the unique records? Since the data is in a temp table (which gets deleted after each append) should I run some sort of 'find duplicates' query, and delete the duplicates from the temp table first, then append to the destination table?
Thanks in advance.SMc

View 2 Replies View Related

Unique Counter Field In A Table - Best Way?

Oct 11, 2005

I need to to have a table that has a counter field (used to generate an id code for a record).  This is not the primary id for the field but just a counter to label a record in another table, similar to a registration code for an event.  This is a sequential counter that will start 1 and go up on each new record inserted into other tables.  What I want to do is be able to query this counter table to get the next valid number and increment the count for the next time.  This is a multi user web app so needs to prevent duplicate use of same number.How best should this be handled?  A transaction to query and update the field?

View 6 Replies View Related

Creating A Unique Temp Table.

Jan 11, 2007

Set Quoted_Identifier On
Go
Set Ansi_Nulls On
Go

Alter Procedure spReport_SomeFooReport
@SearchFromThisDate datetime = null, @SearchToThisDate datetime = null

As
Declare @TableUniqueIdentifier varchar(80), @SQLString varchar(5000)

set @TableUniqueIdentifier = newid()
set @TableUniqueIdentifier = 'Report_SomeFooReport' + @TableUniqueIdentifier
set @TableUniqueIdentifier = replace(@TableUniqueIdentifier, '-', '7')
set @SQLString = 'Create Table ' + @TableUniqueIdentifier + ' (xxx varchar(40))'
exec @SQLString

Return
Go
Set Quoted_Identifier Off
Go
Set Ansi_Nulls On
Go


-------------------------------------------
the error is:
Server: Msg 2812, Level 16, State 62, Line 12
Could not find stored procedure 'Create Table Report_SomeFooReport06EEEC8D7EA6A74D0178EDD79E999B (xxx varchar(40))'.

So may'be a format issue or something,
im trying to create "temp" tables for sql 2005 report services in my Stored procedures which would have a sql job to get deleted at 23:00

View 5 Replies View Related

How To Receive Unique Row In Database Table

Feb 26, 2013

I am getting problem in unique row in database table, but not getting unique row because I have used Distinct keyword but not getting unique row so how can we do?

I have two table one table information another table Id. But second table in two code same but I am using distinct keyword i get some row unique but second table in two row in same code but when i am fetching row same auto_id render same id create duplicate row. But I am getting only unique row.

View 1 Replies View Related

How To Know If A Table Has Non-unique Clustered Index

Oct 31, 2015

Give a user table MyTable. How to know whether the table contains a non-unique clustered index by using SQL query?

View 1 Replies View Related

New Unique Number In History Table

May 23, 2008

Hello,
I am using SQL Server 2005 and am having trouble with making a history table like mentioned in my earlier thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102811

This is the table "People" I have created:

|PersonId (PK)|DateFrom (PK)|DateTo|PersonName|Other Attributes....

Each change to a person's attributes results in a new row formed with the same PersonId as in the row with old attributes and the Date these new attributes are valid (DateFrom). So as shown above the Primary Key is a combination of the PersonId and DateFrom as a change to a person's attributes should never happen at the same time twice.

My problem is when I want to create a new person, how do I get a new unique id? Ideally I want the a new incremented id, so that all peoples' ids are in a sequential order.

As always, thanks for the help!

View 4 Replies View Related

Determine That ID Is Unique In Customer Table

Oct 12, 2014

I would need to rewrite SQL code to determine that id is unique in the Customer table.

My two tables are:

CREATE TABLE CUSTOMER(
[CUSTNO] varchar(5) NOT NULL,
[ID] CHAR(9) NOT NULL,
[NAME] VARCHAR(128) NOT NULL,
[ADDRESS] VARCHAR(128) NOT NULL,
[DATEOFBIRTH] DATE NOT NULL,

[Code] ....

View 2 Replies View Related

Unique Constraint To A Column From Another Table

Oct 29, 2005

Is it possible to create a unique constraint to a column from anothertable? For example:tb_current:current_names--------------aaabbbtb_new:new_name--------cccNow I want to create a constraint on tb_new.new_name to be unique withrespect to tb_current.current_names. However, tb_new.new_name shouldnot be unique to itself. So I should not be able to insert 'aaa' totb_new.new_name. But I should be able to insert 'ccc' totb_new.new_name.Here's the script to reproduce this example:create table tb_current(current_names varchar(10))create table tb_new(new_name varchar(10))insert tb_current values ('aaa')insert tb_current values ('bbb')insert tb_new values ('ccc')select * from tb_currentselect * from tb_newinsert tb_new values ('aaa') -- this should NOT be allowedinsert tb_new values ('ccc') -- this should be allowed

View 3 Replies View Related

Unique Index For Two Columns In A Table

Jul 20, 2005

Hi,I would like to add a unique index that consists of two fields in atable.e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combinationmust be Unique.Can anyone tell me the actual sql syntax to create this index?Thanks,June.

View 3 Replies View Related

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

Jul 5, 2015

This index is not unique

ix_report_history_creative_id

Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.

The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161

The statement has been terminated.

Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).

The statement has been terminated.

View 6 Replies View Related

Insert Unique Rows In Temp Table

Nov 3, 2006

i have temp table name "#TempResult" with column names Memberid,Month,Year. Consider this temp table alredy has some rows from previuos query.  I have one more table name "Rebate" which also has columns MemberID,Month, Year and some more columns. Now i wanted to insert rows from "Rebate" Table into Temp Table where MemberID.Month and Year DOES NOT exist in Temp table.
MemberID + Month + Year should ne unique in Temp table

View 1 Replies View Related

How To Return An Unique From Two Table With Duplicated Records?

Sep 27, 2005

Hello, everyone:

I have two tables with some duplicated records like,

ZZZTest:
C_IDC1C2C3
10AAA
20BBB
30AAA
40BBB


ZZZTestTable:
D_IDC11C22C33
1AAA
2AAA
3BBB
4BBB
5AAA
6AAA
7BBB


I wand to get the unique records by SELECT / JOIN statement. Now I used a query,

SELECT * FROM ZZZTest t
INNER JOIN ZZZTestTable tt
ON t.Col1=tt.Col11 AND t.Col2=tt.Col22 AND t.Col3 = tt.Col33

and got the records like,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA1AAA
10AAA2AAA
30AAA2AAA
20BBB3BBB
40BBB3BBB
20BBB4BBB
40BBB4BBB
10AAA5AAA
30AAA5AAA
10AAA6AAA
30AAA6AAA
20BBB7BBB
40BBB7BBB



What I am expecting is,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA2AAA
20BBB3BBB
40BBB4BBB

Any suggestion will be great appreciated.

Thanks

ZYT

View 3 Replies View Related

Getting Unique Rows From Multi-table Query

Mar 10, 2012

how to join these 3 tables but there are duplicate rows in the results. How can I get all the unique rows to only show up once.

My query is:

SELECT tbl_rate.rate_ID, tbl_rate.rate_site_IDref, tbl_rate.rate_rank_IDref, tbl_rate.rate_dollar, tbl_rate.rate_ot, tbl_rate.rate_dt, tbl_rate.rate_loa,
tbl_employee.employee_name, tbl_LEM.LEM_date, tbl_LEM.LEM_reg_hrs, tbl_LEM.LEM_ot_hrs, tbl_LEM.LEM_dt_hrs, tbl_LEM.LEM_tt_hrs, tbl_LEM.site_IDREF,
tbl_LEM.LEM_LOA, tbl_LEM.LEM_Expenseinfo, tbl_LEM.LEM_workorder, tbl_LEM.LEM_posted, tbl_LEM.LEM_Expense, tbl_LEM.LEM_tagnumberREF,
tbl_LEM.LEM_reg_rate, tbl_LEM.Lem_ot_rate, tbl_LEM.Lem_tt_rate, tbl_LEM.LEM_dt_rate, tbl_LEM.Lem_loa_rate, tbl_LEM.LEM_ID, tbl_LEM.LEM_equip_days

FROM tbl_rate INNER JOIN
tbl_employee ON tbl_rate.rate_rank_IDref = tbl_employee.employee_rankREF INNER JOIN
tbl_LEM ON tbl_employee.employee_ID = tbl_LEM.employee_IDREF

The results somehow need to be distinct by tbl_lems.lem_ID

View 4 Replies View Related

SQL Server 2012 :: Unique Index On Table

Mar 20, 2015

I am trying to create a unique index on a table such that the combination of 2 columns is unique. How do I go about that?

View 5 Replies View Related

Finding Unique Values From Associative Table

Apr 15, 2015

I have a table which maps two related IDs. That table has 3 columns: ID, BHID & EPID. I need to find all of the BHIDs where the EPID is unique. It seems easy enough, but I keep going in circles..

USE [CGB]
GO
/****** Object: Table [dbo].[ePID_BHID] Script Date: 04/15/2015 15:48:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 2 Replies View Related

Get Next Unique ID From A Table Before Insert @@identity / Sequence

Jul 23, 2005

How do I get the next int value for a column before I do an insert inMY SQL Server 2000? I'm currently using Oracle sequence and doingsomething like:select seq.nextval from dual;Then I do my insert into 3 different table all using the same uniqueID.I can't use the @@identity function because my application uses aconnection pool and it's not garanteed that a connection won't be usedby another request so under a lot of load there could be major problemsand this doens't work:insert into <table>;select @@identity;This doesn't work because the select @@identity might give me the valueof an insert from someone else's request.Thanks,Brent

View 4 Replies View Related

How To Force Unique Entries In A Linking Table?

Jul 20, 2005

I have a table 'Group2Operation' that stores many to many relationsbetween the 'Group' table and the 'Operation' table (each group is haspermission to perform one or more of the available operations)PROBLEM=======I need to prevent duplicate entries being created. e.g. lets say thatin the 'Group2Operation' table a record links the 'editor' group tothe 'publish' operation. Should I prevent an administrator creating aduplicate of that record? (Otherwise deleting that permission willhave to be done twice or more for it to be effective)SOLUTION?=========So far I've done this with a trigger:CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2OperationFOR INSERT, UPDATEAS UPDATE Group2OperationSET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTagThe 'NoDuplicate' unique index column in the Group2Operation tablestores a concatenation of the unique group and operation identifiers.So when an attempt is made to create a record, the trigger is fired.If there is a duplicate, this will mean a duplicate entry in the'NoDuplicate' column. As a result, the INSERT or UPDATE will fail andthe duplication will be prevented.WHAT DO YOU THINK?==================What do you think? Am I going about this in the right way? Is atrigger a good way to do this or should I rely on application logic toprevent duplicates?Any help appreciated by this db novice.John Grist

View 2 Replies View Related

Table Relationship Question For Unique Record

Apr 9, 2008

Hello Hello,
This Noob has a question.
I have been atempting for days to figure this out.
I have this set up: (See Below)

The Problem I am having is occuring at the Table named OU in Maroon below.
It combines 2 chains of keys into one table to try and make a unique record.
The problem is that it is not.

Pasted below is a Query run on the data within the tables:
As you can see certain data is getting duplicated in this tabel.
I want 1 Unique record in the OU table based upon the Study ID Primary Key and the zCombined Primary Key.

Any Ideas as to what is wrong, or how to make it work would defineatly be appreciated.

Thanks a Bunch


Table: Sponsor
PK: Sponsor ID

Table: Protocol
PK: ProtocolID
FK: Sponsor ID

Table: Study
PK: StudyID
FK: ProtocolID
Table: OUPK: OUIDFK: StudyIDFK: zCombinedID

Table:zCombined
PK: zCombinedID
FK: TempID ~ To Temp Table
FK: ShipTypeID ~ To Ship Type Table
FK: CoordinatorID ~ To Coordinator Table
FK: BoxTypeID ~ To Box Type Table







Sponsor

Protocol

Study

OU~Column1

OU~Column2

Box

Coordinator

Ship Type

Temp


Omni

AAAAA

XPG

S0101

NA

Wheaton

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

Matrix

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

B-1

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

9 x 9 4mL

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

NA

9 x 9 2mL

Raul Vargas

Daily

-70


Omni

AAAAA

XPG

S0101

S0201

Wheaton

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

Matrix

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

B-1

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

9 x 9 4mL

Mike Keane

DNA

-40


Omni

AAAAA

XPG

S0101

S0201

9 x 9 2mL

Mike Keane

DNA

-40

View 3 Replies View Related

Lookup Primary Key Of A Master Table Which Is Does Not Contains Unique Key

Mar 21, 2008



Hi
I have a strange requirement in ETL operation. My Source contains only the details table data. Out of it, I have to load the master table and refer the Master table primary key ID to load Details table. I can easily load the Master Table with aggregate transformation. But problem is how to look up the Master Table Primary Key ID to load the Details table, as the master does not contains any unique key to lookup. This may seems to be strange but this is my requirement. You can refer the Source and Destination data model as below. They may give you clear picture. Can you guys help me out on this?

My Source Table
--------------------------








Citye Type
City Name

Source
New York

Via City1
Pittsburg

Destination City
Chicago

Source
New York

Via City1
Philadelphia

Destination City
Chicago


My Destination
------------------------






Master Table

ID

1

2









Detail Table



Master Table ID
Citye Type
City Name

1
Source
New York

1
Via City1
Pittsburg

1
Destination City
Chicago

2
Source
New York

2
Via City1
Philadelphia

2
Destination City
Chicago


Thanks

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved