If Else question URGENT

I want to run a query that will insert rows into another table.

I also want to do some calculations on a couple of the columns:


SELECT
KEYCODESTRINGDESCRIPTION,
STRING,
Mailed,
Sales,
Orders,
CATALOGTITLE,
Response = Orders / Mailed,
[Average Invoice] = Sales / Orders,
SMP = (Sales / Mailed) * 1000

INTO TP_GA_REPORT
FROM TP_GA_REPORT_TEMP

I have a condition where some of the colums might have a 0 in them, which of course causes a "Divide by 0" error. What I would like to do is put an IF statement in the query to deal with this 0.

i.e.

if orders = 0 then response=0
else response = Response = Orders / Mailed

Hope this makes sense!

Thanks KenTry using SQL CASE:

SELECT ...,
CASE WHEN Mailed = 0 THEN 0 ELSE Orders / Mailed END,
...
FROM ...|||Thank you!!! So very much, worked like a charm!

IDENTITY_INSERT in transaction?

I have to add some rows into a table that is very busy with user-actions. To add the records, I need to use IDENTITY_INSERT ON to ensure that the records keep their original id. The question is: when I execute the insert-script with IDENTITY_INSERT ON, does this setting effect all users or just my transaction?Refer this : http://msdn2.microsoft.com/en-us/library/ms188059.aspx|||I can't find the anwer to my question there, you?|||

Sorry abt the link,.

If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.

|||

It is just for your current session/transaction only. It wont affect others

But before exiting form the SP/your Batch SET back the orginal property..

If your apps uses connection pooling it may cause a issue...

Mantra : if you start it, you have to finish it

sql

Identity Seed

have a table set one column as
data type = int
identity to yes
identity seed = 1 and identity increment = 1
after deleting some rows (e.g. 5), the number 5 will never be re used.
when the number has grown to the limit, can I still insert new rows?when you mention a limit, I am assuming you are talking about a check
constraint.
Regardless, identity column values are not necessary inserted in the order
of sequence, however the seed will always be no lower than the last
committed value.
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Music Lover" <music@.my-heart.org> wrote in message
news:OTtGn5$VDHA.1680@.tk2msftngp13.phx.gbl...
> have a table set one column as
> data type = int
> identity to yes
> identity seed = 1 and identity increment = 1
> after deleting some rows (e.g. 5), the number 5 will never be re used.
> when the number has grown to the limit, can I still insert new rows?
>
>|||No he means what happens when the INT field gets to 0x7FFFFFFF (2147483647)
"Mark Broadbent" <nospamplease_mark.broadbent@.virgin.net> wrote in message
news:VnpWa.582$k4.11506@.news2.nokia.com...
> when you mention a limit, I am assuming you are talking about a check
> constraint.
> Regardless, identity column values are not necessary inserted in the order
> of sequence, however the seed will always be no lower than the last
> committed value.
>
> --
> BR,
> Mark Broadbent mcdba,mcse+i
> _________________________
> "Music Lover" <music@.my-heart.org> wrote in message
> news:OTtGn5$VDHA.1680@.tk2msftngp13.phx.gbl...
> > have a table set one column as
> > data type = int
> > identity to yes
> > identity seed = 1 and identity increment = 1
> >
> > after deleting some rows (e.g. 5), the number 5 will never be re used.
> >
> > when the number has grown to the limit, can I still insert new rows?|||Little Test...
USE PUBS
CREATE TABLE dbo.tblIdentity ( ID INT IDENTITY(2147483640,1) CONSTRAINT
PK_tblIdentity PRIMARY KEY CLUSTERED, DATA CHAR(1) )
GO
INSERT INTO tblIdentity ( DATA )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
UNION ALL SELECT 'F'
UNION ALL SELECT 'G'
UNION ALL SELECT 'H'
UNION ALL SELECT 'I'
UNION ALL SELECT 'J'
GO
SELECT * FROM tblIdentity
GO
DROP TABLE dbo.tblIdentity
The Insert query generates the following Error:-
Server: Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.
So to answer your question.
No you can't insert records when the INT field reaches its limit
(2147483647)
When this happens you will have to reseed the field to -2147483648.
This will give you a few million more records...
When you run out of numbers after going through the negative values it'll be
time to a) archive some data b) change the number to a GUID or c) change the
number to a BIGINT. But 2147million records is enough for most people ;)
DBCC CHECKIDENT ( tblIdentity , RESEED , -2147483648 )
"Music Lover" <music@.my-heart.org> wrote:
> have a table set one column as
> data type = int
> identity to yes
> identity seed = 1 and identity increment = 1
> after deleting some rows (e.g. 5), the number 5 will never be re used.
> when the number has grown to the limit, can I still insert new rows?
>
>|||you can reset the identity seed to start incrementing from
your chosen number...
>--Original Message--
>have a table set one column as
>data type = int
> identity to yes
>identity seed = 1 and identity increment = 1
>after deleting some rows (e.g. 5), the number 5 will
never be re used.
>when the number has grown to the limit, can I still
insert new rows?
>
>.
>|||And bigint can store -9,223,372,036,854,775,808 through
9,223,372,036,854,775,807.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Tony Wilton" <tony@.scruffytiger.co.uk.NOSPAM> wrote in message
news:3f2a3824$0$12152$7b0f0fd3@.mistral.news.newnet.co.uk...
> If you use an INT field yes.
> Maximum values for data types are:-
> TINYINT 255
> SMALLINT 32767
> INT 2147483647
>
> "Music Lover" <music@.my-heart.org> wrote:
> > Thanks for your reply.
> > Want to double confirm
> > I can insert 2147million records withou any problem?
>|||thanks
how?
"jano" <janobermudes@.microsoft.com> wrote in message
news:09a801c35812$d7540810$a401280a@.phx.gbl...
> you can reset the identity seed to start incrementing from
> your chosen number...
>
> >--Original Message--
> >have a table set one column as
> >data type = int
> > identity to yes
> >identity seed = 1 and identity increment = 1
> >
> >after deleting some rows (e.g. 5), the number 5 will
> never be re used.
> >
> >when the number has grown to the limit, can I still
> insert new rows?
> >
> >
> >
> >.
> >

