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 only distinct values from a Flat File

I have to insert the Values from the Flat Files , My table structures have Primary keys , how do i insert only the distinct values into the table without the ERROR VIOLATION OF Primary Key already exists a record.

Dropping and Adding Relationships after insert is a way but doesnt serve the whole purpose is there a way we can eliminate duplicate records based on their Primary key before inserting them into the Database.

You can do a lookup against your target table. Redirect the error output and send these rows to a sql destination for the target table (i.e. if the lookup didn't find it, it is a new row)

See the following thread...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Sort transformation has a check box that allows you to eliminate duplicates; another approach could be to use a agregation transformation.|||Thanks for the Quick Solutions i will do that and come back with the feedback, what about the performance which one is better the sort or Aggregates using group by or Lookup|||Are there duplicate primary key's in your flat file? If so you will need to go with either the aggregate or sort solutions (I may have misread your question, the lookup will find any primary keys already in the table before you start to insert your new records). I don't know which of these will be more effecient for you, but you can always try them both out for a few test runs and see for yourself as it will often depend on environment and data being loaded as to which will come out ahead.|||

The Lookup approach will not detect duplicates within the batch being processed. You would need to change it to no-cache mode. Sort and aggregation transformation should be give you about the same performance. In general the no-cache lookup, sort or aggregation approach are not great from the performance standpoint; but that would depend on many factors; so test and measure yourself.

Sort/aggregation transformation cache the full set of rows in RAM; so if the volume of data to be de-duplicated is huge; the system could run out of memory.

An alternative could be to use an staging table and then let the DB engine to do the dedup work. (e.g. http://rafael-salas.blogspot.com/2007/04/remove-duplicates-using-t-sql-rank.html )|||

I got to filter out the distinct values using aggregate transformation but i am unsure baout how to get all the columns into the output ... as the Aggregate is returning only the columns used to get the distinct on the Primary Keys.

|||There are two fundamentals being discussed in this thread. We've yet to get the crucial question answered though:

Are you trying to dedupe the source data before going into the destination table, or are you trying to prevent duplicate records from getting inserted (unique data from the source, but not necessarily all unique in the destination) and hence raising a primary key violation?

Deduping the source data can be done with an aggregate or the sort transformations. You could also load the data into a staging table (as Rafael stated) and then run a SQL statement against that data (select DISTINCT perhaps). Using the techniques described in the thread linked to earlier, you can ensure that your data does not violate primary keys by using a lookup. Note that you may have to do a combination of all of the above.|||

Dev2624 wrote:

I got to filter out the distinct values using aggregate transformation but i am unsure baout how to get all the columns into the output ... as the Aggregate is returning only the columns used to get the distinct on the Primary Keys.

Use the sort transformation instead.|||I am trying to Prevent the Duplicate Records from getting inserted . i am working on it will post back with the results. Thanks!!!sql

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 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 new unique INT if none is given

I've got a table name Messages and each message has a MessageID (the primary, auto-generated column) and a ThreadID (for grouping replies together). When I create a new message, I want to insert a new ThreadID if none is given to the stored procedure. I'm looking for something like NewID() as the default value but that inserts a NewInt. Do I have to write my own trigger?

And I know ThreadID should be a foreign key to a Threads table but I'm keeping it simple for now. I have the option of making that later if I like.

Thanks.

In Oracle, you would set up a sequence and ask it for the next number. Piece of cake!

I can think of two ways to do it in Sql Server.

The first is the easiest and, to my mind, the best. Create a Threads table with a ThreadId column that is an identity column. Create a trigger on your Messages table. It's simple, it's clean, and it's the right thing to do. :)

The second is to write a trigger that queries the messages table and returns the highest threadid value you find (+ 1). The problem is that, in a multi-user environment, you will have to lock the entire Messages table first to prevent anyone else from running that query until your insert finishes. Otherwise, you will get two different, unrelated messages with the same thread id. It's nasty, prone to error, and actually harder than doing the right thing.

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 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 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