Inserts into table that has a Primary key/Unique constraint

We do a lot of inserts into a table that has a primary key on an identity
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>

Insertion With Explicit IDENTITY

Hi,
I'm writing a database interface to someone else's code and am getting passed INSERT statements that look something like this:

INSERT INTO my_table(my_id, my_val)
VALUES (%s, 5)

My code is expected to replace the %s with a suitable value for an ID. Now, my_table looks like this:

CREATE TABLE my_table (
my_id INT IDENTITY(1,1) PRIMARY KEY,
my_val INT
)

and I'm trying to get a suitable value for the '%s' such that the insert will work correctly. I understand that I need to 'SET IDENTITY_INSERT my_table ON' prior to the insert, but what can I replace '%s' with? I can't put a separate SELECT statement in there, and @.@.IDENTITY is too global to be useful (it's a multithreaded app with a lot of inserts across multiple tables). Hacking the input string to remove the 'my_id, ' and '%s, ' completely is not allowed (unfortunately).

I've tried NULL in the hope that SQLServer will work it out but it complains. I don't want to do a 'SELECT IDENT_CURRENT('my_table')' prior to the INSERT due to the overhead and potential concurrency problems. Is there some special keyword or similar I can put in here?

DB is SQLServer2000 SP2. Any help is greatly appreciated.

Cheers,
Jim.Sounds like you have an application that was designed to run against diffrent vendor databases OR someone has missed the design boat. I don't understand why you would define a table with an identity attribute AND have a requirment that you pass a value for the identity value.

To my knowledge there is no way to pass a place hold in the fashion you have described.|||Originally posted by Paul Young
Sounds like you have an application that was designed to run against diffrent vendor databases OR someone has missed the design boat. I don't understand why you would define a table with an identity attribute AND have a requirment that you pass a value for the identity value.

The reason is that the app talks to a number of different back-end databases and they all have different ways of doing an insert on a table with a unique ID. For instance, Postgres expects to have nextval('my_table_f_id_seq') or similar in place of the %s. I'm just stuck (really stuck, as it turns out) with coding to the supplied spec.

Thanks for the reply.|||SET IDENTITY_INSERT my_table ON

INSERT INTO my_table(my_id,my_val)
VALUES (IDENT_CURRENT('my_table') + 1, 5)

SET IDENTITY_INSERT my_table OFF

May want to replace the + 1 with call to function IDENT_INCR()

Tim S|||Tim, that works a treat. Thanks!

Insertion data via Stored Procedure [URGENT]!

Hi all;

Question:
=======
Q1) How can I insert a record into a table "Parent Table" and get its ID (its PK) (which is an Identity "Auto count" column) via one Stored Procedure??

Q2) How can I insert a record into a table "Child Table" whose (FK) is the (PK) of the "Parent Table"!! via another one Stored Procedure??

Example:
----
I have two tables "Customer" and "CustomerDetails"..

SP1: should insert all "Customer" data and return the value of an Identity column (I will use it later in SP2).

SP2: should insert all "CustomerDetials" data in the record whose ID (the returned value from SP1) is same as ID of the "Customer" table.

FYI:
--
MS SQL Server 2000
VS.NET EA 2003
Win XP SP1a
VB.NET/ASP.NET :)

Thanks in advanced!There are a couple of ways to get the last inserted IDENT, but I prefer to use IDENT_CURRENT('table_name') to get the ID from the last inserted record of thespecified table.

INSERT INTO Customer (val1,val2,etc) VALUES (@.Val1,@.val2,etc)
INSERT INTO CustomerDetails (ID,Val1,Val2, etc) VALUES ((IDENT_CURRENT('Customer'),@.val1,@.val2,etc)

|||Ok, what about if I want to do these process in two SPs??

I want to take the IDDENTITY value from the fitrst "INSERT INTO" statment, because I need this value in my source code as well as other Stored Procedure(s).

Thanks in advanced!|||Insert Blah...;
Return SCOPE_IDENTITY()|||Thanks gays.

I think my problem is how to get the returned value (IDDENTITY value) from the VB.NET code (in other words, how to extract it from VB.NET/ADO.NET code)?

Note:
--
VB.NET/ADO.NET
or
C#.NET/ADO.NET

Are are ok, if you would like to demonstrate your replay. ( I want the answer!).

Thanks again.|||YES!!

The problem was from my ADO.NET part.

Thanks for your help gays.

Inserting with DTS in IDENTITY table

Hi

I need to insert values from a text-file to a table with a primary key as identity. In the text file I have no idea of the primary key values and i get "foreign key constraint violation" when trying to import null values into the column.

How can I solve the problem? With ordinary insert-statement there is no problem since the table generates identity- key values automatically. Is there a possibility to generate identity values with DTS-import?

Bjrnyes.

you just need to not map anything to the primary key column. In the wizard, you need to hit the transform button and make and under the column mappings make sure ignore is selected. If you are using the DTS desginer, you need to get rid of the mapping on your transform data task under the transformations task.

Inserting Values to Identity column.

Hi all,

I have a Table Which only have one column, and that column is also an Identity column. Does anybody know how to insert values to this table. I know that if I change the table and add another column I can solve this problem. But because I can't change the of the Database design provided to me by my architect, I have to find a solution for this.

Please help me......

Regards,
Sandarenu

you could use the statement "SET IDENTITY_INSERT ON" before the insert statement.

you can read about the usage from books online

inserting values for alias names within tables?

hi every1

I have a database table in which one of the fields is an alias for the identity field. That is the alias field self references the table and takes its value for the "id" field of tht table
as follows:
system_id int
system_name varchar(20)
sys_alias int
sys_pro varchar(80)
sys_values varchar(80)
so here the sys_alias takes wtever value the system assigns to the id field, system_id while inserting values into the table..
now my problem is i have to insert records into ths table frm aother tables using insert into...select from statements bt since the id values r enerated by the comp during value insertion i dunno how to give values fr the alias?

e.g insert into my_table(system_name,sys_alias,sys_pro,sys_values)
select x,(how to give ths field),y,z
from another_table
where...

neone who understood my problem and can help puhllezz post me a reply asap!
thnx:)

shuchiSounds like you'd have to do this in a trigger.|||what is the expression for the "alias" column? is it exactly the same value, or is it something like a character prefix plus the value?

perhaps you might consider using a view instead

rudy|||hi...
its exactly the same value..jus like a copy column fr the identity column..
i tried using max(syb_identity) and @.@.identity functions but they do not work specially since i put them in a sub query so theyre not calculated recursivelye but just once and insert the same identity value for all other inserted records!
can anyone help me what sortof view i shud create?
i am doing ths whole process thru a perl script so whatever sql tht needs to be done is done thru an sql file run from my perl script. mebbe asome sorta trigger will work tht automatically inserts the newest value of identityt generated into the alias field while insertion of records?
gosh i really need help here...so any ideas wud be gr8:)
thnx fr all the suggestions:)

-shuchi|||if this "alias" column is to have exactly the same values, then you don't really need it

just select it twice in any query -- select system_id
, system_name
, system_id as sys_alias
, sys_pro
, sys_values
from yourtable

rudy|||thanx a ton fr all the help...managed it:)

-shuchi

Inserting value to Identity Column

