I have a question about adding a unique key column to an existing table.
what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?
A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
In the table, there is a record which has several field. every month, the function will create a same record. that means, the first month, one record. the secord month, two reocrds, ..... for a years. will have same 12 record. so what function can do this? Thanks.
Hello everybody!I'm using the 'Data Sources (ODBC)' program that comes with windows tocreate the odbc connections I need. Although that is quite fast and easy Iwould like to have it more automated, since I'm using the same parametersall the time.Can anybody tell me (or give pointers to) how that is done?regards--Johnny Ljunggren
hi, i have 2 tables where i require an unique ID over both. I googled a bit and discovered that i need to create an own table for this which holds the last value (because mssql unfortunately does not support sequences).
i did this but my problem now is that i want to automatically set the id of my tables with a trigger on INSERT.
hope this is possible .. or anyone have other suggestions? thanks a lot!
I have two practice tables I have created and want to export the values of one into the source table. I want to know if I can export into a table and have the destination table automatically give a primary key value to a record? I haven't been able to figure this out even after fiddling with the "Enable identity insert" checkbox under the Column Mappings tab. I have created source tables with and without primary keys and neither works because of the fact that I need to have a value for a primary key in order to INSERT into the destination.
Do I have to copy the source records into a staging table and assign the PK values myself by hand? This can't be the answer.
How to create insert statements of the data from a table for top 'n' rows. I know we can create using generate scripts wizard, but we can't customize the number of rows. In my scenario i got a database with 10 tables where every table got millions of records, but the requirement is to sample out only top 10000 records from each table.
I am planning to generate table CREATE statements from GENERATE Scripts wizard and add this INSERT STATEMENT at the bottom.
EX : INSERT [dbo].[table] ([SERIALID], [BATCHID] VALUES (126751, '9100278GC4PM1', )
I have an existing MS SQL database (2008 R2). I have a very simple SQL script. I need to automate this script means wants to create a job which runs on a Friday basis and save the output results of the query as a excel file and then automatically sends the mail to everyone.
I am new to MS SQL and I was wondering is it possible to create a table with unique rows?? By this I mean if a table has two columns then a duplicate row would be if BOTH columns matched two columns of another row.
Hi all,I might be getting this all wrong but bear with me. I need to create some kind of Unique field in my DB that is nonsequential. This is because I need it to be difficult to guess ids if you have an example in front of you.I have looked at 8digit EAN codes which include a check digit system.( I use a base digit of the row_id for these) Can anyone tell me how many uniques I can get out of this system?For my ID: I have looked at something along the lines of:
Hex(row_id) + "T" + Hex( Trimmed(EAN) ) The "T" serves to split the numbers for when I am converting back. So for example:row_id EAN_code Hex(row_id) + "T" + Hex( Trimmed(EAN) ) ------------------------------------------------------------------------------------------ 3166 00031663 C5ET7BAF 3167 00031673 C5FT7BB9 3168 00031686 C60T7BC6
Is this too easy to guess (once you can tell there are two hex numbers there?) What do people think? Thanks,Pete
I am attempting to create a unique constraint on an nvarchar field named theology (it is not the primary key field) that allows nulls. The table contains multiple rows with the value of null for field theology. The documentation says one can create a unique constraint on a field with all unique value except for null. Here is the error message:
'testtable1' table - Unable to create index 'IX_testtable1'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found. Most significant primary key is ''. [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
Any ideas? I am creating a unique constraint and not a unique index. Is there some other database option to set to allow this?
I have a table where the key is an autonumber. I also have a field which holds the reference of a room eg 0BM1. It is nvarchar. Is there a way I can set this field to duplicates = No, so that my user cannot enter the same room reference more than once. Or do I have to do this check in my asp.net code ?
I have created the companyid as Primary Key.How to create a unique secondary index on Company Name. To avoid inserting duplicate records in database with the same companyname. I m creatin database in sql server 2005 with asp.net C# 2005. I know one way is write the query if not exists at the time of insert.But,i want to know is there anyother way to make a unique secondary index for the companyname on the company tablethanxs
Hi there,I am trying to create a UID that is unique within my SQL Server. There are many users accessing the Server in seperate databases, but then I want to combine all the data from these tables, keeping the ID from each one as a primary key. I have written the following function, but when i call it as a default value for a field, it does not produce a unique number. CREATE FUNCTION GETNEXTID(@CURDATE DATETIME)RETURNS BIGINTASBEGINRETURN (SELECT CAST(CAST(DATEPART(YY,@CURDATE) AS VARCHAR) +RIGHT('0' + CAST(DATEPART(M,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(D,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(HH,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(SS,@CURDATE) AS VARCHAR),2) +RIGHT('00' + CAST(DATEPART(MS,@CURDATE) AS VARCHAR),3) AS BIGINT))END Can anyone help?
Hello. Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.
I have: CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)
why it is not possible to create a Foreign key to a Unique constraint?
Table A has column 1 holding a Primay key and two columns (2 and 3) holding a Unique combination (and some more columns).He created an Unique constraint on column 2 and 3 together.
He wanted to use this Unique combination to point to table B (instead of the table 1's PK) so he tried to create a foreign key on a column in table B but an error popped up prompting;
The columns in table 'TABLE_A' do not match an existing primary key or UNIQUE constraint.
Ok - these two columns ar no PK but the hold an Unique constraint......
Introduction This MS SQL Store Procedure solves a problem which is not common except when you have a table in a database which each row needs to be uniquely identified and their are more rows in the table than you can identfy with a big int or Unique Identifier.
So for example,
if you used a "unique identifier" you would be limited to 8.6904152163272468261061026005374e+50 unique rows at best. If you used a "Big Int" you would be limited to -2^63 ( -9223372036854775808) through 2^63-1 (9223372036854775807).
This method will allow you to have 2.2528399544939174411840147874773e+106. (With cluster indexing the identity field.) or, 4.722366482869645213696e+129 (Without indexing the identity field)
Why would you need that many unique values? Well, the reason for this invention is due to the need to track every email that an application sends for Sarbanes/Oxley Requirements. With this technique, every email sent out will be uniquely identified for a very very very long time.
The purpose of this is to show how to set up an identity column with a larger range than a big int or unique id. Try transaction logs where you need to track every change, or determining click paths through a website, etc.
The point is, that this method pretty much does what the title says, "Create unlimited Unique ID's". What table you apply this too and for what reason is up the the programmer.
Background This design uses basic counting methods and handles the limitations of MS SQL TSQL. First, you can use a varchar(4000) as the unique id column but the issue with this is that as of MSSQL 2000, the largest indexable field is 900 character. So if you need to be able to quickly search the table by key, or clustered keys, you need to limit your key column with to 900 characters, otherwise if you use a varchar(4000) make sure when searching the table you create a temporary table, select the subset into it and search that.
Using the code First, copy and paste all the TSQL into a Query Window and compile it in the database you wish to use it in.
[Code] /********************************************************************************** Program: ALTER Unlimited Unique ID's (Auto Increment) Programmer: Vince Gee Date: 9/28/2005 Parameters: @TABLE_NAME - The name of the table to establish the auto incrementing field in @COLUMN_NAME - The column name in the table to establish the auto incrementing field in @DROP_EXISTING_TRIGGER - Whether or not on running to drop an existing trigger with the same name. Theory: A varchar 900 field will be able to have 2.2528399544939174411840147874773e+106 unique identifiers in it.
A uniqueID only has 8.6904152163272468261061026005374e+50 unique identifiers in it.
Description: The purpose of the sql procedure is to automate the creation of auto updating identities on a sql table without the trouble of writing the trigger each time.
So what does this do? Well for example lets say we have the following table which you will have many many many rows in.
myKey is the unique identifier for each row. We can set it's size really to anything, This proc will look for the column specified and determine it's size. The column should be nvarchar of type
All the other columns don't matter, the only issue is if all the column names concated together exceed the storage compacity w/ the trigger code of 4000 characters. If this is the case your gonna have to write the trigger manually.
So to set the auto incrementing field up you would call this proc: Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey' or Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Output: When data is inserted into the table, the auto incrementing field will look like 0000000001 0000000002 0000000003 0000000004 0000000005 0000000006 0000000007 0000000008 0000000009 000000000A 000000000B 000000000C 000000000D 000000000E 000000000F 000000000G 000000000H 000000000I 000000000J 000000000K 000000000L with how many 0's set up etc. It goes 0-9, then A-Z
--If the trigger if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[' + 'IO_Trig_INS_' + @COLUMN_NAME + ']') and OBJECTPROPERTY(id, N'IsTrigger') = 1) begin IF @DROP_EXISTING_TRIGGER = 0 BEGIN -- USER DOESN'T WANT US TO AUTODROP THE TRIGGER, BY DEFAULT AUTODROP TRIGGER IS OFF PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' PRINT 'STOP ERROR :: PLEASE DROP THE EXISTING TRIGGER BEFORE RUNNING THIS PROC' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' RETURN END ELSE BEGIN --CREATE A SQL STRING TO DROP THE TRIGGER SO WE CAN RECREATE IT. set @EXECSQLSTRING = 'drop trigger IO_Trig_INS_' + @COLUMN_NAME --EXECUTE THE SQL EXEC SP_EXECUTESQL @EXECSQLSTRING END end
--CREATE A TABLE TO HOLD THE RESULTS FOR THE SP_COLUMNS create table #temp ( TABLE_QUALIFIER varchar(255), TABLE_OWNER varchar(255), TABLE_NAME varchar(255), COLUMN_NAME varchar(255), DATA_TYPE int, [TYPE_NAME] varchar(255), [PRECISION] int, LENGTH int, SCALE int, RADIX int, NULLABLE int, REMARKS varchar(255), COLUMN_DEF varchar(255), SQL_DATA_TYPE int, SQL_DATETIME_SUB varchar(255), CHAR_OCTET_LENGTH int, ORDINAL_POSITION int, IS_NULLABLE varchar(255), SS_DATA_TYPE int ) --POPULATE THE TEMP TABLE W/ A SP_COLUMNS ON THE TARGET TABLE insert into #temp exec sp_columns @TABLE_NAME
--CYCLE THROUGH ALL THE COLUMN NAMES AND BUILD OUR COLUMN NAME STRING --FOR INSERTS. THE LAST COLUMN NAME IS ALWAYS THE IDENTITY FIELD. SELECT @MAXORDINAL = MAX(ORDINAL_POSITION) FROM #TEMP SET @COUNTER = 1 SET @COLUMN_NAMES = '' WHILE @COUNTER <= @MAXORDINAL BEGIN select @tCOLUMN_NAME = COLUMN_NAME FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER if (@tCOLUMN_NAME <> @COLUMN_NAME) begin SET @COLUMN_NAMES = @COLUMN_NAMES + @tCOLUMN_NAME+ ',' end else begin select @KEYLENGTH = LENGTH FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER end SET @COUNTER = @COUNTER +1 END --CLEAN UP drop table #temp
IF @KEYLENGTH > 900 Begin PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!WARNING:: YOU WILL NOT BE ABLE TO INDEX THIS TABLE BY YOUR CHOSEN COLUMN,!!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!BECAUSE THE COLUMN IS OVER 900 CHARACTERS. 900 CHARS ARE THE MAX THAT !!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!THAT CAN BE INDEXED !!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' END SET @EXECSQLSTRING = ' CREATE TRIGGER IO_Trig_INS_' + @COLUMN_NAME + ' ON ' + @TABLE_NAME + '
END' if len(@EXECSQLSTRING) <4000 begin EXEC SP_EXECUTESQL @EXECSQLSTRING end else begin print 'STOP ERROR:: BUFFER OVERFLOW. THE GENERATED TRIGGER TEXT > 4000, Trigger must be hand written.' end
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
First, to test the functionality create a temp table.
First, to test the functionality create a temp table.
Second, call the proc on the table. Parameters are:
Table Name - Name of the table to put the trigger on. Column Name - Name of the column to use as the key. Drop Existing Trigger - If this is set to 1 and a trigger with the name generated by this stored procedure exists it will drop it. (OPTIONAL)
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey'
or Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Now, we are going to test how this works. Copy and paste the following code into a query analyzer.
declare @t int set @t = 0
while @t <= 40000 begin insert into countertest select '','s','s','s' set @t = @t + 1 end GO
Once this completes, you can inspect the unique id by selecting it from the table SELECT RIGHT (MYKEY,10) FROM countertest
The table will keep incrementing the key column first 0-9 then a-z. When it reaches all 'z' it will roll over, but if your key column is wide enough this shouldn't happen while your still employeed.
Also, the stored procedure figures out how wide the key column is automatically and adjusts the script accordingly. So if you widen or shrink the key column, just rerun the proc to update the trigger script.
Hi, can anyone guide me on how to create a unique field with SqlExpress and VC#.net?
I know how to create primary key with identity, however I need to create another unique column for "email". I have search high and low for a solution but found none, the closest i got is unique constraint which I've not idea what's that.
Please help, else I have to always do a duplication check before inserting new records.
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example: Row 1 - Column 1 = "cool" Row 1 - Column 3 = 91 Row 3 - Column 1 = "cool" Row 3 - Column 3 = 91
I dont care about one column having duplicate values, I want to protect against Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?
I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view.
I am trying to add a unique index/constraint on a column that allows NULL values. The column does have NULL values and when I try to create a unique constraint, I get the following error.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 9. Most significant primary key is '<NULL>'.
Are'nt you allowed to create a UNIQUE constraint on a NULL column? Books Online says that you are allowed to create a unique constraint on NULL columns, then why am I getting this error.
Hi all,My program is a central data processing application built in ASP.We have different companies that use different web pages on another webapplication (from different countries) to load some inventory data(merchandise - clothes).Here is my requirement: Load different types of data (in differentformats) into a common set of tables, to do this I have to firstfilter, do lookup's, use cross-reference tables on this data and thenload it into a couple of tables.Since data is so different everytime, I want to have one main storedprocedure in which I can build the table (I know the format of thisdata so I know what columns/types to create) into which I will feed thedata.After this I will create other stored procs/udf's that reference thistable (probably from the same main stored proc) 'cleanse' the databefore loading into the actual tables.How feasible is my approach? (if you can call it one :-) I do not wantto have seperate tables for each country, that will be too many and sothis plan...If not any other ideas will be really helpful.thanks much
I'm a newby to SQL and looking for how-to-help. I have an existing DB and within a certain table, I have created a new Column via Studio manager, but need help with the following:
Need to make the new Col "Unique, and Indexed" but cannot see anywhere in studio manager interface to do it.
I have the following table (Table does not have unique key id )
Last Name First Name DATE Total-Chrg
Jaime KRiSH 5/1/2015 -4150.66 Jaime KRiSH 5/1/2015 1043.66 Jaime KRiSH 5/1/2015 1043.66 Jaime KRiSH 5/1/2015 4150.66 Jaime KRiSH 5/3/2015 4150.66 Peter Jason 5/1/2015 321.02 Peter Jason 5/1/2015 321.02 Peter Jason 5/23/2015 123.02
I want the results to be in following way
Uniq ID Last Name First Name DATE Total-Chrg
1 Jaime KRiSH 5/1/2015 -4150.66 2 Jaime KRiSH 5/1/2015 1043.66 2 Jaime KRiSH 5/1/2015 1043.66 3 Jaime KRiSH 5/1/2015 4150.66 4 Jaime KRiSH 5/3/2015 4150.66 5 Peter Jason 5/1/2015 321.02 6 Peter Jason 5/1/2015 321.02 7 Peter Jason 5/23/2015 123.02
May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values. There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates).
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
ReportModel is created ,there is only a named query in DSV ,it has a few tables in it(The relationship are inner joins and outer joins).
The question is how could I create a unique logical primary key to identify each unique row in the named query dataset, and also you cannt generate a model unless the named query has a logical primary key . how can I solve this problem,any help?
I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables. This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one!
I can find those rows by
SELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0 WHERE EXISTS ( SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1 WHERE table0.ParentREF = table1.ParentREF AND table0.UniqueName = table1.UniqueName AND table0.OID != table1.OID ) ORDER BY ParentREF, UniqueName, ModifiedUTC desc
...but I struggle to make the required SQL (SP?) to update the "invalid" rows. Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged!
ATM the preferred (cause easiest) way is to rename the invalid rows with UniqueName = OID because if I use any other name I risk to create another double entry.
