How to insert records into two tables that references each other?

I have two tables , A1 and B1 . B1 has a reference key pointing to A1.ID which is a primary key. Now I want to INSERT a record into both table.

CREATE TABLE a1 (
T_ID INTEGER IDENTITY(1,1) PRIMARY KEY,
T_Test INTEGER NOT NULL,
);

CREATE TABLE B1 (
B_ID INTEGER IDENTITY(1,1) PRIMARY KEY,
T_SID INTEGER NOT NULL REFERENCES a1(T_ID),
);

-- A function to get the next primary key value

-- Get the next unique key from A1
CREATE FUNCTION [dbo].[getTicketNo]
(
)
RETURNS INT
AS
BEGIN
RETURN (SELECT Max(T_ID) from A1)
END

I can only insert a record into a table at a time. and I rely on dbo.getTicketNo() to get the lastest T_ID

I wonder how do you insert a new record into A1 and B1 and maintain referential integrity? You must call Insert twice to accomplish the job right?

What I want to insert a record into B1.SID is the primary key value (T_ID) that was created during "Insert into A1 values (0)", using dbo.getTicketNo()
I worry if I am going to insert a record into A1 first, then insert a record into B1 next, there is a chance the data wont be consistent; as I illustrated a situation below:

t A1 B1
0 (0)Insert
1 (1)Insert
2 (0)Insert
3 (1)Insert

dbo.getTicketNo() at t3 may return the wrong value if another process (1)insert is executed, that will change the primary key number to 2 which is incorrect

Can I lock up A1 and then unlock A1 after I finished working with B1?

If so, what would happen to the application if it tries to access into A1 during it is locked?

If you ask me this not a good approach to get the identity value.
You can use the following objects to get the last inserted identity value.

@.@.Identity or Scope_Identity()

1.Insert the data on Master table (A1)
2.Then get the last inserted value from those system functions or global varibale
3.Use the values on Child table (B1)

Example,

Insert into A1(T_Test) values (@.T_Test);
Insert into B1(T_SID) values (@.@.Identity)
--or
Insert into B1(T_SID) values (Scope_Identity())

NOTE: Don't use MAX function to get the last inserted identity value. It may be
Inserted by other concurrent users.

|||

Thank you ManiD

From your code, is there any chance that another INSERT query is executed by other concurrent users after the 1st Insert and before the 2nd Insert in your case?

time 0:00:00 (User 0) Insert into A1(T_Test) values (@.T_Test);
time 0:00:20 (User 1) Insert into A1(T_Test) values (@.T_Test);
time 0:00:21 (User 0) Insert into B1(T_SID) values (@.@.Identity)

If it is possible, what is the best approach to ensure that the second Insert into B1 is consistent to the master A1?

Thanks for your precious time

|||

In this case,

@.@.Identity hold the User1 Identity data..

So it always good idea to use SCOPE_IDENTITY() instead of using @.@.Identity..

SCOPE_IDENTITY always hold the current user's identity data...

|||

Ok,,, thank you ManiD

Let me do some research on SCOPE_Indentity() ...

Thank you for your kind replies

|||

When I google it, found some people mispelled SCOPE_IDENTITY() to SCOPE_INDENTITY() .....

Here is the MSDN for SCOPE_IDENTITY() if anyone got the similar issues can have a look

http://msdn2.microsoft.com/en-us/library/ms190315.aspx

How to insert primary keys without using identity

I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?

thanks for your time,

AndreIt is possible with cursors, but would be awkward and I expect subject to problems if the table was having lots of records added by lots of users at the same time...

Why do you not want to use Autoincrement?

How To Insert into a table with a uniqueidentifier as primary key?

I would like to insert into a table with a primary key that has a uniqueidentifier. I would like it to go up by one each time I execute this insert statement. It would be used as my ReportId

My VB code is this.

ProtectedSub btncreate_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btncreate.Click'set connection stringDim errstrAsString =""Dim conn =New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")'set parameters for SPDim cmdcommand =New SqlCommand("sprocInsertNewReport", conn)

cmdcommand.commandtype = CommandType.StoredProcedure

cmdcommand.parameters.add(

"@.UserName", Session("UserName"))

cmdcommand.parameters.add(

"@.Week", vbNull)

cmdcommand.parameters.add(

"@.Date", vbDate)

cmdcommand.parameters.add(

"@.StartTime", vbNull)