I want all users in database to be able to call Set IDENTITY_INSERT
[table] = ON
However when a user who is not the dbo logs in to execute the stored
procedure I get the following error message.
prcIdentityFudgeSave, Line 8
The current user is not the database or object owner of table
'IdentityFudge'. Cannot perform SET operation.
Please Help
(See Stored Prc below that the users are trying to execute)
CREATE PROCEDURE prcIdentityFudgeSave
(
@.IdentityBeforeTrigger int
) AS
--Fudge the Identity Column
delete From IdentityFudge
SET IDENTITY_INSERT IdentityFudge ON
Insert Into IdentityFudge (IndentityFudgeID) values
(@.IdentityBeforeTrigger)
GO> I want all users in database to be able to call Set IDENTITY_INSERT
> [table] = ON
Then why are you using IDENTITY at all?|||give permission to the SQL Server login [SQL Server authentication] / Window
login [Windows Authentication] to acess the table and sp.
"dermot" <dfrench@.tommyfrench.co.uk> wrote in message
news:1110465514.012467.136020@.f14g2000cwb.googlegroups.com...
>I want all users in database to be able to call Set IDENTITY_INSERT
> [table] = ON
> However when a user who is not the dbo logs in to execute the stored
> procedure I get the following error message.
> prcIdentityFudgeSave, Line 8
> The current user is not the database or object owner of table
> 'IdentityFudge'. Cannot perform SET operation.
> Please Help
> (See Stored Prc below that the users are trying to execute)
>
> CREATE PROCEDURE prcIdentityFudgeSave
> (
> @.IdentityBeforeTrigger int
> ) AS
> --Fudge the Identity Column
> delete From IdentityFudge
> SET IDENTITY_INSERT IdentityFudge ON
> Insert Into IdentityFudge (IndentityFudgeID) values
> (@.IdentityBeforeTrigger)
> GO
>|||Joel,
I think that is not enough. See "SET IDENTITY_INSERT (Permissions)" in BOL.
AMB
"Joel Leong" wrote:

> give permission to the SQL Server login [SQL Server authentication] / Wind
ow
> login [Windows Authentication] to acess the table and sp.
> "dermot" <dfrench@.tommyfrench.co.uk> wrote in message
> news:1110465514.012467.136020@.f14g2000cwb.googlegroups.com...
>
>|||If the value in this column has meaning to your users then don't use
IDENTITY. Why would you want to do this?
David Portas
SQL Server MVP
--

Inserting to multiple tables in SQL Server 2005 that use identity specification

Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
I have four tables, an

Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
and a linking table for all of them... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)

My problem is that I can insert data to each specific table by itself using seperate insert statements....eg...

//insert an attendance record to the attendance table
string userID;

userID = Session["User"].ToString();

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@.MembershipNo, @.Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}

//insert an aerobics record into the aerocibs table

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@.MachineID, @.Intensity, @.ExerciseTime)";

pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}
//same code as above for the resistance table

However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
I have read several forums where they recommend using something called @.@.IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
Any help would be so much appreciated... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#
Cheers
ScottyI think the problem is in your design. I'm thinking you should dump the ExerciseMaster table.

If you have a one-to-one relationship between attendance, Resistance, and Aerobics, then a single table will do. If you have a one-to-many relationship (for a given attendance, there may be zero or more resistance sessions and zero or more aerobics sessions) then this schema should work for you:

1. Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
2. Resistance Table (ResistId(PK Identity specific), AttendanceID(FK), Weight , Reps, Sets)
3. Aerobics Table(AerobicsID(PK Identity specific), AttendanceID(FK), MachineID, Intensity, Time)

You can insert the attendance record and then either recover the ID created using @.@.Identity or switch to GUID identifiers and let your interface create the ID. Then use that ID when you insert the Resistance and Aerobics records.|||Thanks Blindman, I see where you are coming from about the tables and I agree with what you have suggested. However, i am still a bit lost as to what the actual code would be to use the @.@.IDENTITY...
eg.
Suppose i have changed my database design to reflect your suggestions and want to enter code to the Aerobics table, how do i use the attendanceID that is used in the attendance table and enter it into the attendanceID in the Aerobics table..I have no idea how or where to use the @.@.IDENTITY in my code...

//insert an attendance record to the attendance table
string userID;

userID = Session["User"].ToString();

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@.MembershipNo, @.Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}

