Monday, March 26, 2012
IF @paramter <> something
IF @.MESSAGE_CODE <> 2
The paramter is defined like @.MESSAGE_CODE NUMERIC ,I print the value and
it;s for example 1 but it never gets into the preceding block. 1<> 2 ,right?
but it dosen't work.
Any suggestions,
ThanksHow can we reproduce the problem?
If @.MESSAGE_CODE is null, then it is not considered as diff.
Example:
if null != 2 print 'hello!'
else print 'hi!'
AMB
"Ray5531" wrote:
> I'm using this statement in my sp to check a paramter value:
> IF @.MESSAGE_CODE <> 2
> The paramter is defined like @.MESSAGE_CODE NUMERIC ,I print the value and
> it;s for example 1 but it never gets into the preceding block. 1<> 2 ,righ
t?
> but it dosen't work.
> Any suggestions,
> Thanks
>
>|||Ray said: ,I print the value and it;s for example 1
It's not null:-) I'm printing the paramtere.
Thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C872E8D0-B347-4FC1-A1CE-D0C43134CF93@.microsoft.com...
> How can we reproduce the problem?
> If @.MESSAGE_CODE is null, then it is not considered as diff.
> Example:
> if null != 2 print 'hello!'
> else print 'hi!'
>
> AMB
> "Ray5531" wrote:
>|||Well, your post is incomplete, It's probably some syntax error, but without
looking at SP Code, it's impossible to guess...
Post the complete code...
"Ray5531" wrote:
> I'm using this statement in my sp to check a paramter value:
> IF @.MESSAGE_CODE <> 2
> The paramter is defined like @.MESSAGE_CODE NUMERIC ,I print the value and
> it;s for example 1 but it never gets into the preceding block. 1<> 2 ,righ
t?
> but it dosen't work.
> Any suggestions,
> Thanks
>
>|||Ray5531 wrote:
> I'm using this statement in my sp to check a paramter value:
> IF @.MESSAGE_CODE <> 2
> The paramter is defined like @.MESSAGE_CODE NUMERIC ,I print the value
> and it;s for example 1 but it never gets into the preceding block.
> 1<> 2 ,right? but it dosen't work.
> Any suggestions,
> Thanks
Must be a syntax issue as others have stated:
declare @.n numeric
set @.n = 1
print @.n
if @.n <> 2
print 'not equal'
Make sure you have a Begin / End after the IF block if you are using
more than one statement after the IF (good idea to use them in any
case).
David Gugick
Imceda Software
www.imceda.com|||here is the complete sp code:
CREATE PROCEDURE [dbo].[IF_InsertLog]
@.INTERNAL_FILEID NUMERIC,
@.AFFILIATE_ID NUMERIC=NULL,
@.CLIENT_ID VARCHAR(10)=NULL,
@.LINE_NUMBER NUMERIC,
@.COLUMN_ORDER NUMERIC,
@.MESSAGE_CODE NUMERIC,
@.MESSAGE VARCHAR(500),
@.CREATED_BY_USER VARCHAR(30),
@.C1STATUS_CODE VARCHAR(6)=NULL
AS
SET NOCOUNT ON
DECLARE @.NEW_LOGENTRY VARCHAR(1)
DECLARE @.C4ENROLLMENT_ID NUMERIC(9)
DECLARE @.C4ENROLMENT_WITHERROR_COUNT NUMERIC(9)
SET @.C4ENROLLMENT_ID= (SELECT CAST(ENROLLMENT_ID AS NUMERIC(9)) FROM
dbo.IF_C4TRANSFORM WHERE FILEID=1355 AND
LINE_NUMBER=5)
IF @.MESSAGE_CODE <> 2
IF @.CREATED_BY_USER='C1_DTS'
IF EXISTS (SELECT LOG_ID FROM IF_LOG WHERE
AFFILIATE_ID=@.AFFILIATE_ID AND CLIENT_ID= @.CLIENT_ID AND
COLUMN_ORDER=@.COLUMN_ORDER AND CREATED_BY_USER=@.CREATED_BY_USER )
SET @.NEW_LOGENTRY='N'
ELSE
SET @.NEW_LOGENTRY='Y'
ELSE IF (@.CREATED_BY_USER='C4_DTS')
BEGIN
SELECT @.C4ENROLLMENT_ID=CAST(ENROLLMENT_ID AS NUMERIC(9)) FROM
dbo.IF_C4TRANSFORM WHERE FILEID=@.INTERNAL_FILEID AND
LINE_NUMBER=@.LINE_NUMBER
SELECT @.C4ENROLMENT_WITHERROR_COUNT = COUNT(*) FROM dbo.IF_C4TRANSFORM
T1 INNER JOIN dbo.IF_C4TRANSFORM T2 ON
T1.ENROLLMENT_ID = T2.ENROLLMENT_ID AND
T1.ERROR_STATUS = T2.ERROR_STATUS
IF @.C4ENROLMENT_WITHERROR_COUNT > 0
SET @.NEW_LOGENTRY='N'
ELSE
SET @.NEW_LOGENTRY='Y'
END
ELSE IF (@.CREATED_BY_USER='C4_DTS_HISTORY')
IF EXISTS (SELECT LOG_ID FROM IF_LOG WHERE
AFFILIATE_ID=@.AFFILIATE_ID AND CLIENT_ID= @.CLIENT_ID AND
COLUMN_ORDER=@.COLUMN_ORDER AND CREATED_BY_USER=@.CREATED_BY_USER )
SET @.NEW_LOGENTRY='N'
ELSE
SET @.NEW_LOGENTRY='Y'
ELSE
SET @.NEW_LOGENTRY='U'
ELSE
PRINT 'A Warning has been reported and there is no need for New/OLD
detection'
SET @.NEW_LOGENTRY=@.MESSAGE_CODE
INSERT INTO DBO.IF_LOG (INTERNAL_FILEID,
AFFILIATE_ID,
CLIENT_ID,
LINE_NUMBER,
COLUMN_ORDER,
MESSAGE_CODE,
MESSAGE,
CREATED_BY_USER,
NEW_LOGENTRY,
C1STATUS_CODE,
C4ENROLLMENT_ID)
VALUES(@.INTERNAL_FILEID,
@.AFFILIATE_ID,
@.CLIENT_ID,
@.LINE_NUMBER,
@.COLUMN_ORDER,
@.MESSAGE_CODE,
@.MESSAGE,
@.CREATED_BY_USER,
@.NEW_LOGENTRY,
@.C1STATUS_CODE,
@.C4ENROLLMENT_ID)
GO|||Use BEGIN and END statements in all blocks and see if the problem still
occurs. Also, why have you chosen to use the NUMERIC data type? Why not
using something like an INT instead?
David Gugick
Imceda Software
www.imceda.com|||What's wrong with numeric?
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23$jgDwnUFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Use BEGIN and END statements in all blocks and see if the problem still
> occurs. Also, why have you chosen to use the NUMERIC data type? Why not
> using something like an INT instead?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||They store more information and so take up more space. If you do not need
to keep decimal places (very rare with IDs), you're better off with INT, and
I typically use SMALLINT or TINYINT in finite data sets.
Even the smallest NUMERIC declaration takes up one more byte than a full
INT. And the default (18,0) takes more than double (9 bytes).
http://www.aspfaq.com/2503
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Ray5531" <RayAll@.microsft.com> wrote in message
news:OfF6x0nUFHA.580@.TK2MSFTNGP15.phx.gbl...
> What's wrong with numeric?|||Apparently there was some problem using INT and thet's what DBA forces us to
do.I don;t know why and I don't care.I've told not to use INT:-) I know
that's funny but you know....
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:O2fC$QoUFHA.3572@.TK2MSFTNGP12.phx.gbl...
> They store more information and so take up more space. If you do not need
> to keep decimal places (very rare with IDs), you're better off with INT,
> and I typically use SMALLINT or TINYINT in finite data sets.
> Even the smallest NUMERIC declaration takes up one more byte than a full
> INT. And the default (18,0) takes more than double (9 bytes).
> http://www.aspfaq.com/2503
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:OfF6x0nUFHA.580@.TK2MSFTNGP15.phx.gbl...
>sql
Wednesday, March 21, 2012
identity value gaps
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.This is not a problem. The identity does not guarantee that you will get no
gaps. In the event that an INSERT occurs in a transaction, the identity it
would have taken does not get re-used.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
Hi,
In production system we have a table which takes orders, order-id is defined
with identity(1, 1) as part of table defination. Problem is that most of the
time they see order-id's in sequence, but some time they see gaps in
order-id's , so busines rep asking me what happened with gaps. I asked
developers, are there any deletes or is it transactionalised. But they told
me that it is very simple application, have one straight forward Insert
command. I am wondering how can i track id gaps. I dont want to go with
alternate solution, only i need is an answer to business rep why there are
gaps. It is happening once in a month. Let me know if you need more info
Thanks,
Subbu.|||"subbu" <subbaiahd@.hotmail.com> wrote in message
news:uVWDAo9ZGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Hi,
> In production system we have a table which takes orders, order-id is
defined
> with identity(1, 1) as part of table defination. Problem is that most of
the
> time they see order-id's in sequence, but some time they see gaps in
> order-id's , so busines rep asking me what happened with gaps. I asked
> developers, are there any deletes or is it transactionalised. But they
told
> me that it is very simple application, have one straight forward Insert
> command. I am wondering how can i track id gaps. I dont want to go with
> alternate solution, only i need is an answer to business rep why there are
> gaps. It is happening once in a month. Let me know if you need more info
>
Basically any time an IDENTITY is generated, it's "used up".
So if someone generates an order, but say cancels it before it's completed,
that IDENTITY value is still used up.
Identity columns should NOT be used in almost all cases for an externally
used identifier for this and other reasons.
Imagine for example you BCP the data out, truncate the table and then BCP it
in (very rare to do, but not unheard of).
Your data will NOT have gaps now (or if it does, they may be different.)
This means all your Order-IDs are now "wrong".
> Thanks,
> Subbu.
>
Monday, March 12, 2012
IDENTITY limit
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I defined a primary field using integer 4 bytes as IDENTITY.
>> What will be the limit of this number?
>> I am not sure the number is big enough in 10 years of time after my
>> application have been running.
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>
IDENTITY limit
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>
IDENTITY limit
What will be the limit of this number?
I am not sure the number is big enough in 10 years of time after my
application have been running."Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>
2^31-1 (2,147,483,647)
Keep in mind that all numbers are used, even if not allocated.
So if someone begins a transaction, inserts 1000 rows and rolls it back,
then 1000 IDENTITY values are "used" up.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||2 billion. If that's not enough, you could start the seed at -2 billion and
double the capacity that way. If that's still not enough, you could use
BIGINT (and start at a negative number here, too), and if your app is going
to find a way to exceed that limit, then you should probably consider not
using an integer at all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
>I defined a primary field using integer 4 bytes as IDENTITY.
> What will be the limit of this number?
> I am not sure the number is big enough in 10 years of time after my
> application have been running.
>|||Hello,
To add to Greg, the limitation wil be based on the usage. Incase if you feel
the INT is not big enough then go ahead and use the BIGINT data type
which will allow 2^63-1 (9,223,372,036,854,775,807) and the storage will be
8 bytes.
Thanks
Hari
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:ufoLBFdZHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Alan T" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23Q6$b5cZHHA.588@.TK2MSFTNGP06.phx.gbl...
> 2^31-1 (2,147,483,647)
> Keep in mind that all numbers are used, even if not allocated.
> So if someone begins a transaction, inserts 1000 rows and rolls it back,
> then 1000 IDENTITY values are "used" up.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
>
identity insert
assume a table called ABC and the xyz field is an identity...
SET IDENTITY_INSERT ABC on
insert into ABC (xyz, filed2, field3, ...)
select 14, 'A','B',...
SET IDENTITY_INSERT OFF
Wednesday, March 7, 2012
Identity Column values getting corrupted
I have bi-directional replication setup between SQL7 and SQL2000 and I have defined range for each publisher. Every thing has been working just fine and over time I have added several distribution agents for new tables for 2 way replication. In order to be sure both the databases get to do insert in their assigned range, I have a CHECK constraint with "NOT FOR REPLICTION" setup on each replicated table.
Just recently, I have encountered 2 tables for which the check constraint throws an error saying the application is trying to insert row that violates the check constraint. Upon doing DBCC CHECKIDENTI with NORESEED option, I find out that the identity column value is incorrect even though this column has the property "NOT FOR REPLICATION".
I have fixed the problem by running DBCC CHECKIDENT with RESEED option, but after a few days the identity column value gets corrupted again. Any ideas?
I don't think the application is doing insert with "SET IDENTITY INSERT" because a) the identity column is set with NOT FOR REPLICATION property and b)I have check constraints to keep the application from inserting in the wrong range.
Any help will be deeply appreciated. Thanks!
-A
What version of SQL Server 2000 are you running? In earlier versions there was a bug in the SQL engine such that under certain conditions the identity seed can be corrupted. This bug is fixed in SP3 and after.Identity Column Rollover
seed=1, increment=1
Does it automatically roll back to 1 when the number of records reaches
32,767. Assuming of course, the last record assigned identity 1 has been
removed.
I assume it does, but hey, I've assumed stuff like this before and gotten
burned.
JohnNo it does not automatically roll over. Instead you will receive an
overflow error and future inserts will fail. You could then reseed the
value using the DBCC CHECKIDENT command. A much better plan however, is
to create the column large enough that it won't overflow for the
forseeable future.
David Portas
SQL Server MVP
--|||No, you assume wrong. You'll get an error when you try to insert a new row.
"Arithmetic overflow error for data type smallint, value = 32768"
Change to Int if you believe that it may be a problem.
"John Manion" <JohnManion@.discussions.microsoft.com> wrote in message
news:785FF58A-2C96-4AAF-9E2A-30C918853DDE@.microsoft.com...
> If I have an Identity column defined as a smallint
> seed=1, increment=1
> Does it automatically roll back to 1 when the number of records reaches
> 32,767. Assuming of course, the last record assigned identity 1 has been
> removed.
> I assume it does, but hey, I've assumed stuff like this before and gotten
> burned.
> John|||No. SQL Server will try to increment the identity value and also give you an
error when trying to store the new value.
Example:
use northwind
go
create table t (
colA smallint not null identity
)
go
set identity_insert t on
go
insert into t (colA) values(32767)
go
set identity_insert t off
go
select * from t
go
insert into t default values
go
select * from t
go
drop table t
go
AMB
"John Manion" wrote:
> If I have an Identity column defined as a smallint
> seed=1, increment=1
> Does it automatically roll back to 1 when the number of records reaches
> 32,767. Assuming of course, the last record assigned identity 1 has been
> removed.
> I assume it does, but hey, I've assumed stuff like this before and gotten
> burned.
> John|||Not surprising.
Thanks,
John
"David Portas" wrote:
> No it does not automatically roll over. Instead you will receive an
> overflow error and future inserts will fail. You could then reseed the
> value using the DBCC CHECKIDENT command. A much better plan however, is
> to create the column large enough that it won't overflow for the
> forseeable future.
> --
> David Portas
> SQL Server MVP
> --
>
Friday, February 24, 2012
Identity Column
previously defined as YES...and now need to change it to Yes (Not For
Replication).
Thanks
There is no simple SQL command for that. You have to drop and recreate the
column. If you use Enterprise Manager, you can change the property and then
generate a script (third button from the left) which you can save, study and
run alter.
Jacco Schalkwijk
SQL Server MVP
"CH" <CH@.discussions.microsoft.com> wrote in message
news:5AD5D775-0125-471D-B9ED-EC8A6428099A@.microsoft.com...
> Would like to know how to use SQL 2000 command to modify a IDENTITY column
> previously defined as YES...and now need to change it to Yes (Not For
> Replication).
> Thanks
|||Thanks for your reply. I believe that there's a command to change the
identity. Thanks again.
"Jacco Schalkwijk" wrote:
> There is no simple SQL command for that. You have to drop and recreate the
> column. If you use Enterprise Manager, you can change the property and then
> generate a script (third button from the left) which you can save, study and
> run alter.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "CH" <CH@.discussions.microsoft.com> wrote in message
> news:5AD5D775-0125-471D-B9ED-EC8A6428099A@.microsoft.com...
>
>
Identity Column
previously defined as YES...and now need to change it to Yes (Not For
Replication).
ThanksThere is no simple SQL command for that. You have to drop and recreate the
column. If you use Enterprise Manager, you can change the property and then
generate a script (third button from the left) which you can save, study and
run alter.
Jacco Schalkwijk
SQL Server MVP
"CH" <CH@.discussions.microsoft.com> wrote in message
news:5AD5D775-0125-471D-B9ED-EC8A6428099A@.microsoft.com...
> Would like to know how to use SQL 2000 command to modify a IDENTITY column
> previously defined as YES...and now need to change it to Yes (Not For
> Replication).
> Thanks|||Thanks for your reply. I believe that there's a command to change the
identity. Thanks again.
"Jacco Schalkwijk" wrote:
> There is no simple SQL command for that. You have to drop and recreate the
> column. If you use Enterprise Manager, you can change the property and the
n
> generate a script (third button from the left) which you can save, study a
nd
> run alter.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "CH" <CH@.discussions.microsoft.com> wrote in message
> news:5AD5D775-0125-471D-B9ED-EC8A6428099A@.microsoft.com...
>
>
Sunday, February 19, 2012
IDENTITY and IMPORTING DATA FROM EXISTING DATABASE TO A NEW DB
I need to migrate the database which has identity fields defined to a new DB
which will also be having an identity field. For example table A which has
Field A as IDENTITY and this needs to be migrated to Table B which has Field
B as IDENTITY. The data in Table A has been starting from 1 to 1000 and when
I migrate the data I have a constraint that only certain data will be
migrated it may be from 1 to 100 and then 200 to 400 in that case, if Table
B
has idenity field defined then the data I am migrating will be having Field
B
from 1 to 300 which is not correct. So if someone can give suggestions of ho
w
I can accomplish this migration. I also dont want to change the way IDENTITY
Field is defined. I need to have 301 generated for the new row that gets
generated in Table B.
I hope I am making myself clear here. Any help would be greatly appreciated.
Thanks
JohnJohn
1) If the structure of both databases is identical why not use
BACKUP/RESTORE command?
2) Look at SET IDENTITY_INSERT in the BOL
"John @. X" <JohnX@.discussions.microsoft.com> wrote in message
news:8C370CDD-31EC-46AF-9C6F-13223AAAF8EA@.microsoft.com...
> Hi there,
> I need to migrate the database which has identity fields defined to a new
DB
> which will also be having an identity field. For example table A which has
> Field A as IDENTITY and this needs to be migrated to Table B which has
Field
> B as IDENTITY. The data in Table A has been starting from 1 to 1000 and
when
> I migrate the data I have a constraint that only certain data will be
> migrated it may be from 1 to 100 and then 200 to 400 in that case, if
Table B
> has idenity field defined then the data I am migrating will be having
Field B
> from 1 to 300 which is not correct. So if someone can give suggestions of
how
> I can accomplish this migration. I also dont want to change the way
IDENTITY
> Field is defined. I need to have 301 generated for the new row that gets
> generated in Table B.
> I hope I am making myself clear here. Any help would be greatly
appreciated.
> Thanks
> John|||Try setting IDENTITY_INSERT on for that table
See BOL:
http://msdn.microsoft.com/library/d... />
t_7zas.asp
-- Jesse
On Wed, 9 Feb 2005 21:53:01 -0800, "John @. X"
<JohnX@.discussions.microsoft.com> wrote:
>Hi there,
>I need to migrate the database which has identity fields defined to a new D
B
>which will also be having an identity field. For example table A which has
>Field A as IDENTITY and this needs to be migrated to Table B which has Fiel
d
>B as IDENTITY. The data in Table A has been starting from 1 to 1000 and whe
n
>I migrate the data I have a constraint that only certain data will be
>migrated it may be from 1 to 100 and then 200 to 400 in that case, if Table
B
>has idenity field defined then the data I am migrating will be having Field
B
>from 1 to 300 which is not correct. So if someone can give suggestions of h
ow
>I can accomplish this migration. I also dont want to change the way IDENTIT
Y
>Field is defined. I need to have 301 generated for the new row that gets
>generated in Table B.
>I hope I am making myself clear here. Any help would be greatly appreciated
.
>Thanks
>John|||Thank You for the reply.
But the issue is that BACKUP/RESTORE can pull all the data and make the
IDENTITY correct but my requirement is let us say only 100 records of 1000
data is getting migrated and that too is not in a sequence. In that case my
IDENITY field will be getting wacked and the data is not correctly imported
right.
Thanks
John
"Uri Dimant" wrote:
> John
> 1) If the structure of both databases is identical why not use
> BACKUP/RESTORE command?
> 2) Look at SET IDENTITY_INSERT in the BOL
>
>
> "John @. X" <JohnX@.discussions.microsoft.com> wrote in message
> news:8C370CDD-31EC-46AF-9C6F-13223AAAF8EA@.microsoft.com...
> DB
> Field
> when
> Table B
> Field B
> how
> IDENTITY
> appreciated.
>
>|||John
> IDENTITY correct but my requirement is let us say only 100 records of 1000
> data is getting migrated and that too is not in a sequence. In that case
my
> IDENITY field will be getting wacked and the data is not correctly
imported
> right.
After Restortion you have to manage the tables at new database. I think it
would be easier to delete/truncate (whatever) than manage to insert /dts
from another one. But it is my own opinion.
"John @. X" <JohnX@.discussions.microsoft.com> wrote in message
news:F676A343-50B4-49EA-B48D-EAFA0E1615CF@.microsoft.com...
> Thank You for the reply.
> But the issue is that BACKUP/RESTORE can pull all the data and make the
> IDENTITY correct but my requirement is let us say only 100 records of 1000
> data is getting migrated and that too is not in a sequence. In that case
my
> IDENITY field will be getting wacked and the data is not correctly
imported
> right.
> Thanks
> John
> "Uri Dimant" wrote:
>
new
has
and
of
gets