Identity range when rows already exist

I've created a merge publication with automatic range management. Insert
fails because the ranges assigned have already been used. How do I specify
that I want the new identity ranges to start above those which have already
been used?
I created this publication by backing up my production database and
restoring it to my test database. Then I created the publication on my test
database by manually editing the auto-generated script for creating the
publication on the production database. I don't know if this is the reason
things don't work out as I want them to.
I think you would be best to drop this publication and its subscriptions and
recreate from start.
If you are a masochist you can do the following.
Look in your distributor for a table called MSrepl_identity_range. The
highest range is the range which is deployed to one of your subscribers. You
can bump this value up to give yourself a cushion.
For instance if the highest range is 10000, bump it up to 20000, which will
be the next value assigned.
Now go to your problem subscriber and fix the table there. Use dbcc
checkident('tablename') to determine what the current range is, and then
reseed to the value you found on your publisher's distribution database
MSrepl_identity_range table.
Now issue a sp_help 'problemTableName' to get the name of the check
constraint used to restrict the range of possible values acceptable for this
table. script out the check constraint and recreate it with a set of values
which matches the range you assigned with the checkident reseed statement.
If you are really feeling like punishing yourself you might want to read
http://www.simple-talk.com/2005/07/05/replication/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel" <daXniel_kriXstensXen_@.hotmail.com (remove the Xs)> wrote in
message news:92B2698A-DE7C-485C-9381-78295E49A335@.microsoft.com...
> I've created a merge publication with automatic range management. Insert
> fails because the ranges assigned have already been used. How do I specify
> that I want the new identity ranges to start above those which have
already
> been used?
> I created this publication by backing up my production database and
> restoring it to my test database. Then I created the publication on my
test
> database by manually editing the auto-generated script for creating the
> publication on the production database. I don't know if this is the reason
> things don't work out as I want them to.
|||"Hilary Cotter" wrote:

> I think you would be best to drop this publication and its subscriptions and
> recreate from start.
I already did that. Perhaps the problem was that I created the publication
using the script generated by EM. For each merge article it did:
exec sp_addmergearticle ... @.article = [tableName] ...
go
To solve the problem, for all merge articles for which I use automatic range
management I added:
declare @.NewID int
Select @.newID = max(ID)+1 FROM [tableName]
DBCC CHECKIDENT([tableName],RESEED,@.newID)
exec sp_addmergearticle @.article = [tableName] ...
go
That is, I reseed the identity for each table before adding it to the
publication. It seems to work.

>If you are really feeling like punishing yourself you might want to read
> http://www.simple-talk.com/2005/07/05/replication/
Thanks I did that. You got all these great articles scattered all over the
net. But your book about merge replication is due any week now, right? It
would be nice to have the information gathered in one place

IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[tablename] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.
You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call that
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
David Portas
SQL Server MVP

IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[table
name] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call tha
t
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
David Portas
SQL Server MVP
--

IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[tablename] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call that
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
--
David Portas
SQL Server MVP
--

IDENTITY column?

I'm using an IDENTITY column (datatype = integer) for the first time and
am not quite sure it's what I want. I'm deleting rows from its table
quite often. I'd like a way so that when my stored procedure inserts
rows I can first issue a separate command that tells the column to
restart to a value of 1, and then the INSERTs will actually insert the
1, then 2, then 3...
I looked at setting IDENTITY_INSERT *on* but I see this simply allows
you to manually set the value right in the INSERT statement which is not
what I want. I want to be able to have two separate processes:
-- restart the numbering to 1 on this column
-- ok, now here's my INSERT statements that will actually insert 1, then
2, then 3...
Is this possible? Thanks.Lookup DBCC CHECKINDENT in Books On Line
DBCC CHECKIDENT (TAbleNAme, RESEED, 1)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Rick Charnes wrote:
> I'm using an IDENTITY column (datatype = integer) for the first time and
> am not quite sure it's what I want. I'm deleting rows from its table
> quite often. I'd like a way so that when my stored procedure inserts
> rows I can first issue a separate command that tells the column to
> restart to a value of 1, and then the INSERTs will actually insert the
> 1, then 2, then 3...
> I looked at setting IDENTITY_INSERT *on* but I see this simply allows
> you to manually set the value right in the INSERT statement which is not
> what I want. I want to be able to have two separate processes:
> -- restart the numbering to 1 on this column
> -- ok, now here's my INSERT statements that will actually insert 1, then
> 2, then 3...
> Is this possible? Thanks.|||Thanks much. Is there another way to do what I'm trying to do? I'm not
sure my DBA will be happy with me using DBCC -- the user running the
proc may not have permissions.
In article <1149690037.334461.34780@.i40g2000cwc.googlegroups.com>,
denis.gobo@.gmail.com says...
> Lookup DBCC CHECKINDENT in Books On Line
> DBCC CHECKIDENT (TAbleNAme, RESEED, 1)
>|||Yes use TRUNCATE TableName instead of DELETE TableName
It is faster and will also reset the identity, however you need to have
db_owner or db_ddladmin privileges to use TRUNCATE
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Rick Charnes wrote:
> Thanks much. Is there another way to do what I'm trying to do? I'm not
> sure my DBA will be happy with me using DBCC -- the user running the
> proc may not have permissions.
> In article <1149690037.334461.34780@.i40g2000cwc.googlegroups.com>,
> denis.gobo@.gmail.com says...|||DBCC requires a lower level of permissions than TRUNCATE, then? I think
of DBCC as something run by DBA's and not so commonly used in daily
business-related stored procedures run by users -- am I wrong?
I guess I was wondering if there's another way to do this other than
using IDENTITY -- a way to automatically insert (and increment) a value
into a table without having to include it in your INSERT statement.
Your help is much appreciated.
In article <1149691117.373637.158280@.i39g2000cwa.googlegroups.com>,
denis.gobo@.gmail.com says...
> Yes use TRUNCATE TableName instead of DELETE TableName
> It is faster and will also reset the identity, however you need to have
> db_owner or db_ddladmin privileges to use TRUNCATE
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/|||> I guess I was wondering if there's another way to do this other than
> using IDENTITY -- a way to automatically insert (and increment) a value
> into a table without having to include it in your INSERT statement.
If you want the system to generate a number for you, why do you care so much
what it is, or where it starts at?
Anyway, there are ways to do this, like using a central sequence table,
wrapping a function around it, and setting the default value in the column
in your table to be the result of the function. But you will have to lock
the central table, which will serialize inserts, and may have serious
performance implications. Have a look at these conversations for some
ideas:
http://tinyurl.com/lnm5n|||> DBCC requires a lower level of permissions than TRUNCATE, then?
No, same same, see below two quotes from Books Online:
DBCC CHECKIDENT permissions default to the table owner, members of the sy
min fixed server role,
and the db_owner and db_ddladmin fixed database role, and are not transferab
le.
TRUNCATE TABLE permissions default to the table owner, members of the sym
in fixed server role,
and the db_owner and db_ddladmin fixed database roles, and are not transfera
ble.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1ef0b3eff9279e84989940@.msnews.microsoft.com...
> DBCC requires a lower level of permissions than TRUNCATE, then? I think
> of DBCC as something run by DBA's and not so commonly used in daily
> business-related stored procedures run by users -- am I wrong?
> I guess I was wondering if there's another way to do this other than
> using IDENTITY -- a way to automatically insert (and increment) a value
> into a table without having to include it in your INSERT statement.
> Your help is much appreciated.
> In article <1149691117.373637.158280@.i39g2000cwa.googlegroups.com>,
> denis.gobo@.gmail.com says...