//insert an aerobics record into the aerocibs table

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([AttendanceID], [/COLOR][MachineID], [Intensity], [ExerciseTime]) VALUES (@.MachineID, @.Intensity, @.ExerciseTime)";

pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}|||Well, the other thing you need to do is to stop issuing dynamic SQL code from your application. It is more complicated, less efficient, and insecure.

Write a store procedure called "InsertAttendance" that takes MembershipNo and Date as parameters. The stored procedure will insert the new record (after checking to make sure it does not already exist), and then call @.@.Identity (better yet, use @.@.SCOPEIDENTITY...) to get the ID that was just created. The sproc can pass the new ID back as an output parameter.

Or, as I said, you can switch to using GUIDs rather than identity integers as your primary key. Then you can actually create the new GUID within your application code, and you never have to query the database to find out what ID was assigned.|||is there any chance you could provide some sample code as to how i would call the @.@.IDENTITY, where i would call it from and how i would use it to actually write the information to another table ? im a bit lost as im very new to all this...
Thanks|||set nocount on
--Create a dummy table
create table SampleTable (IdentityColumn int identity, DummyData varchar(50))
go

--Create a dummy sproc
create procedure InsertSample (@.DummyValue varchar(50), @.NewIdentity int output)
as
begin
insert into SampleTable (DummyData) values(@.DummyValue)
set @.NewIdentity = Scope_Identity()
end
go

--Run some insert statements and check the ID values retrieved
declare @.NewID int
exec InsertSample 'Fubar1', @.NewIdentity = @.NewID output
select @.NewID
exec InsertSample 'Fubar2', @.NewIdentity = @.NewID output
select @.NewID
exec InsertSample 'Fubar3', @.NewIdentity = @.NewID output
select @.NewID

--Cleanup
drop procedure InsertSample
drop table SampleTable
go|||Thanks for that, Have that working but only when i execute the stored procedure from the database. I am still unable to actually use it unfortunatley within the application...

string userID;
const int macId = 3;

userID = Session["User"].ToString();

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();
pgpDataSource.SelectCommandType = SqlDataSourceCommandType.Text;

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
pgpDataSource.InsertCommand = "InsertAttendance";

pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);
//I think this may be the problem, both here and again below where
// i have the line of code commented out again...
//pgpDataSource.InsertParameters.Add("AttendanceID", newID.ToString());
pgpDataSource.InsertParameters.Add("MachineID", macId.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text );
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);
//pgpDataSource.InsertParameters.Add("AttendanceID", newID.ToString());
pgpDataSource.InsertParameters.Add("Reps", txtReps.Text);
pgpDataSource.InsertParameters.Add("Sets", txtSets.Text);

int RowsAffected = 0;

try
{
RowsAffected = pgpDataSource.Insert();
}

catch (Exception ex)
{
Server.Transfer("~/Problem.aspx");
}

finally
{
pgpDataSource = null;
}

if (RowsAffected != 1)
{
Response.Redirect("~/Problem.aspx");
}
else
{
Response.Redirect("~/Log.aspx");
}
}

This is the storedProcedure i created to fill all three tables which works but not when i try to use it in the application...

ALTER PROCEDURE InsertAttendance

@.NewIdentity int output,
@.MembershipNo smallint,
@.Date datetime,
@.Reps smallint,
@.Sets smallint,
@.Weight smallint,
@.MachineID smallint,
@.Intensity smallint,
@.ExerciseTime int

AS

BEGIN

INSERT Attendance (MembershipNo, Date)
Values (@.MembershipNo, @.Date)

INSERT Aerobics (AttendanceID, MachineID, Intensity, ExerciseTime)
Values (@.NewIdentity, @.MachineID, @.Intensity, @.ExerciseTime)

INSERT Resistance ( AttendanceID, Reps, Sets, Weight)
VALUES (@.NewIdentity, @.Reps, @.Sets, @.Weight)
Set @.NewIdentity = Scope_Identity()

