How can I INSERT w/Constraints in other tables?

trint

Three tables (all new, no data) will receive data from dBase and be
transposed into them...All three have auto generated IDENTITY columns
and pk and fk constraints. Can someone provide me with an
understandible sample?
Thanks,
Trint

Jul 23 '05 #1

David Portas

This example may help. Assuming your tables look like this:

CREATE TABLE Foo (foo_id INTEGER IDENTITY PRIMARY KEY, foo_key
VARCHAR(10) NOT NULL UNIQUE)

CREATE TABLE Bar (bar_id INTEGER IDENTITY PRIMARY KEY, bar_key
VARCHAR(10) NOT NULL UNIQUE, foo_id INTEGER NOT NULL REFERENCES Foo
(foo_id))

And assuming you have a similar source structure from dBase, here are
the INSERTs:

INSERT INTO Foo (foo_key)
SELECT DISTINCT foo_key
FROM Foo_source

INSERT INTO Bar (bar_key, foo_id)
SELECT DISTINCT B.bar_key, F2.foo_id
FROM Bar_source AS B
JOIN Foo_source AS F
ON B.foo_id = F.foo_id
JOIN Foo AS F2
ON F.foo_key = F2.foo_key

For this to work you do of course need the alternate key ("foo_key" in
this case). That shouldn't be a problem. IDENTITY should never be the
only key of a table.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2

Trint Smith

David,
Here are the two tables...can you help in using the example with them?::

CREATE TABLE [tblTravelDetailMember] (
[TravelDetailUplineId] [int] NOT NULL ,
[TravelDetailId] [int] NOT NULL ,
[TravelDetailMemberTypeId] [int] NOT NULL ,
[memberId] [bigint] NOT NULL ,
[rankId] [int] NOT NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDetailMember] PRIMARY KEY CLUSTERED
(
[TravelDetailUplineId]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblTravelDetailMember_tblTravelDetail] FOREIGN KEY
(
[TravelDetailId]
) REFERENCES [tblTravelDetail] (
[TravelDetailId]
),
CONSTRAINT [FK_tblTravelDetailMember_tblTravelDetailMemberType] FOREIGN
KEY
(
[TravelDetailMemberTypeId]
) REFERENCES [tblTravelDetailMemberType] (
[TravelDetailMemberTypeId]
)
) ON [PRIMARY]
GO
----------------

CREATE TABLE [tblTravelDetailMemberType] (
[TravelDetailMemberTypeId] [int] NOT NULL ,
[Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CreatedDateTime] [datetime] NOT NULL ,
[Operator] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_tblTravelDetailMemberType] PRIMARY KEY CLUSTERED
(
[TravelDetailMemberTypeId]
) ON [PRIMARY]
) ON [PRIMARY]
GO

I get this error with this code:
string strSQL2 = "INSERT INTO tblTravelDetailMember(memberId, " +
" TravelDetailUplineId, " +
" rankId, " +
" TravelDetailId, " +
" CreatedDateTime, " +
" Operator) " +
"VALUES ('" + insertString2 + "', " +
" '" + insertString3 + "', " +
" '" + insertString4 + "', " +
" '" + insertString5 + "', " +
" '" + insertString6 + "', " +
" '" + insertString7 + "')";

Thanks,
Trint
Net programmer
tr***********@gmail.com

*** Sent via Developersdex http://www.developersdex.com ***

Jul 23 '05 #3

David Portas

You didn't specify what error message you are getting. You didn't
include the alternate keys or the IDENTITY properties that you said you
had. The only obvious problem with your INSERT is that you've missed
out one of the columns ("TravelDetailMemberTypeId").

Put the INSERT in a stored procedure rather than construct a SQL string
in your app. That way you can test and check the code more easily.

If you only want to insert rows one at a time then you should be able
to make use of the SCOPE_IDENTITY() function to do this. See Books
Online for details. I was assuming you would load all the data with one
INSERT per table.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #4