Hi,
I have a field in my sql table called ORDER_NO which is also the primary
key. Now, I want to add amother field called AO_Number whose valued increment
whenever a record is added . Those values should be AO-1, AO-2, AO-3...& so
on...
How to have these values for this field coz making it an identity column
makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
Is this possible? & how?
--
pmudYou could have an identity column AND a calculated column that combined the
identity with the value you wanted.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> Hi,
> I have a field in my sql table called ORDER_NO which is also the primary
> key. Now, I want to add amother field called AO_Number whose valued
increment
> whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
so
> on...
> How to have these values for this field coz making it an identity column
> makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> Is this possible? & how?
> --
> pmud|||Hi Mike,
Where do I have to write the sql code for creating a calculated column? In
user defined functions?
Thanks
"Mike Jansen" wrote:
> You could have an identity column AND a calculated column that combined the
> identity with the value you wanted.
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > Hi,
> >
> > I have a field in my sql table called ORDER_NO which is also the primary
> > key. Now, I want to add amother field called AO_Number whose valued
> increment
> > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> so
> > on...
> >
> > How to have these values for this field coz making it an identity column
> > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> >
> > Is this possible? & how?
> > --
> > pmud
>
>|||Here is how to do it. You can also create a view.
alter your_table
add ao_number int not null identity(1, 1)
go
alter your_table
all ao_formatted_number as 'AO-' + ltrim(ao_number)
go
selet * from your_table
go
AMB
"pmud" wrote:
> Hi Mike,
> Where do I have to write the sql code for creating a calculated column? In
> user defined functions?
> Thanks
> "Mike Jansen" wrote:
> > You could have an identity column AND a calculated column that combined the
> > identity with the value you wanted.
> >
> > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > > Hi,
> > >
> > > I have a field in my sql table called ORDER_NO which is also the primary
> > > key. Now, I want to add amother field called AO_Number whose valued
> > increment
> > > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> > so
> > > on...
> > >
> > > How to have these values for this field coz making it an identity column
> > > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> > >
> > > Is this possible? & how?
> > > --
> > > pmud
> >
> >
> >|||Here is an example:
CREATE TABLE tbl (
key_col INT NOT NULL PRIMARY KEY,
id_col INT NOT NULL IDENTITY,
calc_col AS 'AO-' + CAST( id_col AS VARCHAR ), -- calculated column
...)
If you are looking for a truly monotonic sequence, avoid identity. There are
certain instances where identity column can have gaps its values. If the
value is something that can be derived based on some collating sequence of
existing values in other columns, consider using a ranking mechanism like
the one detailed in KBA 186133. Another alternative, is to use a view which
can generate the sequential values based on existing columns without
exposing its complexity.
--
Anith|||Hi Aljandro,
That solved my problem.
Thanks
"Alejandro Mesa" wrote:
> Here is how to do it. You can also create a view.
> alter your_table
> add ao_number int not null identity(1, 1)
> go
> alter your_table
> all ao_formatted_number as 'AO-' + ltrim(ao_number)
> go
> selet * from your_table
> go
>
> AMB
> "pmud" wrote:
> > Hi Mike,
> >
> > Where do I have to write the sql code for creating a calculated column? In
> > user defined functions?
> >
> > Thanks
> >
> > "Mike Jansen" wrote:
> >
> > > You could have an identity column AND a calculated column that combined the
> > > identity with the value you wanted.
> > >
> > > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > > news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a field in my sql table called ORDER_NO which is also the primary
> > > > key. Now, I want to add amother field called AO_Number whose valued
> > > increment
> > > > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> > > so
> > > > on...
> > > >
> > > > How to have these values for this field coz making it an identity column
> > > > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> > > >
> > > > Is this possible? & how?
> > > > --
> > > > pmud
> > >
> > >
> > >
Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts
Friday, March 23, 2012
Making a Non Primary key a unique column
I have a table in which a column that is not part of the primary key must
contain data that can not be duplicated in the same column in another row
but it should not - for other design reasons - be the primary key.
I created an index on that field in the table and in the properties for that
index I checked Create Unique and checked Constraint. I thought that that
would prevent entering duplicate values in that field in that table.
However, when I tested this in the table in the data entry screen of
Enterprise manager, I was able to enter duplicate values in that field in
several rows in that table and the database did not return any error
messages.
What's wrong here, can anyone shed light on this behaviour?
How do I achieve the goal set out above?
The field giving me the problem is an nvarchar type max 50 length.
Thanks for any help,
RDIt is difficult for us to answer without knowing exactly what you did and ho
w we can reproduce it.
Can you post CREATE TABLE, CREATE INDEX or ALTER TABLE ADD UNIQUE CONSTRAINT
with some insert
statements we can run to reproduce the behavior?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RD" <nospam@.nospam.net> wrote in message news:%23hgdDzSaFHA.2996@.TK2MSFTNGP10.phx.gbl...[c
olor=darkred]
>I have a table in which a column that is not part of the primary key must
> contain data that can not be duplicated in the same column in another row
> but it should not - for other design reasons - be the primary key.
> I created an index on that field in the table and in the properties for th
at
> index I checked Create Unique and checked Constraint. I thought that that
> would prevent entering duplicate values in that field in that table.
> However, when I tested this in the table in the data entry screen of
> Enterprise manager, I was able to enter duplicate values in that field in
> several rows in that table and the database did not return any error
> messages.
> What's wrong here, can anyone shed light on this behaviour?
> How do I achieve the goal set out above?
> The field giving me the problem is an nvarchar type max 50 length.
> Thanks for any help,
> RD
>
>[/color]|||A UNIQUE constraint should ineed prevent duplicate values. I suspect the
constraint has not been created as you wanted it. In Query Analyzer you can
easily generate the script for the constraint so that you can verify it and
edit it as necessary (right-click on the constraint in the Object Browser,
then click Script Object to New Window As > Create).
One reason I prefer to use QA rather than EM for any structure changes is
that you have better control and visibility over what is happening. However,
you can do a similar thing in EM when you change something in the Table
Designer. You can click the Save Change Script button on the toolbar (3rd
one along) to show you the actual script that will make the changes. The
complete change script EM generates is harder to read however than the
equivalent in QA.
Most of us will be glad when the EM/QA duality disappears in SQL2005 to be
replaced by a single place for all management and development tasks.
David Portas
SQL Server MVP
--|||You might want to create the table in QA with DDL and use the UNIQUE
constraint. This will document your design better. I have no idea why
EM would not do this properly.|||Thanks to all for your explanations.
Indeed it works properly as explained by you and the docs.
This morning I just tried again and realized that the duplicate data I
thought I entered was not EXACTLY duplicate after all, hence my mistaken
belief that it didn't work.
As usual the problem is 18 inches from the screen.
Sorry to have disturbed you like that, comes with old age I guess. Can't
stay up late anymore and do anything worthwhile ;-)
RD.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117914232.752590.247840@.g49g2000cwa.googlegroups.com...
> You might want to create the table in QA with DDL and use the UNIQUE
> constraint. This will document your design better. I have no idea why
> EM would not do this properly.
>
contain data that can not be duplicated in the same column in another row
but it should not - for other design reasons - be the primary key.
I created an index on that field in the table and in the properties for that
index I checked Create Unique and checked Constraint. I thought that that
would prevent entering duplicate values in that field in that table.
However, when I tested this in the table in the data entry screen of
Enterprise manager, I was able to enter duplicate values in that field in
several rows in that table and the database did not return any error
messages.
What's wrong here, can anyone shed light on this behaviour?
How do I achieve the goal set out above?
The field giving me the problem is an nvarchar type max 50 length.
Thanks for any help,
RDIt is difficult for us to answer without knowing exactly what you did and ho
w we can reproduce it.
Can you post CREATE TABLE, CREATE INDEX or ALTER TABLE ADD UNIQUE CONSTRAINT
with some insert
statements we can run to reproduce the behavior?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RD" <nospam@.nospam.net> wrote in message news:%23hgdDzSaFHA.2996@.TK2MSFTNGP10.phx.gbl...[c
olor=darkred]
>I have a table in which a column that is not part of the primary key must
> contain data that can not be duplicated in the same column in another row
> but it should not - for other design reasons - be the primary key.
> I created an index on that field in the table and in the properties for th
at
> index I checked Create Unique and checked Constraint. I thought that that
> would prevent entering duplicate values in that field in that table.
> However, when I tested this in the table in the data entry screen of
> Enterprise manager, I was able to enter duplicate values in that field in
> several rows in that table and the database did not return any error
> messages.
> What's wrong here, can anyone shed light on this behaviour?
> How do I achieve the goal set out above?
> The field giving me the problem is an nvarchar type max 50 length.
> Thanks for any help,
> RD
>
>[/color]|||A UNIQUE constraint should ineed prevent duplicate values. I suspect the
constraint has not been created as you wanted it. In Query Analyzer you can
easily generate the script for the constraint so that you can verify it and
edit it as necessary (right-click on the constraint in the Object Browser,
then click Script Object to New Window As > Create).
One reason I prefer to use QA rather than EM for any structure changes is
that you have better control and visibility over what is happening. However,
you can do a similar thing in EM when you change something in the Table
Designer. You can click the Save Change Script button on the toolbar (3rd
one along) to show you the actual script that will make the changes. The
complete change script EM generates is harder to read however than the
equivalent in QA.
Most of us will be glad when the EM/QA duality disappears in SQL2005 to be
replaced by a single place for all management and development tasks.
David Portas
SQL Server MVP
--|||You might want to create the table in QA with DDL and use the UNIQUE
constraint. This will document your design better. I have no idea why
EM would not do this properly.|||Thanks to all for your explanations.
Indeed it works properly as explained by you and the docs.
This morning I just tried again and realized that the duplicate data I
thought I entered was not EXACTLY duplicate after all, hence my mistaken
belief that it didn't work.
As usual the problem is 18 inches from the screen.
Sorry to have disturbed you like that, comes with old age I guess. Can't
stay up late anymore and do anything worthwhile ;-)
RD.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117914232.752590.247840@.g49g2000cwa.googlegroups.com...
> You might want to create the table in QA with DDL and use the UNIQUE
> constraint. This will document your design better. I have no idea why
> EM would not do this properly.
>
Wednesday, March 21, 2012
Making a column's values unique
I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!
http://www.sql-server-performance.com/rd_delete_duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>
|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.com/rd_delete_duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>
|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!
http://www.sql-server-performance.com/rd_delete_duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>
|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.com/rd_delete_duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>
|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?
Making a column's values unique
I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!http://www.sql-server-performance.c..._duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.c..._duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!http://www.sql-server-performance.c..._duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.c..._duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?
Making a column's values unique
I have an INTEGER column that I want to convert to a primary key. However,
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!http://www.sql-server-performance.com/rd_delete_duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.com/rd_delete_duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>>I have an INTEGER column that I want to convert to a primary key. However,
>>some of the values currently in the dataset are not unique. What query
>>will delete any records with duplicate values of the intended index?
>> Many thanks!
>|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?
some of the values currently in the dataset are not unique. What query will
delete any records with duplicate values of the intended index?
Many thanks!http://www.sql-server-performance.com/rd_delete_duplicates.asp
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/kb/139444
Andrew J. Kelly SQL MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>I have an INTEGER column that I want to convert to a primary key. However,
>some of the values currently in the dataset are not unique. What query will
>delete any records with duplicate values of the intended index?
> Many thanks!
>|||Thanks! This > http://www.sqlteam.com/item.asp?ItemID=3331 did the trick.
Regards,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uzNZXBPkHHA.5048@.TK2MSFTNGP04.phx.gbl...
>
> http://www.sql-server-performance.com/rd_delete_duplicates.asp
> http://www.sqlteam.com/item.asp?ItemID=3331
> http://support.microsoft.com/kb/139444
>
> --
> Andrew J. Kelly SQL MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:eZld$eOkHHA.4628@.TK2MSFTNGP06.phx.gbl...
>>I have an INTEGER column that I want to convert to a primary key. However,
>>some of the values currently in the dataset are not unique. What query
>>will delete any records with duplicate values of the intended index?
>> Many thanks!
>|||How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
'Microsoft SQL Server tables should never contain duplicate rows,
nor non-unique primary keys...Duplicate PKs are a violation of
entity integrity, and should be disallowed in a relational system.'
While it is not surprising that any vetting process (should it even
exist) at Redmond would allow this nonsense to seep through, what
is particular disturbing is how it could possibly pass through
at leading institutions of learning. One can only paraphrase
the great Met philosopher Casey Stengel: is there anybody here
that knows how to play this here relational game?
Make varchar field unique
Anybody know a way to make a varchar field not allow duplicates besides
making it a primary key?hi adam,
how about creating unique constraint on the column.
Ex:
create table #t(col1 varchar(50) unique)
insert into #t values('a')
insert into #t values('b')
insert into #t values('a') -- error
-- Vishal
making it a primary key?hi adam,
how about creating unique constraint on the column.
Ex:
create table #t(col1 varchar(50) unique)
insert into #t values('a')
insert into #t values('b')
insert into #t values('a') -- error
-- Vishal
Make Table Query
Dear Sir/Madam,
I want to create a replica of another table with all the constraints except primary key.
when i tried the command
SELECT * INTO vch1 FROM Voucher
none of the constraints like DEFAULT, NOT NULL are defined in the new table.
pl. guide me.
with regards
wilfi
not sure if this is what you are looking for. In SQL 2005 if you right click the table in SSMS then select Script Table As, then CREATE To you can script the table to a new query window. This will provide all the SQL required to create the table as well as all constraints. You can then simply remove the primary key once the table is created.
Hope this helps,
Grant|||SELECT ... INTO does NOT transfer any table constraints -it only transfers data to the new table.|||Or you can just remove the primary key constraint from the DDL that you get from the Script Table As command. Generally speaking though, it is a best practice to build your objects from a file so you can maintain the table create in version control. Then you can just use that script to recreate the table.|||
hey why dont u just right click on the table in enterprise manager and select "COPY" option and then paste in in the Query Analyser window u will get all the constraints u can then change the table name and execute
RegarDs,
Jacx
|||Thank u sir.
Yes I could do it that way. But i thought with the make table query there may be some option to carry the constraints.
thanks a lot.
with regards
wilfi
|||
Dear Jac,
Yes i could do that & i did that at last. First i thought there may be some option in the
make query to carry the constraints.
thank u.
with regards
wilfi
Monday, March 12, 2012
Make a Job continue after Primary Key Violation?
i have a stored procedure that processes new incoming data. during this
procedure i have many INSERT commands that may result with a duplicate key
violation.
this kind of error should not stop the process and the algorithm continue.
when i run it manually ( EXEC SP1 ...) it behaves ok and continue even when
primary key violation occur.
BUT, when i run it via a job (single step) it quits on the 1st error!!!
WHY ?
how can i force the job/SP to continue running after data errors like key
violations '
thanks
rafiThere are errors in sql that are not trappable. Erland has some good writing
that you should take a look:
http://www.sommarskog.se/error-handling-I.html
-oj
"Rafi" <Rafi@.discussions.microsoft.com> wrote in message
news:93C834D3-71DA-48AF-B2C6-2151EC06F136@.microsoft.com...
>i have a stored procedure that processes new incoming data. during this
> procedure i have many INSERT commands that may result with a duplicate key
> violation.
> this kind of error should not stop the process and the algorithm continue.
> when i run it manually ( EXEC SP1 ...) it behaves ok and continue even
> when
> primary key violation occur.
> BUT, when i run it via a job (single step) it quits on the 1st error!!!
> WHY ?
> how can i force the job/SP to continue running after data errors like key
> violations '
> thanks
> rafi|||Hi
Why not add a where clause to the insert statement to check the PK value
does not exist? If they do exist you may want to log the fact.
It may not a good idea to SET XACT_ABORT OFF as you may miss something that
is important.
John
"Rafi" wrote:
> i have a stored procedure that processes new incoming data. during this
> procedure i have many INSERT commands that may result with a duplicate key
> violation.
> this kind of error should not stop the process and the algorithm continue.
> when i run it manually ( EXEC SP1 ...) it behaves ok and continue even whe
n
> primary key violation occur.
> BUT, when i run it via a job (single step) it quits on the 1st error!!!
> WHY ?
> how can i force the job/SP to continue running after data errors like key
> violations '
> thanks
> rafi
procedure i have many INSERT commands that may result with a duplicate key
violation.
this kind of error should not stop the process and the algorithm continue.
when i run it manually ( EXEC SP1 ...) it behaves ok and continue even when
primary key violation occur.
BUT, when i run it via a job (single step) it quits on the 1st error!!!
WHY ?
how can i force the job/SP to continue running after data errors like key
violations '
thanks
rafiThere are errors in sql that are not trappable. Erland has some good writing
that you should take a look:
http://www.sommarskog.se/error-handling-I.html
-oj
"Rafi" <Rafi@.discussions.microsoft.com> wrote in message
news:93C834D3-71DA-48AF-B2C6-2151EC06F136@.microsoft.com...
>i have a stored procedure that processes new incoming data. during this
> procedure i have many INSERT commands that may result with a duplicate key
> violation.
> this kind of error should not stop the process and the algorithm continue.
> when i run it manually ( EXEC SP1 ...) it behaves ok and continue even
> when
> primary key violation occur.
> BUT, when i run it via a job (single step) it quits on the 1st error!!!
> WHY ?
> how can i force the job/SP to continue running after data errors like key
> violations '
> thanks
> rafi|||Hi
Why not add a where clause to the insert statement to check the PK value
does not exist? If they do exist you may want to log the fact.
It may not a good idea to SET XACT_ABORT OFF as you may miss something that
is important.
John
"Rafi" wrote:
> i have a stored procedure that processes new incoming data. during this
> procedure i have many INSERT commands that may result with a duplicate key
> violation.
> this kind of error should not stop the process and the algorithm continue.
> when i run it manually ( EXEC SP1 ...) it behaves ok and continue even whe
n
> primary key violation occur.
> BUT, when i run it via a job (single step) it quits on the 1st error!!!
> WHY ?
> how can i force the job/SP to continue running after data errors like key
> violations '
> thanks
> rafi
Subscribe to:
Posts (Atom)