Inserting 2 tables with Pk/Fk

Hello all... I'm working on a C++ Windows service that writes to a SQL Server database. I consider myself quite a novice at SQL Server, but I have played around with it over the years... Performance is going to be a concern with this project.

Let's say...
Table A has columns PkA(identity), Stuff(text), FkB (Table B's Pk)
Table B has columns PkB(identity), MoreStuff(text)

I'll be executing SQL statements from my service - INSERTs, etc...

What's the most efficient way to write to these two tables? The immediate challenge I have is getting that PkB value after inserting Table B and using it for Table A's FkB.

Is there a way I can insert into both tables with one SQL statement?

Thanks!! Curt.First, I recommend that your service call a stored procedure to make this happen, and not issue an ad-hoc query. the sproc would do both inserts, you'd just call it with the values you need to put in Stuff and MoreStuff. So as far as your service is concerned, both inserts happen in "one statement". Within the sproc it's still two inserts though.

Second, in your sproc after your insert into tableB, you can call SCOPE_IDENTITY() to get the identity value that was just inserted. use this value as the fk in tableA when you do the insert there.

take a look at SCOPE_IDENTITY() in BOL. @.@.IDENTITY is a related beast, but SCOPE_IDENTITY() is preferred since it's scoped, as the name implies.

Edit: since I have my roots in C++ as well, thought I would add this: leaving your tables open to ad-hoc queries from client apps is like designing a class in C++ where all the fields are public. If your table structure changes, you have to recompile and redeploy your service. You wouldn't want to do that would you? :)

I think of sprocs as analogous to the public member functions on a class. use them to control how clients are allowed to manipulate the private fields (your tables), and make all fields (tables) private.|||jezemine, yeah I guess I should have qualfied that a bit more... We are in fact planning to put that into a sproc a little later on. As I mentioned, I'm not really a sql server pro and sprocs are on my list of items to conquer... Right now we just need to get something up and running to help prove concept. Thanks for the tips, though! Perhaps I'll conquer that beast sooner than I thought! :)|||ok, but remember that prototype code sometimes has a way of "sticking" :)

Insert...exec doesn't work properly

Hello
MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
I have certain procedure which can't be reproduced
using my common technique.
CREATE table #t (text nvarchar(4000))
insert into #t exec sp_helptext 'objectname'
SELECT * FROM #T
DROP TABLE #T
exec sp_helptext 'objectname'
Two output resultsets are DIFFERENT!
Row order in temporary table doesn't coincide with real
row order (two rows with numbers 119 and 120 inserted
into positions 68 and 69).
This is critical for me!
Does anyone know the cause of this or any workaround?
Thanks,
Serge ShakhovThere is no way to totally control how rows are stored in a table. The
order in which you insert them has little to do with how they are stored or
placed into the pages on disk. Even with a clustered index you are not
guaranteed for everything to be physically and logically in order. So if
you want to get them out of a table in a specific order then you need to
specify an ORDER BY clause.
--
Andrew J. Kelly
SQL Server MVP
"Serge Shakhov" <REMOVETHIS_ACETYLENE@.mail.ru> wrote in message
news:48dgmb.5o3.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
> I have certain procedure which can't be reproduced
> using my common technique.
> CREATE table #t (text nvarchar(4000))
> insert into #t exec sp_helptext 'objectname'
> SELECT * FROM #T
> DROP TABLE #T
> exec sp_helptext 'objectname'
> Two output resultsets are DIFFERENT!
> Row order in temporary table doesn't coincide with real
> row order (two rows with numbers 119 and 120 inserted
> into positions 68 and 69).
> This is critical for me!
> Does anyone know the cause of this or any workaround?
> Thanks,
> Serge Shakhov
>|||Hello
> MSSQL 2000 EE + SP3 on Windows 2000 AS + SP4
> I have certain procedure which can't be reproduced
> using my common technique.
> CREATE table #t (text nvarchar(4000))
> insert into #t exec sp_helptext 'objectname'
> SELECT * FROM #T
> DROP TABLE #T
> exec sp_helptext 'objectname'
> Two output resultsets are DIFFERENT!
I still don't know the reason of this behavior
but now I know that changing nvarchar datatype
to varchar forces server to work properly.
This can be considered as workaround.
Serge Shakhovsql

Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP Job
This sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>

Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>sql

Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx
.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>