END|||How you call this stored procedure from your application code depends upon your programming platform. I don't code interfaces, so I can't help you with that. But I'd have a hard time believing that any respectable programming language would not have a method of calling a stored procedure and retrieving an output parameter.
I suggest you ask about this on a forum specific to your development platform.|||Are you sure this works properly? I would have thought that you would have to set the value of @.NewIdentity before you used it in the Insert statements for the Aerobics and Resistance tables.

[/QUOTE]

INSERT Attendance (MembershipNo, Date)
Values (@.MembershipNo, @.Date)

INSERT Aerobics (AttendanceID, MachineID, Intensity, ExerciseTime)
Values (@.NewIdentity, @.MachineID, @.Intensity, @.ExerciseTime)

INSERT Resistance ( AttendanceID, Reps, Sets, Weight)
VALUES (@.NewIdentity, @.Reps, @.Sets, @.Weight)
Set @.NewIdentity = Scope_Identity()

END[/QUOTE]

Inserting same identity value into two linked tables

Hi,
I have two tables which has 2 linked fields, as David Portas posted in
'Field value determines whether there is extra info that should be held
about that record':
CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type
INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many
different types you have */), UNIQUE (event_no, event_type) /* ...
other columns for events */) ;
CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY,
event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN
KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON
DELETE CASCADE /* ... other columns for event type 1 */) ;
I've set the event_no identity property in table 'events' to true. I have a
stored procedure to insert data to both tables at once (using 2 INSERT INTO
statements coming one after another).
The problem is that currently I'm using ADO to generate the new ID (By
selecting MAX of the event_no, adding 1 to the MAX value, and using the
result as the new event_no) which is sent as a parameter to the stored
procedure, which uses it in the 2 INSERT INTO statements.
This method is working fine by now, but could potentially cause problems.
How can generate a new value for that event_no identity column, then use it
in both the INSERT INTO statements?
I've read about @.@.IDENTITY, but I'm not sure about how to use it. I've also
read that there are better options, but these are not relevant since I'm
using SQL Server 7.
Kind Regards,
Amir.Amir wrote:
> Hi,
> I have two tables which has 2 linked fields, as David Portas posted in
> 'Field value determines whether there is extra info that should be held
> about that record':
> CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type
> INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many
> different types you have */), UNIQUE (event_no, event_type) /* ...
> other columns for events */) ;
> CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY,
> event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN
> KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON
> DELETE CASCADE /* ... other columns for event type 1 */) ;
> I've set the event_no identity property in table 'events' to true. I have
a
> stored procedure to insert data to both tables at once (using 2 INSERT INT
O
> statements coming one after another).
> The problem is that currently I'm using ADO to generate the new ID (By
> selecting MAX of the event_no, adding 1 to the MAX value, and using the
> result as the new event_no) which is sent as a parameter to the stored
> procedure, which uses it in the 2 INSERT INTO statements.
> This method is working fine by now, but could potentially cause problems.
> How can generate a new value for that event_no identity column, then use i
t
> in both the INSERT INTO statements?
> I've read about @.@.IDENTITY, but I'm not sure about how to use it. I've als
o
> read that there are better options, but these are not relevant since I'm
> using SQL Server 7.
> Kind Regards,
> Amir.
Use @.@.IDENTITY to retrieve the last inserted IDENTITY value. Like this
for example:
CREATE TABLE events (event_no INTEGER IDENTITY NOT NULL PRIMARY KEY,
event_name VARCHAR(10) NOT NULL UNIQUE, event_type INTEGER NOT NULL
CHECK (event_type BETWEEN 1 AND 10), UNIQUE (event_no, event_type) ) ;
INSERT INTO events (event_name, event_type)
VALUES ('foo',1);
SELECT @.@.IDENTITY AS last_identity_value ;
David Portas
SQL Server MVP
--|||Thanks, David!
Kind Regards,
Amir.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1137159522.649050.309090@.g47g2000cwa.googlegroups.com...
> Amir wrote:
> Use @.@.IDENTITY to retrieve the last inserted IDENTITY value. Like this
> for example:
> CREATE TABLE events (event_no INTEGER IDENTITY NOT NULL PRIMARY KEY,
> event_name VARCHAR(10) NOT NULL UNIQUE, event_type INTEGER NOT NULL
> CHECK (event_type BETWEEN 1 AND 10), UNIQUE (event_no, event_type) ) ;
> INSERT INTO events (event_name, event_type)
> VALUES ('foo',1);
> SELECT @.@.IDENTITY AS last_identity_value ;
> --
> David Portas
> SQL Server MVP
> --
>

