We do a lot of inserts into a table that has a primary key on an identity
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>
Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts
Friday, March 9, 2012
Inserts into table that has a Primary key/Unique constraint
We do a lot of inserts into a table that has a primary key on an identity
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>
Inserts into table that has a Primary key/Unique constraint
We do a lot of inserts into a table that has a primary key on an identity
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?
You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?
You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>
Wednesday, March 7, 2012
Inserting with DTS in IDENTITY table
Hi
I need to insert values from a text-file to a table with a primary key as identity. In the text file I have no idea of the primary key values and i get "foreign key constraint violation" when trying to import null values into the column.
How can I solve the problem? With ordinary insert-statement there is no problem since the table generates identity- key values automatically. Is there a possibility to generate identity values with DTS-import?
Bjrnyes.
you just need to not map anything to the primary key column. In the wizard, you need to hit the transform button and make and under the column mappings make sure ignore is selected. If you are using the DTS desginer, you need to get rid of the mapping on your transform data task under the transformations task.
I need to insert values from a text-file to a table with a primary key as identity. In the text file I have no idea of the primary key values and i get "foreign key constraint violation" when trying to import null values into the column.
How can I solve the problem? With ordinary insert-statement there is no problem since the table generates identity- key values automatically. Is there a possibility to generate identity values with DTS-import?
Bjrnyes.
you just need to not map anything to the primary key column. In the wizard, you need to hit the transform button and make and under the column mappings make sure ignore is selected. If you are using the DTS desginer, you need to get rid of the mapping on your transform data task under the transformations task.
Inserting Values Into Primary Key and Foreign Key Tables
I have two tables that I would like to insert values into at the same time
and would like help with the SQL statement to do so. One table (Member_Info)
has a PK and the other (Image_Info) a FK. The relationship the two tables
share is through the (E_Mail) column. Example structure:
Member_Info table columns:
First_Name
Last_Name
Birthday
E_Mail (PK)
Image_Info table columns:
E_Mail (FK)
Use
Name
Please Help! Thanks!Use INSERT TRIGGER in Member_Info table
"Willie Davis via webservertalk.com" wrote:
> I have two tables that I would like to insert values into at the same time
> and would like help with the SQL statement to do so. One table (Member_Inf
o)
> has a PK and the other (Image_Info) a FK. The relationship the two tables
> share is through the (E_Mail) column. Example structure:
> Member_Info table columns:
> First_Name
> Last_Name
> Birthday
> E_Mail (PK)
> Image_Info table columns:
> E_Mail (FK)
> Use
> Name
>
> Please Help! Thanks!
>|||Due to not determining what table will be filled by your code and what table
has to be filled "automagicaly", the following just depends on my guesswork.
CREATE TRIGGER TrgIns Member_Info
FOR INSERT
AS
BEGIN
INSERT INTO (EMail, Name)
SELECT EMail, COALESCE(FirstName,'') + ' ' + COALESCE(LastName,'') FROM
Inserted
END
I dont know wheter you Name column will store the First and the Lastname or
just one of it, but you should consider an Update Trigger to update this
information if its changed in the Primary Key Table. (Guess you sure wont
need it, if this just stores the name of the Member, even this is already
stored in the PK Table)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Willie Davis via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im
Newsbeitrag news:4183b2e7baef434899249fa71b414bf6@.SQ
webservertalk.com...
>I have two tables that I would like to insert values into at the same time
> and would like help with the SQL statement to do so. One table
> (Member_Info)
> has a PK and the other (Image_Info) a FK. The relationship the two tables
> share is through the (E_Mail) column. Example structure:
> Member_Info table columns:
> First_Name
> Last_Name
> Birthday
> E_Mail (PK)
> Image_Info table columns:
> E_Mail (FK)
> Use
> Name
>
> Please Help! Thanks!|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
same time M<<
SQL works with one table at a time. Put you code into a stored
procedure, do two inserts and depend on DRI actions to maintain
integrity.
and would like help with the SQL statement to do so. One table (Member_Info)
has a PK and the other (Image_Info) a FK. The relationship the two tables
share is through the (E_Mail) column. Example structure:
Member_Info table columns:
First_Name
Last_Name
Birthday
E_Mail (PK)
Image_Info table columns:
E_Mail (FK)
Use
Name
Please Help! Thanks!Use INSERT TRIGGER in Member_Info table
"Willie Davis via webservertalk.com" wrote:
> I have two tables that I would like to insert values into at the same time
> and would like help with the SQL statement to do so. One table (Member_Inf
o)
> has a PK and the other (Image_Info) a FK. The relationship the two tables
> share is through the (E_Mail) column. Example structure:
> Member_Info table columns:
> First_Name
> Last_Name
> Birthday
> E_Mail (PK)
> Image_Info table columns:
> E_Mail (FK)
> Use
> Name
>
> Please Help! Thanks!
>|||Due to not determining what table will be filled by your code and what table
has to be filled "automagicaly", the following just depends on my guesswork.
CREATE TRIGGER TrgIns Member_Info
FOR INSERT
AS
BEGIN
INSERT INTO (EMail, Name)
SELECT EMail, COALESCE(FirstName,'') + ' ' + COALESCE(LastName,'') FROM
Inserted
END
I dont know wheter you Name column will store the First and the Lastname or
just one of it, but you should consider an Update Trigger to update this
information if its changed in the Primary Key Table. (Guess you sure wont
need it, if this just stores the name of the Member, even this is already
stored in the PK Table)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Willie Davis via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im
Newsbeitrag news:4183b2e7baef434899249fa71b414bf6@.SQ
webservertalk.com...
>I have two tables that I would like to insert values into at the same time
> and would like help with the SQL statement to do so. One table
> (Member_Info)
> has a PK and the other (Image_Info) a FK. The relationship the two tables
> share is through the (E_Mail) column. Example structure:
> Member_Info table columns:
> First_Name
> Last_Name
> Birthday
> E_Mail (PK)
> Image_Info table columns:
> E_Mail (FK)
> Use
> Name
>
> Please Help! Thanks!|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
same time M<<
SQL works with one table at a time. Put you code into a stored
procedure, do two inserts and depend on DRI actions to maintain
integrity.
Inserting values into multiple tables
Hi. I have a primary key field in one table that is a foreign key field in another table. How do I write an INSERT statement that puts the value in both tables? Can this even be done?
On Thu, 20 May 2004 20:56:02 -0700, twright wrote:
>Hi. I have a primary key field in one table that is a foreign key field in another table. How do I write an INSERT statement that puts the value in both tables? Can this even be done?
Hi twright,
Not in one statement. You have to use two INSERT statements. If you want
to be sure that either both or none are executed, whatevert happens,
enclose them in a transaction:
BEGIN TRANSACTION
INSERT FirstTable (KeyColumn)
VALUES (17)
INSERT SecondTable (OtherKeyColumn, ForeignKeyColumn)
VALUES (231, 17)
COMMIT TRANSACTION
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
On Thu, 20 May 2004 20:56:02 -0700, twright wrote:
>Hi. I have a primary key field in one table that is a foreign key field in another table. How do I write an INSERT statement that puts the value in both tables? Can this even be done?
Hi twright,
Not in one statement. You have to use two INSERT statements. If you want
to be sure that either both or none are executed, whatevert happens,
enclose them in a transaction:
BEGIN TRANSACTION
INSERT FirstTable (KeyColumn)
VALUES (17)
INSERT SecondTable (OtherKeyColumn, ForeignKeyColumn)
VALUES (231, 17)
COMMIT TRANSACTION
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, February 24, 2012
Inserting Records, Skipping Duplicates
I'd like to ask if there's any statement to insert records into a table, suc
h
that if any record violates the primary key constraint, it will "neglect" th
e
record and insert the next one.
Thank youAn exception/error will be generated if you try to insert a row that
violates the primary key constraint. You can pre-empt the primary key
constraint violation by checking each row on insert via trigger.
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:CC9F8302-1C4D-4141-B9B1-EADC999CC01B@.microsoft.com...
> I'd like to ask if there's any statement to insert records into a table,
> such
> that if any record violates the primary key constraint, it will "neglect"
> the
> record and insert the next one.
> Thank you|||Hi,
Maybe if you perform your inserts one row at a time within a loop you could
handle the errors with @.@.ERROR.
Ray
"wrytat" wrote:
> I'd like to ask if there's any statement to insert records into a table, s
uch
> that if any record violates the primary key constraint, it will "neglect"
the
> record and insert the next one.
> Thank you|||Add a WHERE NOT EXISTS() to the INSERT.
INSERT Whatever
VALUES('a', 'b', 'c')
WHERE NOT EXISTS
(select * from Whatever as X
where X.pk = 'a')
or
INSERT Whatever
SELECT A, B, C
FROM Somewhere
WHERE NOT EXISTS
(select * from Whatever as X
where X.pk = Somewhere.A)
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 19:30:02 -0700, wrytat
<wrytat@.discussions.microsoft.com> wrote:
>I'd like to ask if there's any statement to insert records into a table, su
ch
>that if any record violates the primary key constraint, it will "neglect" t
he
>record and insert the next one.
>Thank you
h
that if any record violates the primary key constraint, it will "neglect" th
e
record and insert the next one.
Thank youAn exception/error will be generated if you try to insert a row that
violates the primary key constraint. You can pre-empt the primary key
constraint violation by checking each row on insert via trigger.
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:CC9F8302-1C4D-4141-B9B1-EADC999CC01B@.microsoft.com...
> I'd like to ask if there's any statement to insert records into a table,
> such
> that if any record violates the primary key constraint, it will "neglect"
> the
> record and insert the next one.
> Thank you|||Hi,
Maybe if you perform your inserts one row at a time within a loop you could
handle the errors with @.@.ERROR.
Ray
"wrytat" wrote:
> I'd like to ask if there's any statement to insert records into a table, s
uch
> that if any record violates the primary key constraint, it will "neglect"
the
> record and insert the next one.
> Thank you|||Add a WHERE NOT EXISTS() to the INSERT.
INSERT Whatever
VALUES('a', 'b', 'c')
WHERE NOT EXISTS
(select * from Whatever as X
where X.pk = 'a')
or
INSERT Whatever
SELECT A, B, C
FROM Somewhere
WHERE NOT EXISTS
(select * from Whatever as X
where X.pk = Somewhere.A)
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 19:30:02 -0700, wrytat
<wrytat@.discussions.microsoft.com> wrote:
>I'd like to ask if there's any statement to insert records into a table, su
ch
>that if any record violates the primary key constraint, it will "neglect" t
he
>record and insert the next one.
>Thank you
Sunday, February 19, 2012
Inserting problem with sqlserver and vb.net
hi there i ve got a quick question regarding table and adding primary key and values
Basically i have a empty table called ReligionType and have two fields ReligionID and ReligionName. I would like to add a new data into this table (i set the indentity and identity seed in sql server but when i execute the value in sqladapter it give me error on
"An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll"
This is the code that i wrote in vb.net
con2.Open() //sql connection
Me.SqlDataAdapter2.Fill(DataSet1)
Me.SqlInsertCommand2.CommandText() = "Insert into ReligionType(ReligionName) Values (@.ReligionName) SELECT @.@.identity As 'ReligionID' GO"
Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.ReligionName", System.Data.SqlDbType.NVarChar, 20)).Value = inputdata.Text
Me.SqlInsertCommand2.ExecuteNonQuery()
'Me.SqlDataAdapter2.Fill(DataSet1)
con2.Close()
so where did i go wrong ?
please helpHi,
i suppose the SQL should look like this.
"Insert into ReligionType(ReligionName) Values (@.ReligionName); SELECT @.@.identity As 'ReligionID' GO"
Hope this helps.
Regards...|||thanks but it still doesnt work|||The GO command would cause a problem...
The GO command isn't a T-SQL command (although it works in Query Analyzer). Take it out and give it a try.
I haven't looked too hard at the rest of the code...|||Still when i have remove the word go it still doesnt work
ps... i forgot to mention that the data in the table is currently empty
and also have type try and catch statement and this is the result of message
"Object reference not set to an instance of an object."
so what did i do wrong ?
Basically i have a empty table called ReligionType and have two fields ReligionID and ReligionName. I would like to add a new data into this table (i set the indentity and identity seed in sql server but when i execute the value in sqladapter it give me error on
"An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll"
This is the code that i wrote in vb.net
con2.Open() //sql connection
Me.SqlDataAdapter2.Fill(DataSet1)
Me.SqlInsertCommand2.CommandText() = "Insert into ReligionType(ReligionName) Values (@.ReligionName) SELECT @.@.identity As 'ReligionID' GO"
Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.ReligionName", System.Data.SqlDbType.NVarChar, 20)).Value = inputdata.Text
Me.SqlInsertCommand2.ExecuteNonQuery()
'Me.SqlDataAdapter2.Fill(DataSet1)
con2.Close()
so where did i go wrong ?
please helpHi,
i suppose the SQL should look like this.
"Insert into ReligionType(ReligionName) Values (@.ReligionName); SELECT @.@.identity As 'ReligionID' GO"
Hope this helps.
Regards...|||thanks but it still doesnt work|||The GO command would cause a problem...
The GO command isn't a T-SQL command (although it works in Query Analyzer). Take it out and give it a try.
I haven't looked too hard at the rest of the code...|||Still when i have remove the word go it still doesnt work
ps... i forgot to mention that the data in the table is currently empty
and also have type try and catch statement and this is the result of message
"Object reference not set to an instance of an object."
so what did i do wrong ?
inserting nulls into a primary key
The Question is ...Is there a way or work around to inserting a blank values into a primary field PLZ HELP.A blank is ok but null is NOT allowed. See BOL under the subject of 'Creating and Modifying PRIMARY KEY Constraints'|||One would think not. Primary keys are unique by definition. If you need to be inserting blank values into a primary key that field/column probably isn't your primary key.|||thank ..but could you show me in code how to do this.|||Hey, but he would only be able to put in one...
Where did I put that thread...|||the thing is that my warehouse requires this as this is a modification i only need to know how to insert that blank spot in that primary key any help would be very much aprreciated|||Here it is...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31801
Can you post the DDL of your table?
And you can insert a blank in to a pk...but only once
If it's a composite (col, col2) Then only once for the unique occurance of col1, and so on...
Maybe if you explain why you need to do this...|||Primary keys aren't even ALLOWED to accept nulls. The closest you could come would be using a zero-lengths string on a character-based primary key, but again you would only be able to insert one record.
You can't do what you are trying to do, but I suspect that the problem lies in your database design than in SQL Server's limitations.
Where did I put that thread...|||the thing is that my warehouse requires this as this is a modification i only need to know how to insert that blank spot in that primary key any help would be very much aprreciated|||Here it is...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31801
Can you post the DDL of your table?
And you can insert a blank in to a pk...but only once
If it's a composite (col, col2) Then only once for the unique occurance of col1, and so on...
Maybe if you explain why you need to do this...|||Primary keys aren't even ALLOWED to accept nulls. The closest you could come would be using a zero-lengths string on a character-based primary key, but again you would only be able to insert one record.
You can't do what you are trying to do, but I suspect that the problem lies in your database design than in SQL Server's limitations.
Subscribe to:
Posts (Atom)