insert update problem

I have a couple Windows 2000 servers SP4, that cannot insert/updated to a
SQL 2000 SP3a server on a Windows 2003 server. Windows 2003 server was
installed fresh. Upgraded to 2000 to 2003 Windows servers with SQL 2000
SP3a work fine. I have ran MDAC 2.8 on the Windows 2000 SP4 server to see
if that would help, but it did not. Any ideas? Thanks, Marc
What query are you executing? What error or unexpected behavior do you get?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
sql

Insert produces error

Hi,

Using SQL Server 2000 with Windows 2000 Adv Server
&
Microsoft Access linked table (running stored procedure using ADO as
follows:

************************************************** ********
Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim msg As String

On Error GoTo Err_AddrType_NotInList
'Exit the procedure if the combo box was cleared
If Trim(NewData) = "" Then Exit Sub

'Confirm that the user wants to add AddrType
msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
'If the user chose not to add AddrType, set the response
'argument to supress an error message and undo changes.
Response = acDataErrContinue
MsgBox "No record added.", vbOKOnly, "Action Cancelled"
Else
'If the user chose to add AddrType, open a recordset
'using the AddrType table

Set cmd = New ADODB.Command
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
Catalog=groomery;Integrated Security=SSPI;"

cmd.ActiveConnection = cnn
cmd.CommandText = "spInsertAddrType"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("AddrType", adVarChar,
adParamInput, , Trim(NewData))
cmd.Execute Parameters:=prm
'Set Response argument to indicate that new data is being added
Response = acDataErrAdded

cnn.Close
Set cnn = Nothing
End If

Exit_AddrType_NotInList:
Exit Sub

Err_AddrType_NotInList:
MsgBox Err.Description
Response = acDataErrContinue
************************************************** ********

"NewData" is a text string - in this case "Test"

The stored procedure referenced in the code is:

************************************
CREATE PROCEDURE [spInsertAddrType]
(@.AddrType [nvarchar](50))

AS
INSERT INTO [groomery].[dbo].[tblAddrTypes]
([fldAddrType])

VALUES
(@.AddrType)
GO
*************************************

When I execute this code, I receive the following error

"Cannot update identity column 'fldAddrTypeID'."

fldAddrTypeID is configured as follows:

***************************
Data Type = int
Identity = Yes
Identity Seed = 1
Identity Increment = 1
***************************

The documentation I've found online concerning this error says that it is
produced when you try to supply a value for an identity field without SET
IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
figure why I'm getting this error.

Thanks for any help you can offer.

ToddHi,

Found the answer elsewhere but thought I'd share it here in case someone
else has this problem.

Access's upsizing wizard created a trigger on tblAddrTypes which (evidently)
was meant to emulate Access's autonumber functionality. Once I deleted that
trigger, everything worked fine.

Todd
"Todd" <infoNOSPAM@.MAPSONgroomery.biz> wrote in message
news:T1j5e.11405$FN4.303@.newssvr21.news.prodigy.co m...
> Hi,
> Using SQL Server 2000 with Windows 2000 Adv Server
> &
> Microsoft Access linked table (running stored procedure using ADO as
> follows:
> ************************************************** ********
> Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)
> Dim cnn As ADODB.Connection
> Dim cmd As ADODB.Command
> Dim prm As ADODB.Parameter
> Dim msg As String
> On Error GoTo Err_AddrType_NotInList
> 'Exit the procedure if the combo box was cleared
> If Trim(NewData) = "" Then Exit Sub
> 'Confirm that the user wants to add AddrType
> msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
> msg = msg & "Do you want to add it?"
> If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
> 'If the user chose not to add AddrType, set the response
> 'argument to supress an error message and undo changes.
> Response = acDataErrContinue
> MsgBox "No record added.", vbOKOnly, "Action Cancelled"
> Else
> 'If the user chose to add AddrType, open a recordset
> 'using the AddrType table
>
> Set cmd = New ADODB.Command
> Set cnn = New ADODB.Connection
> cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
> Catalog=groomery;Integrated Security=SSPI;"
> cmd.ActiveConnection = cnn
> cmd.CommandText = "spInsertAddrType"
> cmd.CommandType = adCmdStoredProc
> Set prm = cmd.CreateParameter("AddrType", adVarChar,
> adParamInput, , Trim(NewData))
> cmd.Execute Parameters:=prm
> 'Set Response argument to indicate that new data is being added
> Response = acDataErrAdded
> cnn.Close
> Set cnn = Nothing
> End If
> Exit_AddrType_NotInList:
> Exit Sub
> Err_AddrType_NotInList:
> MsgBox Err.Description
> Response = acDataErrContinue
> ************************************************** ********
> "NewData" is a text string - in this case "Test"
> The stored procedure referenced in the code is:
> ************************************
> CREATE PROCEDURE [spInsertAddrType]
> (@.AddrType [nvarchar](50))
> AS
> INSERT INTO [groomery].[dbo].[tblAddrTypes]
> ([fldAddrType])
> VALUES
> (@.AddrType)
> GO
> *************************************
> When I execute this code, I receive the following error
> "Cannot update identity column 'fldAddrTypeID'."
> fldAddrTypeID is configured as follows:
> ***************************
> Data Type = int
> Identity = Yes
> Identity Seed = 1
> Identity Increment = 1
> ***************************
> The documentation I've found online concerning this error says that it is
> produced when you try to supply a value for an identity field without SET
> IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
> figure why I'm getting this error.
> Thanks for any help you can offer.
> Todd

Insert or update depending on existence of record

Hello
I'm using SQL Server 2000, Windows 2000.
I'm writing a SP to check for the existence of records in a table, and to
insert or update based on this existence. In other words, I have 2 tables
- source and destination. If a record from the source table already exists
in the destination table I want to update it, if not, I want to insert the
record. At them moment I am using 'If Exists', but I don't know how to
loop through all the records in the source table and check for each one.
Because the SP finds at least one record in both tables, Exists is true and
the SP never reaches the 'Else'.
Here's what I have so far (apologies for the formatting - it's gone a bit
mad):-
CREATE PROCEDURE UpdateClient
AS
Begin
if exists(select a.MembershipNo from tblDataDest2 a, tblDataDest b
where a.MembershipNo = b.MembershipNo)
Begin
Update tblDataDest2
set NINumber = a.NINumber, Firstname = a.Firstname, Surname = a.Surname,
Title = a.Title,
Initials = a.Initials, Address1 = a.Address1, Address2 = a.Address2,
Address3 = a.Address3,
Town = a.Town, County = a.County, Postcode = a.Postcode, Location =
a.Location,
CurrentSalary = a.CurrentSalary, NRA = a.NRA, DOB = a.DOB,
StateRetirementAge = a.StateRetirementAge,
Sex = a.Sex, ServiceStartDate = a.ServiceStartDate, FullTimeIndicator =
a.FullTimeIndicator,
PlanType = a.PlanType, OldPlanBasisAtNRA5 = a.OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4 = a.OldPlanBasisAtNRA4, OldPlanBasisAtNRA3 =
a.OldPlanBasisAtNRA3,
OldPlanBasisAtNRA2 = a.OldPlanBasisAtNRA2, OldPlanBasisAtNRA1 =
a.OldPlanBasisAtNRA1,
OldPlanBasisAtNRA = a.OldPlanBasisAtNRA, OldDBBenefitAtNRA5 =
a.OldDBBenefitAtNRA5,
OldDBBenefitAtNRA4 = a.OldDBBenefitAtNRA4, OldDBBenefitAtNRA3 =
a.OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2 = a.OldDBBenefitAtNRA2, OldDBBenefitAtNRA1 =
a.OldDBBenefitAtNRA1,
OldDBBenefitAtNRA = a.OldDBBenefitAtNRA, CAREAtNRA5 = a.CAREAtNRA5,
CAREAtNRA4 = a.CAREAtNRA4, CAREAtNRA3 = a.CAREAtNRA3, CAREAtNRA2 =
a.CAREAtNRA2,
CAREAtNRA1 = a.CAREAtNRA1, CAREAtNRA = a.CAREAtNRA, DC@.11AtNRA5 =
a.DC@.11AtNRA5,
DC@.11AtNRA4 = a.DC@.11AtNRA4, DC@.11AtNRA3 = a.DC@.11AtNRA3,
DC@.11AtNRA2 = a.DC@.11AtNRA2, DC@.11AtNRA1 = a.DC@.11AtNRA1,
DC@.11AtNRA = a.DC@.11AtNRA, S2P = a.S2P, S2POld = a.S2POld, BatchID =
a.BatchID
From tblDataDest a
where tblDataDest2.MembershipNo = a.MembershipNo
End
Else
Begin
Insert into tblDataDest2 (MembershipNo, NINumber, Firstname, Surname,
Title,
Initials, Address1, Address2, Address3, Town, County, Postcode, Location,
CurrentSalary, NRA,
DOB, StateRetirementAge, Sex, ServiceStartDate, FullTimeIndicator,
PlanType, OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4, OldPlanBasisAtNRA3, OldPlanBasisAtNRA2,
OldPlanBasisAtNRA1,
OldPlanBasisAtNRA, OldDBBenefitAtNRA5, OldDBBenefitAtNRA4,
OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2, OldDBBenefitAtNRA1, OldDBBenefitAtNRA, CAREAtNRA5,
CAREAtNRA4,
CAREAtNRA3, CAREAtNRA2, CAREAtNRA1, CAREAtNRA, DC@.11AtNRA5, DC@.11AtNRA4,
DC@.11AtNRA3, DC@.11AtNRA2, DC@.11AtNRA1, DC@.11AtNRA, S2P, S2POld, BatchID)
Select MembershipNo, NINumber, Firstname, Surname, Title, Initials,
Address1, Address2,
Address3, Town, County, Postcode, Location, CurrentSalary, NRA, DOB,
StateRetirementAge, Sex,
ServiceStartDate, FullTimeIndicator, PlanType, OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4,
OldPlanBasisAtNRA3, OldPlanBasisAtNRA2, OldPlanBasisAtNRA1,
OldPlanBasisAtNRA,
OldDBBenefitAtNRA5, OldDBBenefitAtNRA4, OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2,
OldDBBenefitAtNRA1, OldDBBenefitAtNRA, CAREAtNRA5, CAREAtNRA4,
CAREAtNRA3,
CAREAtNRA2, CAREAtNRA1, CAREAtNRA, DC@.11AtNRA5, DC@.11AtNRA4,
DC@.11AtNRA3,
DC@.11AtNRA2, DC@.11AtNRA1, DC@.11AtNRA, S2P, S2POld, BatchID from
tblDataDest
End
End
GO
Can anyone suggest how I can tweak this in order to have it run through
each record in the source and check it against the destination?
Many thanks
DeniseWhat about updating first all rows and then add the non existing ?
UPDATE tblDataDest2
SET (YourUpdatesetlist)
FROM tblDataDest2 T1
INNER JOIN tblDataDest T2
ON T1.MembershipNo = T2.MembershipNo
and then
INSERt INTO tblDataDest2
(Columnlisthere)
SELECT (Columnlisthere)
FROM tblDataDest T1
WHERE NOT EXISTS
(
SELECT * FROM tblDataDest2 T2
WHERE T1.MembershipNo = T2.MembershipNo
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks Jens. That worked perfectly. And so simple!
On 3 May 2006 03:15:25 -0700, Jens wrote:

> What about updating first all rows and then add the non existing ?
> UPDATE tblDataDest2
> SET (YourUpdatesetlist)
> FROM tblDataDest2 T1
> INNER JOIN tblDataDest T2
> ON T1.MembershipNo = T2.MembershipNo
> and then
> INSERt INTO tblDataDest2
> (Columnlisthere)
> SELECT (Columnlisthere)
> FROM tblDataDest T1
> WHERE NOT EXISTS
> (
> SELECT * FROM tblDataDest2 T2
> WHERE T1.MembershipNo = T2.MembershipNo
> )
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Jena
I sure would like to see the DDL of the table and some sample data to go
along with that. I have a similar business requirements and would like to se
e
if I could duplicate your results. This would give me a great start.
Thanks In Advance
kw_uh97
"Denise" wrote:

> Thanks Jens. That worked perfectly. And so simple!
>
> On 3 May 2006 03:15:25 -0700, Jens wrote:
>
>|||You can contact me along the EMailadress which can be found on my site:
http://www.sqlserver2005.de