cmdcommand.parameters.add(

"@.EndTime", vbNull)

cmdcommand.parameters.add(

"@.HeatTicket", vbNull)

cmdcommand.parameters.add(

"@.Description", vbNull)

cmdcommand.parameters.add(

"@.TakenAs", vbNull)

cmdcommand.parameters.add(

"@.Dinner", vbNull)

cmdcommand.parameters.add(

"@.Hours", vbNull)

cmdcommand.parameters.add(

"@.Rate", vbNull)

cmdcommand.parameters.add(

"@.PayPeriod", vbNull)

cmdcommand.parameters.add(

"@.LastSave", vbNull)

cmdcommand.parameters.add(

"@.Submitted", vbNull)

cmdcommand.parameters.add(

"@.Approved", vbNull)

cmdcommand.parameters.add(

"@.PagerDays", vbNull)

cmdcommand.parameters.add(

"@.ReportEnd", vbNull)Try'open connection here

conn.Open()

'Execute stored proc

cmdcommand.ExecuteNonQuery()

Catch exAs Exception

errstr =

""'An exception occured during processing.'Print message to log file.

errstr =

"Exception: " & ex.MessageFinally'close the connection immediately

conn.Close()

EndTryIf errstr =""Then

Server.Transfer(

"TimeSheetEntry.aspx")EndIf

My SP looks like this

ALTER PROCEDURE

sprocInsertNewReport

@.UserNamenvarchar(256),

@.Week

Int,

@.Date

Datetime,

@.StartTime

Datetime,

@.EndTime

DateTime,

@.HeatTicket

int,

@.Description

nvarchar(max),

@.TakenAs

nchar(10),

@.Dinner

Nchar(10),

@.Hours

Float,

@.Rate

Float,

@.PayPeriod

int,

@.LastSave

Datetime,

@.Submitted

Datetime,

@.Approved

DateTime,

@.PagerDays

int,

@.ReportEnd

DateTimeASINSERT INTO

ReportDetails

(

rpUserName,

rpWeek,

rpDate,

rpStartTime,

rpEndTime,

rpHeatTicket,

rpTicketDescription,

rpTakenAs,

rpDinnerPremium,

rpHours,

rpRate,

rpPayPeriod,

rpLastSaveDate,

rpSubmittedDate,

rpApprovedDate,

rpPagerDays,

rpReportDueDate

)

VALUES

(

@.Username,

@.Week,

@.Date,

@.StartTime,

@.EndTime,

@.HeatTicket,

@.Description,

@.TakenAs,

@.Dinner,

@.Hours,

@.Rate,

@.PayPeriod,

@.LastSave,

@.Submitted,

@.Approved,

@.PagerDays,

@.ReportEnd

)

RETURN

Any Ideas?

thx!

I am not sure what you mean by "go up one each time." Uniqueidentifiers don't work that way. You have three choices:

1 - generate a guid on the front end and pass it to the proc as the id

2 - Generate the guid within the proc

3 - set the default value for the key field to newid()

|||Why not use an identity column?|||

mpswaim:

Why not use an identity column?

Good question because a GUID is 16bytes binary data type to be used with care, while IDENTITY is INT and a property to the column.

|||

I am using VS2005 Server Explorer, is there an option to set and say int to an identity?

I do not see any ways to do so

|||

I changed it to an int and found the as Identity property and it works!

Thanks for the help!

|||

