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
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.Â
need help how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute) i need whan i insert to the another table add 2 more fields (unique number , date_time )
this is the table 1 i select from ID fname new_date val_holiday ----------------------------------------------------
this is the table 2 i insert into ---------------------------------- ID fname new_date val_holiday unique number date_time --------------------------------------------------------------------------------------------------------------------
for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)
next insert ...... ID fname new_date val_holiday unique number date_time --------------------------------------------------------------------------------------------------------------------
next insert ...... ID fname new_date val_holiday unique number date_time --------------------------------------------------------------------------------------------------------------------
I have 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.
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?
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??
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) ;)
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...
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
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
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?
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
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.
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.
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,
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
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.
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).
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
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
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
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
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.
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
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?
I am looking for pros and cons for the following scenarios:
When a table contains a unique key constraint is it viable to always do an insert and immediately check the @@ERROR value and if @@ERROR states a duplicate key exception then perform an update statement?
Another possible solution would be to always check if the key exists and then do the insert / update based upon that result. This method will always require two steps.
I am working on creating a report which is retrieving data from a SQL 2005 database and being displayed in a C#.net web page. Presently, I am binding the dataset to a ASP:Gridview on the web page. I am currently retrieving most of the needed fields using the following SQL statement:PROCEDURE [dbo].[pr_getReportTickets] @DateCreated nvarchar(15) ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure hereSELECT tbl_Queue.num_TicketNumber AS TicketID , tbl_Users.str_Name AS Technician , tbl_Queue.str_QueueLocator AS TechNTID , tbl_Queue.dat_ReceivedRequest AS dateCreated , tbl_Queue.dat_DueDate AS DueDate , tbl_Queue.str_TaskName AS TicketTitle , tbl_Queue.str_Requestor AS RequestorNTID --, I need most current dat_TimeStamp from tbl_Notes here for this record --, I need most current int_PercentComplete from tbl_Notes here for this record --, I need to sum up all of int_MinutesWorked fields from tbl_Notes here for this record , MasterEmp.dbo.fn_FormatFullName(tbl_employee.str_fname, tbl_employee.str_lname) AS RequestorName , tbl_Queue.str_TicketType AS TicketType , tbl_Status.str_TaskStatus AS TicketStatus , tbl_Severity.str_Priority , tbl_Complexity.str_Complexity , tbl_Severity.str_Priority + N' / ' + tbl_Complexity.str_Complexity AS Priority , tbl_Queue.dat_CompleteDate as DateCompleted FROM dbo.tbl_Queue LEFT OUTER JOIN MasterEmp.dbo.tbl_employee AS tbl_employee ON dbo.tbl_Queue.str_Requestor = tbl_employee.str_ntid LEFT OUTER JOIN tbl_Users ON tbl_Queue.str_QueueLocator = tbl_Users.str_ntid LEFT OUTER JOIN tbl_Status ON tbl_Queue.num_Status_CD = tbl_Status.num_Status_CD LEFT OUTER JOIN tbl_Severity ON tbl_Queue.str_Severity = tbl_Severity.str_Severity LEFT OUTER JOIN tbl_Complexity ON tbl_Queue.int_ComplexID = tbl_Complexity.int_ComplexID WHERE (dbo.tbl_Queue.dat_ReceivedRequest > CONVERT(DATETIME, @DateCreated, 102)) ORDER BY TicketType, tbl_Queue.str_Severity I also have another table called tbl_Notes. This table contains an unlimited quantity of records for every “num_TicketNumber�. This table contains the following fields: num_TicketNumber, str_TechRep, str_Notes, dat_TimeStamp, int_PercentComplete and int_MinutesWorked I need to add two more fields to the query, but I do not know how to tell the SQL statement how to retrieve the data for the specfic record and inbed the fields into the Dataset being returned from the database to the web page.1) The first field I need is the most current recorded Date field from the tbl_Notes table for the each of the records returned in the above SQL statement. Similar to: SELECT TOP (1) dat_TimeStamp FROM tbl_Notes WHERE(num_TicketNumber = xxxx) ORDER BY dat_TimeStamp DESC2) The other field I need to return with the Dataset is the sum of the int_MinutesWorked for each of the tickets being retrieved. Similar to: SELECT SUM(int_MinutesWorked) AS TotalMinutesFROM tbl_Notes WHERE (num_TicketNumber = 49)
I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so:
Anna Smith John Doe
etc. I did not see IDs listed for any of the names in the table when I looked.
There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually.
Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them:
strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC"
This gives me the total list but I only want to bring back 6 of the 20 for the pulldown.
Is there a way to modify this statement to pull only the records that I want?
