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 key. Show all posts
Showing posts with label key. Show all posts
Friday, March 23, 2012
Making a report smaller...?
Alright. I'm stuck. I admit it!
I have a bunch of names, and each name can have one or more 'roles'(operator, reader, key operator, etc. Just random words really.) attached to it.
Using reporting services, I've managed to get the information I need with relative ease... the only problem is, with 900 some records to display, it's current length of 41 pages with just one column going down the left side of each page is not exactly preferred by my superior (can't say I blame him really. Looks kind of odd!)
It looks like this right now:
Name1
Function
Function
Function
Name2
Function
Function
Name3
Function
Function
etc all the way down to page 41
I need it to look something like this:
Name 1 Name 4 Name 7
Function Function Function
Name 2 Name 5 Function
Function Function Name 8
Function Function Function
Name 3 Name 6 Function
Function Function Function
etc. Or some variation of...
I've fiddled around, and merely adding one extra column to the initial table-layout with the same =(!UserName etc) just merely replicates the data in the second column... not giving me the new stuff.
I'm quite new to reporting services, but none of the tutorials I've seen/done seem to accomodate for this... Heeelp!
How about this:
Select the first third of the data as a field for your dataset (you'll need some index field in your data to indicate how much data you've selected). Call it say, Column1.
Select the second and third part of the data similarly as respective fields accordingly. Call these Column 2 and Column 3.
Then set up a table or matrix with fields for Column 1 2 and 3.
|||You can put your table into multiple columns on the page by:
From the main menu, select report|report properties
select Layout tab
Change the columns to 3
type 0in for spacing and .5in for the margins
check it out in preview
these instuctions are from the book SSRS2005 by Brian Larson, get your own copy from Amazon
|||Awesome. That does the job right there.
Thanks for the help! Both of you 
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 XACT_ABORT persist?
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation) occurs. I currently accomplish this by prefixing all of my
queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent VegaJust turning that on will eventually help you with your present problems, bu
t will make a lot of trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the curre
nt transaction if a Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ign
oring the error about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag news:aJadna7pIuwCfh
3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation) occurs. I currently accomplish this by prefixing all of my
queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega|||I fully agree with Jens. If you still want to turn this on, you can at the i
nstance level using
sp_configure. Note that many tools etc are not designed to use this setting,
so be aware.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message
news:eDuF9kYVFHA.3312@.TK2MSFTNGP10.phx.gbl...
Just turning that on will eventually help you with your present problems, bu
t will make a lot of
trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the curre
nt transaction if a
Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ign
oring the error about
that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag
news:aJadna7pIuwCfh3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation)
occurs. I currently accomplish this by prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option
on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation) occurs. I currently accomplish this by prefixing all of my
queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent VegaJust turning that on will eventually help you with your present problems, bu
t will make a lot of trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the curre
nt transaction if a Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ign
oring the error about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag news:aJadna7pIuwCfh
3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation) occurs. I currently accomplish this by prefixing all of my
queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega|||I fully agree with Jens. If you still want to turn this on, you can at the i
nstance level using
sp_configure. Note that many tools etc are not designed to use this setting,
so be aware.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message
news:eDuF9kYVFHA.3312@.TK2MSFTNGP10.phx.gbl...
Just turning that on will eventually help you with your present problems, bu
t will make a lot of
trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the curre
nt transaction if a
Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ign
oring the error about
that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag
news:aJadna7pIuwCfh3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation)
occurs. I currently accomplish this by prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option
on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
Make XACT_ABORT persist?
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key constraint violation) occurs. I currently accomplish this by prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continually set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
...which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
Just turning that on will eventually help you with your present problems, but will make a lot of trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the current transaction if a Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ignoring the error about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag news:aJadna7pIuwCfh3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key constraint violation) occurs. I currently accomplish this by prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continually set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
...which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
|||I fully agree with Jens. If you still want to turn this on, you can at the instance level using
sp_configure. Note that many tools etc are not designed to use this setting, so be aware.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in message
news:eDuF9kYVFHA.3312@.TK2MSFTNGP10.phx.gbl...
Just turning that on will eventually help you with your present problems, but will make a lot of
trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the current transaction if a
Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ignoring the error about
that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag
news:aJadna7pIuwCfh3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key constraint violation)
occurs. I currently accomplish this by prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continually set this option
on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
...which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
I want my transactions to abort when a runtime error (i.e. foreign key constraint violation) occurs. I currently accomplish this by prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continually set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
...which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
Just turning that on will eventually help you with your present problems, but will make a lot of trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the current transaction if a Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ignoring the error about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag news:aJadna7pIuwCfh3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key constraint violation) occurs. I currently accomplish this by prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continually set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
...which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
|||I fully agree with Jens. If you still want to turn this on, you can at the instance level using
sp_configure. Note that many tools etc are not designed to use this setting, so be aware.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in message
news:eDuF9kYVFHA.3312@.TK2MSFTNGP10.phx.gbl...
Just turning that on will eventually help you with your present problems, but will make a lot of
trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the current transaction if a
Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ignoring the error about
that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag
news:aJadna7pIuwCfh3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key constraint violation)
occurs. I currently accomplish this by prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continually set this option
on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
...which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
Make XACT_ABORT persist?
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation) occurs. I currently accomplish this by prefixing all of my
queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent VegaJust turning that on will eventually help you with your present problems, bu
t will make a lot of trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the curre
nt transaction if a Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ign
oring the error about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag news:aJadna7pIuwCfh
3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation) occurs. I currently accomplish this by prefixing all of my
queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega|||I fully agree with Jens. If you still want to turn this on, you can at the i
nstance level using
sp_configure. Note that many tools etc are not designed to use this setting,
so be aware.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message
news:eDuF9kYVFHA.3312@.TK2MSFTNGP10.phx.gbl...
Just turning that on will eventually help you with your present problems, bu
t will make a lot of
trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the curre
nt transaction if a
Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ign
oring the error about
that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag
news:aJadna7pIuwCfh3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation)
occurs. I currently accomplish this by prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option
on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation) occurs. I currently accomplish this by prefixing all of my
queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent VegaJust turning that on will eventually help you with your present problems, bu
t will make a lot of trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the curre
nt transaction if a Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ign
oring the error about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag news:aJadna7pIuwCfh
3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation) occurs. I currently accomplish this by prefixing all of my
queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega|||I fully agree with Jens. If you still want to turn this on, you can at the i
nstance level using
sp_configure. Note that many tools etc are not designed to use this setting,
so be aware.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message
news:eDuF9kYVFHA.3312@.TK2MSFTNGP10.phx.gbl...
Just turning that on will eventually help you with your present problems, bu
t will make a lot of
trouble when leaving this as a global setting:
"Specifies whether Microsoft SQL ServerT automatically rolls back the curre
nt transaction if a
Transact-SQL statement raises a run-time error."
I would rather do some error handling than just pushing in all data with ign
oring the error about
that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vincent Vega" <Pulp@.Fiction.com> schrieb im Newsbeitrag
news:aJadna7pIuwCfh3fRVn-3w@.giganews.com...
Hi,
I want my transactions to abort when a runtime error (i.e. foreign key const
raint violation)
occurs. I currently accomplish this by prefixing all of my queries with:
SET XACT_ABORT ON
Is there a way to set this option globally so that I don't have to continual
ly set this option
on each query? My first attempt to do this was:
EXEC sp_dboption 'MyDatabaseName', 'xact_abort', 'true'
..which returned the following error message:
Database option 'xact_abort' does not exist.
Any help would be appreciated. Thanks.
Sincerely,
Vincent Vega
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
Make a copy of parts of a ROW, but with a new Key.
Hi there,
I want to insert a new record into my database table, giving new field
values for all but one (an image field), which I can copied from an existing
record. The background to this is that I am allowing a user to create a new
"template" from an existing one, thus I want a new record, but also to copy
the existing template data blob into the new record.
My code looks like this, but obviously doesn't work (I don't supply a field
for the first field "ID" in my SELECT statement which should be a unique ID.
Obviously, I want SQL to automatically generate one for me!). Is there any
way to do this with SQL?
IF @.Error = 0
BEGIN
SELECT @.DateCreated,
@.Title,
@.Description,
@.Thumbnail,
ReportTemplate.Report
INTO
ReportTemplate
FROM
ReportTemplate
SET @.Error = @.@.ERROR
ENDI think I got it. Like this:
IF @.Error = 0
BEGIN
INSERT INTO
ReportTemplate
(
DateCreated,
Title,
Description,
IsStatic,
Thumbnail,
Report
)
SELECT
@.DateCreated,
@.Title,
@.Description,
@.IsStatic,
@.Thumbnail,
ReportTemplate.Report
FROM
ReportTemplate
WHERE
ReportTemplate.ID = @.ID
SET @.Error = @.@.ERROR
END
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:c635gr$483$1$8302bc10@.news.demon.co.uk...
> Hi there,
> I want to insert a new record into my database table, giving new field
> values for all but one (an image field), which I can copied from an
existing
> record. The background to this is that I am allowing a user to create a
new
> "template" from an existing one, thus I want a new record, but also to
copy
> the existing template data blob into the new record.
> My code looks like this, but obviously doesn't work (I don't supply a
field
> for the first field "ID" in my SELECT statement which should be a unique
ID.
> Obviously, I want SQL to automatically generate one for me!). Is there
any
> way to do this with SQL?
> IF @.Error = 0
> BEGIN
> SELECT @.DateCreated,
> @.Title,
> @.Description,
> @.Thumbnail,
> ReportTemplate.Report
> INTO
> ReportTemplate
> FROM
> ReportTemplate
> SET @.Error = @.@.ERROR
> END
Subscribe to:
Posts (Atom)