Identity Column Increment Control

Hi,
I have a table with identity column. I set the increment
to 1. But after I have deleted a couple of rows then
inserted a new row, the increment is not based on the
existing row number. For example, I had 100 rows already.
After I deleted two rows from the bottom., the last row I
have is 98. Then if I insert another row, it starts from
101 instead of 99. How can I solve this problem.
Thanks,
Derek
This is expected. The next identity value will not be in sequence, you will
see gaps in the identity values. The increment is not based on the existing
row number, however it will be the next of last generated identity value for
the table.
You can use "dbcc checkident" to reset the identity value of the table.
ex:
create table tt(i int not null identity, ii varchar(6000))
go
insert into tt (ii) values('x')
insert into tt (ii) values('y')
go
delete from tt where i = 2
go
DBCC CHECKIDENT (tt, RESEED, 1)
GO
insert into tt (ii) values('z')
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||>> The increment is not based on the existing
row number, <<
I mean to say, it is not based on the last value of the identity column.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Identity Column Increment Control

Hi,
I have a table with identity column. I set the increment
to 1. But after I have deleted a couple of rows then
inserted a new row, the increment is not based on the
existing row number. For example, I had 100 rows already.
After I deleted two rows from the bottom., the last row I
have is 98. Then if I insert another row, it starts from
101 instead of 99. How can I solve this problem.
Thanks,
DerekThis is expected. The next identity value will not be in sequence, you will
see gaps in the identity values. The increment is not based on the existing
row number, however it will be the next of last generated identity value for
the table.
You can use "dbcc checkident" to reset the identity value of the table.
ex:
create table tt(i int not null identity, ii varchar(6000))
go
insert into tt (ii) values('x')
insert into tt (ii) values('y')
go
delete from tt where i = 2
go
DBCC CHECKIDENT (tt, RESEED, 1)
GO
insert into tt (ii) values('z')
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||>> The increment is not based on the existing
row number, <<
I mean to say, it is not based on the last value of the identity column.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Identity Column

Hi all,
I am using a identity column in my table. When any rows are deleted in the identity column. The next value is not assigned to the previously deleted number. ie In one column iam generating sequence of number from 1 to 10. for example, the current row is 5. Now iam deleting the row 5. Next number should come only 5. Instead of that, the next value generated is 6. Can u suggest me any idea on this. Of couse, it is the default property how to over come this. Pls help me.

HenryHi there
AFAIK identities (auto assigned) are unique - that means used just once.

I had the same usage for a identity-row, i've simply used non auto-assignment and a procedure wich searches for the lowest free key and assigns it...

greetz
c0r0n3r|||This is the nature of the IDENTITY property. The following is an excerpt from BOL:

"If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. However, to ensure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON."

So basically if you continue to use the IDENTITY property and wish to attempt to fill the gaps you will have to use the SET IDENTITY_INSERT ON statement.

coroner already mentioned an alternative method if this is a problem. Others just ignore the gaps. Or you can use the method mentioned above.|||Thank u rnealejr & coroner. It was so useful to me.
Thank u so much.|||I'm wondering where SQL Server saves this information about what the "next" generated identity number should be?

i.e How does it know it's been used and deleted.

thanks
js

Identity Column

How do you insert into a table that has an identity column aset of rows with predefined values for the identity column.
I have tried to Set Identity_insert off and then insert but it didnt work.
any ideas?SET IDENTITY_INSERT table ON

Insert into table (columns) select * from table2

Identifying the ErrorColumn (in rejected data rows)

I've noticed that when a row fails to transform/parse, (I.E., the data was truncated) you can use the DFT audit component to see which column contained the error. However, this error column displays the ID of a particular column, NOT the actual order in which the columns are setup (defined in the conn mgr).

Is there any way to modify this so that it will show the sequencial column number, or at the very least, the externalmetadatacolum number?

thanks,

I had hoped that it would be easy to use a Script Component to add Error Description (in place of ErrorCode) and Column Name (in place of ErrorColumn) to a standard error output.

However, while the former is easy (using Me.ComponentMetadata.GetErrorDescription), the latter is not. The column ID that you have in ErrorColumn is the ID of the column in the column collection of the previous component, where the error occurred, and is no longer the same if you choose to include the column of the same name among the input columns of your Script component. So you can't grab the column object to get its name or any of its other properties and, of course, you can't grab a runtime reference to any other components in the data flow.

-Doug