Add Primary Key
-
Posted on July 25, 2009 by Derek Dieter
-
-1
In order to add a primary key to an existing table we need to use the Alter Table command.
Add a primary key for a new column
[cc lang=”sql”]
ALTER TABLE dbo.Presidents
ADD PresidentID int NOT NULL IDENTITY(1,1) CONSTRAINT PK_PresidentID PRIMARY KEY NONCLUSTERED
[/cc]
Note however that the ordering of the identity field will not be predictable for the records that already exist in the table. For newly inserted records, the identity field will increment properly.
Add a primary key for an existing column
[cc lang=”sql”]
ALTER TABLE dbo.Presidents WITH NOCHECK
ADD CONSTRAINT PK_PresidentNumber PRIMARY KEY NONCLUSTERED (PresidentNumber)
[/cc]
- Comments (RSS)
- Trackback
- Permalink
Hi, can you tell me about add a primary key for an existing table because I don’t understand where as table name and field name where.
help me, please! thank’s for your answer.
Hi, can you tell me about your post because I don’t understand where as table name and field name where. thank’s for your answer.
help me, please!
how to create 2 primarykey for one table , i am try this coding but doesn’t working plz help & give suggestion for me.
ALTER TABLE tblAppForm ADD CONSTRAINT pk_Constraints PRIMARY KEY(CanidateID,Category)
you cannot have two primary keys for one table. if u want to add two primary keys, the most appropriate attribute should be the primary key while the other should be the alternate key.
hope this is helpful. I am 100% sure with this theory but not sure whether there is another theory when choosing primary keys for tables in sql.
Hi Praveen
Please tell me query to insert this string… ’13F6789”28/12/2010 04:15:02 PM’1202556,’I011′,RSE58043,1,’28/12/2010 04:15:02 PM’,Secure_3Ph4W_XXXX,Secure,725559555,6.44,6.45,6.45,0.001,0.00,0.00,0.00,0,0.000,0.000,0.001,1.000,1.000,1.000,1.000,0.0,0.0,0.1,49.69,15,0,15,0,7,0,19,0,98390,1057535,0,
Hi Sachan,
I Am able to insert the string you have given.See except single quoute no character will cause problem inserting a string
I will suggest you one technique which may help you to over come this single quote problem.
1.First you add some other special charecters(eg : *,# etc ) in the the place of single qoute,except at the beginning and end.
2.Then execute the below query
Insert into TableName Values(‘ Hi,I*AM FINE ,TIME IS NOW 8:30,”PRAVEEN”,IT*S MINE….!@#^#$%^&() etc… ‘)
Update TableName
Set ColumnName=Replace(ColumnName,’*’,””)
Please note that Your column’s actual value sholud not contain
the special character.Or else use a string and replace it later.In the Above case i am using * Which my actual string not having.
Eg:
Insert into TableName Values(‘ Hi,IsinglequoteAM FINE ,TIME IS NOW 8:30,”PRAVEEN”,ITsinglequoteS MINE….!@#^#$%^&() etc… ‘)
Update TableName
Set ColumnName=Replace(ColumnName,’singlequote’,””)
Hope this problem is ends here.
Best Regards
P.Praveen Yadav
Thank u very much P.P.Y. And Derek
One another issue is how can i insert this string in a table by hard code
–> DSAD””FDF’SDF'”SD’AS””FSAS’ MAX DDFDFDFDSDFSDF <–
Hi Sachan,
I think the below query will solve your probs
DECLARE @STR VARCHAR(100)
SET @STR=’DSAD””FDF”SDF””SD”AS””FSAS” MAX DDFDFDFDSDF’
Now the @str contains the request string. u can use this variable
in insert like below:
INSERT INTO TABLENAME VALUES(@STR,COL2,COL3,ETC…..)
In General you have to use ” for ‘ in SQL
eg: SELECT ””
Which will give you ‘ as output
Best Regards
Praveen
Hi Praveen
I think some thing mismatch to understand. its creating prob at “Set”..
Actualy this string have none specified single quotes or double quotes, they may be even or odd..example like 1 or 4 or 7 or 8 single quotes and like single quote its may be double quotes. String also may have function like max,min,as,…—->DSAD””FDF min ’SDF’”SD’AS””FSAS’ MAX DDFDFDFDSDFSDF
Hi Sachan,
I am not clear abt ur requirement. I think the prob must be with quotes
To insert a string directly into a varchar field,first you should
open two single quotes, like ‘ ‘,then you can add any any any values inside that quotes including double quotes.But if you want to add a single quote you must have to add two single quotes.
Hope that it will help you if not,plz describe your prob , i will try my best to solve it.
Best Regrds
Praveen Yadav
Hi Praveen Yadav
you are right.. we can do it by replacing ‘Odd’ Single Quotes into ‘Even’ Single Quotes and placing Double quotes at the beginning and at end of String…Thank you Very Much
Thanks & Regards
Piyush Kr. Sachan
Hi Derek
One question is that….we are fire “select” command it picks data according to given condition
and shows us… can i direct save this selected data into any files likes csv,excel by TSQL programming ??
Thank You Very Much
Hi P.K.S ,
Yeah we can do..
Before executing the query just press Ctrl+Shift+F.
Then execute the query,it will open a save dialog box,there you can select the supported file format and path where u want to save,then the result will be saved to the file once the query was completed.
Regards
P.Praveen Yadav
Hi Derek
One question is that….we are fire “select” command it picks data according to given condition
and shows us… can i direct save this selected data into any files likes csv,excel by TSQL programming ??
Thank You Very Much
Hi Derek
A very very much thank you………….
Hi Derek
i want to know difference between these ” count()” ? , is there any execution difference ?
1. count(*)
2. count(2)
Thank you very much
Hi P.K.
There is no difference in SQL 2005 +. From what I remember however, there was a difference in previous versions. (Not too sure if it was SQL 2000 or lower). The idea is that with an ordinal specified, the metadata does not have to be referenced. Now the optimizer is smart enough to know that it does not need to reference the ordinal unless DISTINCT is specified.
Derek
Hi Derek,
Thankyou very much..
please see the code and help me to the questions given below..
create TRIGGER [dbo].[T]
ON [dbo].[Piyush]
FOR DELETE,UPDATE
AS
if Exists(Select * From inserted)
begin
PRINT ‘U’
end
else
begin
PRINT ‘D’
end
1. If i ‘update’ some value in Table ‘Piyush’ than it print ‘U’…Why ? even i have not inserted any new record.
Hi P.K.
This happens because the intrinsic table “INSERTED” which is referenced in a trigger, contains all records that were changed. The only problem here is that the intrinsic name of “INSERTED” is probably not the best choice. It should be something like, “changeRecords”. So INSERTED contains both ‘INSERTED’ AND ‘UPDATED’ records.
Derek
Hi Derek,
Thanks a lot..
please see the code and help me to the questions given below..
create TRIGGER [dbo].[T]
ON [dbo].[Piyush]
FOR DELETE,UPDATE
AS
if Exists(Select * From inserted)
begin
PRINT ‘U’
end
else
begin
PRINT ‘D’
end
1. If i ‘update’ some value in Table ‘Piyush’ than it print ‘U’…Why ? even i have not inserted any new record.
Hi Derek
Please solve my problem…
1. if i insert 1000 rows by (union all or by other method ‘at a time’) in ‘new’ empty table than it take time like 9 or 10 second and if i insert same 1000 rows than its take time less than 1 second….Why..?
2.after one process if i change some value in column like 10 to 20 or piii to piyy than i insert 1000 rows its take again 9 or 10 sec….Why..?
3. how i insert 1000 rows in 1 second in ‘new’ table OR how i insert updated 1000 rows in 1 second in table..???
Thanks & Regards
P.K. Sachan
Hi P.K.,
It’s a little difficult to duplicate this without the code or the environment, but I’ll try my best:
1. Does your table contain any indexes? How big are the rows you are inserting? If you are doing a union to insert data, then it is traveling from your client machine to the server. However if you insert 1000 rows directly from one table in the server to another, then the entire action occurs on the server. It does not go from your desktop to the server. This slowness could be because of network latency, or large rows.
2. Is this the 1000 rows you are inserting from your client machine to the server?
3. The best way is to use SELECT.. INTO. This is the fastest way to insert anything in a new table. If you need to insert 1000 rows from your client machine to the server, and it is slow, then I would suggest trying BCP, OPENQUERY, or SSIS. This will at least help you troubleshoot what the issue is.
Try turning on Client – Statistics in SSMS to see what the latency is: Query -> Include Client Statistics
Compare these statistics to the server-side statistics by using: SET STATISTICS TIME ON -> in the window where you are doing the update. Are the client stats slower? If so, then you have network latency.
Last, look at the execution plan to see where the slowness is occuring: Query -> Include actual execution plan
Good Luck,
Derek
Hi Derek,
Thanks a lot to response me and to clear all my doubts..
please see the code and help me to the questions given below..
create TRIGGER [dbo].[T]
ON [dbo].[Piyush]
FOR DELETE,UPDATE
AS
if Exists(Select * From inserted)
begin
PRINT ‘U’
end
else
begin
PRINT ‘D’
end
1. If i ‘update’ some value in Table ‘Piyush’ than it print ‘U’…Why ? even i have not inserted any new record.
Hi Derek
i have one more question…please help me
1. if i insert 1000 rows by (union all or by other method ‘at a time’) in ‘new’ empty table than it take time like 9 or 10 second and if i insert same 1000 rows than its take time less than 1 second….Why..?
2.after one process if i change some value in column like 10 to 20 or piii to piyy than i insert 1000 rows its take again 9 or 10 sec….Why..?
3. how i insert 1000 rows in 1 second in ‘new’ table OR how i insert updated 1000 rows in 1 second in table..???
Thanks & Regards
P.K. Sachan
Hi again P.K.
Without seeing the code it’s difficult to understand what’s happening. Can you send it to derek @ sqlserverplanet. Com?
Thanks
1. in table “T” 4,000 rows coming in 10 sec by ‘job scheduler’
2. i hv put a “code” on AFTER INSERT Trigeer of table ‘T’
3. in “code” i take data from ‘T’ and insert to T1(if data new than insert if duplicate than update with reference primary key)
4.prblem is that…scheduler takes 10 sec than 30 sec than 30 than 1 min…but i want in 10.in whole process.?????
5.any other way for process 1 and 3…????
6. Thank you very much..
Hi P.K.
If I understand you correctly, you are trying to take the newly created identity field created by the insert and match it to an existing table. You may be able to use this instead of a trigger. You do need SQL 2008 though.
Good luck,
Derek