Inserting records with value outside replication identity ranges

I have a live system that uses merge replication. We are upgrading the
system by setting up a whole new set of servers. When we go live on the new
servers, I would like to copy into the new server database, any changes that
have been made to the old databases. Unfortunately, replication adds check
constraints to identity columns starting with the last identity value in the
database. The old system is still adding id values below the maximum id
(e.g. each region is adding values within their own range which is below the
maximum id in the table.)
I can't just copy the entire database and then setup replication because the
snapshot would take way too long to get to one of the subscribers and we need
to be able to switchover relatively quickly.
Since SQL Server does not let me modify or drop the identity column check
constraint that replication added, how can I add these identity values after
replication has already been set up on the new system?
There could be other issues though. What if the subscriber record is updated
rather than inserted - how will you know which updated rows to take? I think
the simplest way is to sync all subscribers prior to migrating the production
database to the new server. Then using this as the master, set up replication
from the new publisher.
Paul Ibison
|||During the cutover, we will stop all changes to the old database, wait for
replication to sync up, then copy changes from the old publisher over to the
new publisher. then get everyone connected to the new servers.
One of our subscribers is in China where network connectivity is slow and
unreliable. I have tried getting a snapshot with all the data to this
subscriber and it just takes too long - days if it ever succeeds, which so
far it has not. I have to get the snapshot to China with some of the tables
empty, then add the data after.
"Paul Ibison" wrote:

> There could be other issues though. What if the subscriber record is updated
> rather than inserted - how will you know which updated rows to take? I think
> the simplest way is to sync all subscribers prior to migrating the production
> database to the new server. Then using this as the master, set up replication
> from the new publisher.
> Paul Ibison

Inserting Problem

I have a table_variable with some data now i need to INSERT it to the
table in database this table has identity column.
How can i get the all identities ?
Message posted via http://www.webservertalk.comINSERT into some table (Col1,col2,col3)
SELECT * from #TempTable
That would be the solution if your tablevariable doesnt contain the
estimated IDs (whereas the column list Col1col2 doenst contain the identity
column)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"ALK via webservertalk.com" <forum@.nospam.webservertalk.com> schrieb im
Newsbeitrag news:b21535e06dbb4ad697a79231b66b0487@.SQ
webservertalk.com...
>I have a table_variable with some data now i need to INSERT it to the
> table in database this table has identity column.
> How can i get the all identities ?
> --
> Message posted via http://www.webservertalk.com|||What do you want to do with the IDENTITY values when you've got them?
Assuming you just want to return a result, you can do something like
the following. Use an alternate key of the table:
INSERT INTO YourTable (key_col, x, y, z)
SELECT key_col, x, y, z
FROM @.table_var
SELECT T.id /* IDENTITY key */
FROM YourTable AS T
JOIN @.table_var AS V
ON T.key_col = V.key_col /* Alternate key */
You must always have an alternate key because IDENTITY should never be
the only key of a table.
David Portas
SQL Server MVP
--|||Thanks, exactly
Message posted via http://www.webservertalk.com