You can do it with SQL Management Studio (I believe that there's a free version available), or Enterprise Manager. Unfortunately I don't see a good way to alter a table to make an existing column an identity column.

|||You just do ANSI SQL ALTER table SET IDENTITY because IDENTITY is defined in ANSI SQL. Hope this helps.

How to insert a row with lowest possible key?

Hi, this seems to be an easy question but I can't find a quick answer to it.

I have an asp page that communicates with a database and I want the following example to work...

Insert 5 records into the DB with primary keys 1-5.

Remove record with key number 2.

Now, if I insert a new record I want it to take the smallest available key number (in this case number 2). This will save space in the database and keep the key numbers from increasing forever.

Does auto increment work like this?

If not, how do I do it?

Thanks for any replies!

Niklas

Auto-increment doesn't work like this.

I'm not sure that you reasons for doing this are sound. I don't think it'll save space really. An INT is an INT so you will only be storing 4bytes no matter what the size of the number. And while they will increase forever, its got to be a pretty big system to hit the limit of 2,147,483,647!!

However, i guess you could do it, but you'll need to run a check like SELECT MIN(Value) FROM Table and then subtracting 1 from that to get your value before running your INSERT statement.

|||

Auto numbers won't fill the gap. You have to write your own logic for this. It might degrade your query performance.

Because,

1. You need to find if there is any gap in existing data

2. You have to find the minimum gap to fill

3. If you already use the auto increment on the current column you have to disable it to fill the gap.

4. If step-1 & step-2 fails then you have to continue with auto increment

Finally,

5. If the concurrent users (let say 2 users) try to insert same id, one will be pass. Another user's transaction

will fail, you have to take care this failure and again you have to find the gap from step1. If n users try to

access this logic the Nth users may need to spend more time to insert his record.

Do you want to continue still?

|||This looks really strange, but it's working for me. The query in red is where the magic happens; everything else is just creating a test environment (run the whole thing in AdventureWorks/Northwind/pubs to see it in action).

Code Snippet

--Make a test table
IF object_id('junk') IS NOT NULL
DROP TABLE junk
GO
CREATE TABLE junk (
id int identity(1, 1),
data char(4)
)
GO

--Put in some test data
SET NOCOUNT ON
DECLARE @.i int
SET @.i = 1
WHILE @.i <= 100
BEGIN
INSERT INTO junk (data) VALUES (@.i)
SET @.i = @.i + 1
END
SET NOCOUNT OFF
GO

--Remove some records
DELETE FROM junk WHERE id IN (4, 5, 56, 17, 9, 82)
GO

--Find the lowest available number
SELECT MIN(j1.id) + 1 AS [next]
FROM junk j1
LEFT OUTER JOIN junk j2
ON j1.id + 1 = j2.id
WHERE j2.id IS NULL

|||Another issue is that if your database does not have cascading deletes, the new records may have foreign key usage in other tables that is not intended for the current record.sql

How to insert a primary key??

Resolved - thank you.

If you are in SQL Server 2005 you are in luck just do ALTER TABLE SET IDENTITY, I think you should know IDENTITY is not a field but property of the field. Now if you are in SQL Server 2000 you have to do it manually through Enterprise Manager and script it and execute it if needed. The code in the link below is for SQL Server 2005 try it if you are in 2000 it may work. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms190273.aspx

how to insert a identity key through store procudure?

I want to insert 3 values to sql database through store procudure

-- a store procudure is like this--

ALTER PROCEDURE dbo.FreeExperience

@.identity ,

@.name nvarchar(20),
@.gender int,

AS
begin
Insert into list(cid,name,gender)
values (@.identity,@.name,@.gender)

end
RETURN

------------------

the cid is a identity value . it will +1 automatically when user insert a new data..

if i ingore this column in store procudure it will cause error (becuase this column is not allow null)

please help. thanks

Try

ALTER

PROCEDURE dbo.FreeExperience
@.namenvarchar(20),
@.genderint,
AS
INSERTINTO list(name,gender)values(@.name,@.gender)
RETURN
GO

or

CREATE

PROCEDURE dbo.FreeExperience2
@.identityINT,
@.namenvarchar(20),
@.genderint,
AS
SETIDENTITY_INSERT listON-- Allows Identity value to be supplied
Insertinto list(cid,name,gender)values(@.identity,@.name,@.gender)
SETIDENTITY_INSERT listOFF
RETURN|||

if you cid field is setup as identity in you table (cid int identity(1,1))

insert statement like this should work without any errors

Insert into list(name,gender)
values (@.name,@.gender)

and identity value will be automatically set by SQL server

|||

I still got error message...

exceptiona information: System.Data.Sqlclien.SqlException: procedure or parameters 'FreeExperience' must have a parameters'@.identity', but didn't provide..

p.s FreeExperience is my store procedure name..

|||

If you want SQL Sever to automatically add the Identity value for you, you need to remove the column entirely from the proc, and your INSERT statement. If you want to manually supply the value then you use the SET IDENTITY_INSERT <Table> ON clause, and provide the value you want to be used instead. IF the column is a Primary Key for the table, you also need to make sure the value you are providing does not already exist.

|||

thank you... but I am not sure if I really understand what did you mean

can you please give me a simple example for this ?

this store procudure is for a program which will insert new colum to table

1 column named " cid" in table which is set as a identity column will automatically +1 and insert to cid

|||

Use this stored procedure
alter PROCEDURE dbo.FreeExperience
@.name nvarchar(20),
@.gender int,
AS
Insert into list(name,gender) values (@.name,@.gender)
RETURN

|||

this stored procedure will cause error

because the cid column is not allow to be null...

|||

>>this stored procedure will cause error because the cid column is not allow to be null...

Identity columns are populated automatically by the database so the stored procedure will work!

|||

thank you .. but it really doesn't works..

it continue showing the error message...

can't insert NULL to 'cid，table'list'; not allow Null。INSERT fail..

I checked all column in table list from database, all column allow null except column cid.

the statement will work only if I set the cid column as accept null ..

please help..

|||

Modify ur table by setting the "IdentityIncrement=1, IdentitySeed=1" of column cId, then remove cId from ur storedprocedure

HTH

|||

Modify ur table by setting the "IsIdentity=Yes, IdentityIncrement=1, IdentitySeed=1" of column cId, then remove cId from ur storedprocedure

HTH

how to insert identity values

hi to the group,
i am small problem,
i am having two columns 1 is col1 which is a primary key and col2 any think .now i want to insert the data into second column at that time the first column must get the values in identity (like 1,2,3,4 etc)
with out using identity(sql server)/generated always(db2)
can any one knows please explain itDidn't I answer this one already?

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx|||Didn't I answer this one already?

http://weblogs.sqlteam.com/brettk/archive/2004/06/29/1687.aspx
OMFG brett, are you kidding?

you seriously expect people to remember everything you wrote up to several years ago on a completely different site from this one?

i don't know, man, did you answer it already?

sheesh|||jagadish, look up the SET IDENTITY INSERT option in Books Online.

How to increment record id in an insert stored procedure

Hello everyone,
I have a problem. I need to insert records from table2 to table1. In
table1 there is a record id which serves as the key of the table. Each time
a record needs to be inserted, a record id is created. This record id is th
e
max(recordid)+1. I wrote the following stored procedure to insert records.
This stored procedure will insert the ten records from table2 into table1.
The only problem is that the recordcount is same for all ten records, instea
d
of incrementing. Should I store the table id in a different table? Any
response will be greatly appreciated.
Begin Transaction
(Select @.recordcount = (max(record)+1) from [dbo].[table1])
Insert into [dbo].[table1] (recordnum, field1, field2, field3, field4,
field5, field6)
Select @.recordcount, field1, field2, field3, field4, field5, field6 from
[dbo].[table2]
select @.errnum=@.@.Error, @.RowCount = @.@.RowCount
if @.errnum <> 0 GOTO sqlerror
Commit TransactionLook up IDENTITY in Books Online, that does all the work for you.
Jacco Schalkwijk
SQL Server MVP
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:765A9768-316A-4446-82A6-58737F5040CB@.microsoft.com...
> Hello everyone,
> I have a problem. I need to insert records from table2 to table1. In
> table1 there is a record id which serves as the key of the table. Each
> time
> a record needs to be inserted, a record id is created. This record id is
> the
> max(recordid)+1. I wrote the following stored procedure to insert records.
> This stored procedure will insert the ten records from table2 into table1.
> The only problem is that the recordcount is same for all ten records,
> instead
> of incrementing. Should I store the table id in a different table? Any
> response will be greatly appreciated.
> Begin Transaction
> (Select @.recordcount = (max(record)+1) from [dbo].[table1])
> Insert into [dbo].[table1] (recordnum, field1, field2, field3, field4,
> field5, field6)
> Select @.recordcount, field1, field2, field3, field4, field5, field6 from
> [dbo].[table2]
> select @.errnum=@.@.Error, @.RowCount = @.@.RowCount
> if @.errnum <> 0 GOTO sqlerror
> Commit Transaction
>

How to increment ID sequentially?

I'm using SQL Server 2000 with MS Management Studio. I have a table that has 400 rows. I have setup the Primary key to increment automatically. How do I setup so that the next record starts at 4001 and up? Right now it starts at 1274, for example. So besides dropping the table and re-creating it, how do reset it so it counts from sequentially from the last row inserted?

Look up DBCCCHECKIDENT. You can use it to view and / or update the identity seed value.

|||Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx|||

Hi mychucky,

You would have to use 'identity seed' property in your table design window in your SQL Server.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||Thanks! I'll give it some reading. It's complicated then I thought. I thought there is a button in SQL Management Studio where I can just click and all is reset.|||

ZLA:

Here's the link:http://msdn2.microsoft.com/en-us/library/ms176057.aspx

Thanks so much! I got it working now. I would never have found the solution if without your link.

How to import a table with primary and triggers

Hi,
When I import a table, it only imports the data without the primary key
and triggers. How to import a table with primary key and triggers?
Thanks
kaiUse Bulk Insert with the following options. Please see BOL for more
details.
CHECK_CONSTRAINTS
Specifies that any constraints on table_name are checked during the
bulk copy operation. By default, constraints are ignored.
FIRE_TRIGGERS
Specifies that any insert triggers defined on the destination table
will execute during the bulk copy operation. If FIRE_TRIGGERS is not
specified, no insert triggers will execute.
Mel|||Thanks Mel
Kai
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1144335965.378137.78020@.v46g2000cwv.googlegroups.com...
> Use Bulk Insert with the following options. Please see BOL for more
> details.
> CHECK_CONSTRAINTS
> Specifies that any constraints on table_name are checked during the
> bulk copy operation. By default, constraints are ignored.
> FIRE_TRIGGERS
> Specifies that any insert triggers defined on the destination table
> will execute during the bulk copy operation. If FIRE_TRIGGERS is not
> specified, no insert triggers will execute.
> Mel
>

How to import a table with primary and triggers

Hi,
When I import a table, it only imports the data without the primary key
and triggers. How to import a table with primary key and triggers?
Thanks
kaiUse Bulk Insert with the following options. Please see BOL for more
details.
CHECK_CONSTRAINTS
Specifies that any constraints on table_name are checked during the
bulk copy operation. By default, constraints are ignored.
FIRE_TRIGGERS
Specifies that any insert triggers defined on the destination table
will execute during the bulk copy operation. If FIRE_TRIGGERS is not
specified, no insert triggers will execute.
Mel|||Thanks Mel
Kai
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1144335965.378137.78020@.v46g2000cwv.googlegroups.com...
> Use Bulk Insert with the following options. Please see BOL for more
> details.
> CHECK_CONSTRAINTS
> Specifies that any constraints on table_name are checked during the
> bulk copy operation. By default, constraints are ignored.
> FIRE_TRIGGERS
> Specifies that any insert triggers defined on the destination table
> will execute during the bulk copy operation. If FIRE_TRIGGERS is not
> specified, no insert triggers will execute.
> Mel
>

How to import a table with primary and triggers

Hi,
When I import a table, it only imports the data without the primary key
and triggers. How to import a table with primary key and triggers?
Thanks
kai
Use Bulk Insert with the following options. Please see BOL for more
details.
CHECK_CONSTRAINTS
Specifies that any constraints on table_name are checked during the
bulk copy operation. By default, constraints are ignored.
FIRE_TRIGGERS
Specifies that any insert triggers defined on the destination table
will execute during the bulk copy operation. If FIRE_TRIGGERS is not
specified, no insert triggers will execute.
Mel
|||Thanks Mel
Kai
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1144335965.378137.78020@.v46g2000cwv.googlegro ups.com...
> Use Bulk Insert with the following options. Please see BOL for more
> details.
> CHECK_CONSTRAINTS
> Specifies that any constraints on table_name are checked during the
> bulk copy operation. By default, constraints are ignored.
> FIRE_TRIGGERS
> Specifies that any insert triggers defined on the destination table
> will execute during the bulk copy operation. If FIRE_TRIGGERS is not
> specified, no insert triggers will execute.
> Mel
>

how to implement unique key on multiple field

hello guys,

I have one table which is using to keep 10 difference type of serial number (that mean i got 10 column in the table). Is there any way to do unique key checking (individually, not combine) on these 10 serial number without sacrify the performance?

thank you.

Sure, i you wnt to check them only per column you can put a UNIQUE Constraint on those columns, that will check like the primary key for uniqueness.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

How to implement a foreign key

Hello,

I am facing a problem, I have a table and the primary key of this table is a foreign key of another table. I want to insert data into the mother table, and at the same time i want to insert data into the child table depending on the last inserted row of the mother table. So I need 2 insert commands to run. But I think data inconsistency may happened. When i insert into the second table, then the mother table may be updated. Primary key of the mother table is declared as identity column. Can anyone suggest me how i can insert into those two tables.I think you should use stored procedure which do some operations like this:

1) insert to mother table
2) use @.@.IDENTITY to get identity value last inserted row
3) insert to the second table