Orphaned Sessions and locks: separate

I've written a set of stored procedures that currently use a cursor to pull
data from a staging table into two separate tables. The two destination
tables are linked by a Foreign Key relationship on the primary key (can't
change this design), which is why I need to use a cursor to be able to inser
t
records into both tables.
I was wondering whether there is any other possible solution to using a
cursor to insert records into the two tables?Another thing I forgot to mention is that I'm generating new
uniqueidentifiers within the cursor for each new record as primary keys for
the inserted records (there are no primary keys within the staging table)
such that destination_table1.accountId = destination_table2.accountId
"EL" wrote:

> I've written a set of stored procedures that currently use a cursor to pul
l
> data from a staging table into two separate tables. The two destination
> tables are linked by a Foreign Key relationship on the primary key (can't
> change this design), which is why I need to use a cursor to be able to ins
ert
> records into both tables.
> I was wondering whether there is any other possible solution to using a
> cursor to insert records into the two tables?|||Can you give the columns in the input tables
and what all needs to go to the first and second table?
--
"EL" wrote:
> Another thing I forgot to mention is that I'm generating new
> uniqueidentifiers within the cursor for each new record as primary keys fo
r
> the inserted records (there are no primary keys within the staging table)
> such that destination_table1.accountId = destination_table2.accountId
> "EL" wrote:
>|||Can you post CREATE TABLE statements, and a couple of lines of sample
data; that would be helpful for those of us who want to copy and paste
the code into our own query analyzers to test. Just off the top of my
head, can you not use temp tables or table variables to pre-generate
the uniqueidentifiers on the parent and child tables, and then insert
the parent rows followed by the child rows?
Without trying to open up a whole can of worms on the issue of
surrogate vs natural keys, why are you using uniqueidentifiers?
Although there are times when the uniqueidentifier is necessary, it has
some consequences.
Stu|||Thanks for youre reply omnibuzz.
I also forgot to mention that I would be inserting into 3 separate tables.
I'll explain them later in this message.
Here's a subset of the columns that exist in the single staging table:
TTC_Agency_Id int null,
Agency_Name varchar(150) NULL,
Agency_Addr1 varchar(150) NULL,
Agency_Addr2 varchar(150) NULL,
Agency_Addr3 varchar(150) NULL,
Agency_City varchar(150) NULL,
Agency_State varchar(150) NULL,
Agency_Zip varchar(150) NULL,
Agency_Country varchar (150) NULL,
Agency_IATA_Number varchar(25) NULL,
Agency_Email varchar(100) NULL,
Agency_Barred varchar(10) NULL,
Agency_Fax varchar (50) NULL,
Agency_Phone varchar(50) NULL
This staging table contains various other columns used by other stored
procedures which are more or less similar in their function. If I was
currently running the procedure that pulls all the data contained in the
Agency_... columns from the staging table then the procedure would be doing
the following (pseudo code):
create cursor from query that pulls Agency_... column data from staging tabl
e
Within Cursor:
Create uniqueidentifier value (Primary Key) -- call this newid
Create uniqueidentifier value (Address Primary Key) -- call this
addressId
Insert into table1 values (newid, Agency_Name, Agency_IATA,
ttc_agency_id)
Insert into table2 values (newid, Agency_Email, Agency_Barred,
Agency_Fax, Agency_Phone)
Insert into table3 values(addressId, newid, Agency_Addr1,
Agency_Addr2, Agency_Addr3, Agency_City, Agency_State, Agency_Zip,
Agency_Country)
close cursor
Table1 and table2 are the main destination tables with a foreign key
relationship on the primary keys (table1.accountId = table2.accountId) which
is why I create a new id for the agency inside the cursor.
Table3 is another table that holds address details (in this case holds the
agency address details)
I thought about putting a uniqueidetifier column within my source / staging
table that gets populated when data is loading into that table and use that
value for the primary keys of the destination tables (table1 and table2), bu
t
that would mean that I would no longer be able to perform bulk inserts into
that table.
"Omnibuzz" wrote:
> Can you give the columns in the input tables
> and what all needs to go to the first and second table?
> --
>
>
> "EL" wrote:
>|||Here's the script for the staging table that I use (simplified down)
Create table TempBookings (
Agency_Name varchar(150) NULL,
Agency_Addr1 varchar(150) NULL,
Agency_Addr2 varchar(150) NULL,
Agency_Addr3 varchar(150) NULL,
Agency_City varchar(150) NULL,
Agency_State varchar(150) NULL,
Agency_Zip varchar(150) NULL,
Agency_Country varchar (150) NULL,
Agency_IATA_Number varchar(25) NULL,
Agency_Email varchar(100) NULL,
Agency_Barred varchar(10) NULL,
Agency_Fax varchar (50) NULL,
Agency_Phone varchar(50) NULL,
Agency_Code varchar (30) NULL,
TTC_Agency_Id int NULL
)
Here's some insert sql code for inserting values into the staging table
insert into TempBookings values ('Test 1', 'Test_1_Addr1', 'Test_1_Addr2',
'Test_1_Addr3', 'Test_1_City', 'Test_1_State', 'Test_1_Zip',
'Test_1_Country', 'Test_1_IATA', 'Test_1_Email@.Test.com', '1', '12345678',
'12345689', 'Test_1_Code', 50)
insert into TempBookings values ('Test 2', 'Test_2_Addr1', 'Test_2_Addr2',
'Test_2_Addr3', 'Test_2_City', 'Test_2_State', 'Test_2_Zip',
'Test_2_Country', 'Test_2_IATA', 'Test_2_Email@.Test.com', '1', '12345678',
'12345689', 'Test_2_Code', 52)
insert into TempBookings values ('Test 3', 'Test_3_Addr1', 'Test_3_Addr2',
'Test_3_Addr3', 'Test_3_City', 'Test_3_State', 'Test_3_Zip',
'Test_3_Country', 'Test_3_IATA', 'Test_3_Email@.Test.com', '1', '12345678',
'12345689', 'Test_3_Code', 65)
insert into TempBookings values ('Test 4', 'Test_4_Addr1', 'Test_4_Addr2',
'Test_4_Addr3', 'Test_4_City', 'Test_4_State', 'Test_4_Zip',
'Test_4_Country', 'Test_4_IATA', 'Test_4_Email@.Test.com', '1', '12345678',
'12345689', 'Test_4_Code', 67)
insert into TempBookings values ('Test 5', 'Test_5_Addr1', 'Test_5_Addr2',
'Test_5_Addr3', 'Test_5_City', 'Test_5_State', 'Test_5_Zip',
'Test_5_Country', 'Test_5_IATA', 'Test_5_Email@.Test.com', '1', '12345678',
'12345689', 'Test_5_Code', 68)
insert into TempBookings values ('Test 6', 'Test_6_Addr1', 'Test_6_Addr2',
'Test_6_Addr3', 'Test_6_City', 'Test_6_State', 'Test_6_Zip',
'Test_6_Country', 'Test_6_IATA', 'Test_6_Email@.Test.com', '1', '12345678',
'12345689', 'Test_6_Code', 69)
insert into TempBookings values ('Test 7', 'Test_7_Addr1', 'Test_7_Addr2',
'Test_7_Addr3', 'Test_7_City', 'Test_7_State', 'Test_7_Zip',
'Test_7_Country', 'Test_7_IATA', 'Test_7_Email@.Test.com', '1', '12345678',
'12345689', 'Test_7_Code', 70)
insert into TempBookings values ('Test 8', 'Test_8_Addr1', 'Test_8_Addr2',
'Test_8_Addr3', 'Test_8_City', 'Test_8_State', 'Test_8_Zip',
'Test_8_Country', 'Test_8_IATA', 'Test_8_Email@.Test.com', '1', '12345678',
'12345689', 'Test_8_Code', 71)
insert into TempBookings values ('Test 9', 'Test_9_Addr1', 'Test_9_Addr2',
'Test_9_Addr3', 'Test_9_City', 'Test_9_State', 'Test_9_Zip',
'Test_9_Country', 'Test_9_IATA', 'Test_9_Email@.Test.com', '1', '12345678',
'12345689', 'Test_9_Code', 72)
insert into TempBookings values ('Test 10', 'Test_10_Addr1',
'Test_10_Addr2', 'Test_10_Addr3', 'Test_10_City', 'Test_10_State',
'Test_10_Zip', 'Test_10_Country', 'Test_10_IATA', 'Test_10_Email@.Test.com',
'1', '12345678', '12345689', 'Test_10_Code', 73)
The scripts below have been generated by scripting the destination tables
involved in one of the storedprocedures
CREATE TABLE [dbo].[AccountBase] (
[AccountId] uniqueidentifier ROWGUIDCOL NOT NULL ,
[Name] [nvarchar] (160) COLLATE Latin1_General_CI_AS NULL ,
[AccountNumber] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[EMailAddress1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Telephone1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Fax] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[AccountExtensionBase] (
[AccountId] [uniqueidentifier] NOT NULL ,
[New_AccountStatus] [int] NULL ,
[New_TTC_Id] [int] NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[AccountBase] WITH NOCHECK ADD
CONSTRAINT [cndx_PrimaryKey_Account] PRIMARY KEY CLUSTERED
(
[AccountId]
) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[AccountExtensionBase] WITH NOCHECK ADD
CONSTRAINT [PK_AccountExtensionBase] PRIMARY KEY CLUSTERED
(
[AccountId]
) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CREATE INDEX [ndx_for_cascaderelationship_account_par
ent_account] ON
[dbo].[AccountBase]([ParentAccountId]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerAddressBase] (
[ParentId] [uniqueidentifier] NOT NULL ,
[CustomerAddressId] uniqueidentifier ROWGUIDCOL NOT NULL ,
[AddressNumber] [int] NULL ,
[Name] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
[Line1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Line2] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Line3] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[City] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[StateOrProvince] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[PostalCode] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerAddressBase] WITH NOCHECK ADD
CONSTRAINT [cndx_PrimaryKey_CustomerAddress] PRIMARY KEY CLUSTERED
(
[CustomerAddressId]
) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CREATE INDEX [ndx_for_cascaderelationship_Contact_Cus
tomerAddress] ON
[dbo].[CustomerAddressBase]([ParentId]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
"Stu" wrote:

> Can you post CREATE TABLE statements, and a couple of lines of sample
> data; that would be helpful for those of us who want to copy and paste
> the code into our own query analyzers to test. Just off the top of my
> head, can you not use temp tables or table variables to pre-generate
> the uniqueidentifiers on the parent and child tables, and then insert
> the parent rows followed by the child rows?
> Without trying to open up a whole can of worms on the issue of
> surrogate vs natural keys, why are you using uniqueidentifiers?
> Although there are times when the uniqueidentifier is necessary, it has
> some consequences.
> Stu
>|||Here's my stored procedure (rather the cursor) that I use to insert the
records into the destination tables from the staging table. I've simplified
it down quite heavily just for this example, and so it may not work correctl
y
declare @.Name varchar(150)
declare @.AddressLine1 varchar(50)
declare @.AddressLine2 varchar(50)
declare @.AddressLine3 varchar(50)
declare @.City varchar(50)
declare @.State varchar(50)
declare @.Zip varchar(50)
declare @.Country varchar(50)
declare @.IATA varchar(30)
declare @.AgencyEmail varchar(100)
declare @.New_AccountStatusNew varchar(5)
declare @.NewId uniqueidentifier
declare @.AddressId uniqueidentifier
declare TravelAgenciesCursor Cursor LOCAL FAST_FORWARD for
select distinct Agency_Name, Agency_Addr1, Agency_Addr2, Agency_addr3,
Agency_City, Agency_State, Agency_Zip, Agency_Country,
Agency_IATA_Number, Agency_Email, Agency_Barred Agency_Fax, TTC_Agency_Id,
Agency_Phone
from TempBooking
open TravelAgenciesCursor
Fetch next from TravelAgenciesCursor into @.Name, @.AddressLine1,
@.AddressLine2, @.AddressLine3, @.City,
@.State, @.Zip, @.Country, @.IATA, @.AgencyEmail, @.New_AccountStatusNew,
@.AgencyFax, @.TTC_Id, @.Phone
While (@.@.Fetch_Status <> -1)
Begin
if (@.@.Fetch_Status <> -2)
Begin
-- Set a new Consortium Id every time new insert and get agency type value
and the current date
set @.NewId = NewId()
-- Inserts sets for inserting Agencies
Insert into AccountBase (AccountId, Name, AccountNumber, EMailAddress1,
Telephone1, Fax) values
(@.NewId, @.Name, @.IATA, @.AgencyEmail, @.Phone, @.Fax)
insert into AccountExtensionBase (AccountId, New_AccountStatus,
New_TTC_Id) values
(@.NewId, 1, @.TTC_Id)
-- insert a new record for the customer
insert into CustomerAddressBase (ParentId, CustomerAddressId,
AddressNumber, Name, Line1, Line2, Line3,
City, StateOrProvince, Country, PostalCode) values
(@.NewId, NewId(), 1, @.Name, @.AddressLine1, @.AddressLine2, @.AddressLine3,
@.City, @.State, @.Country, @.Zip)
End
Fetch next from TravelAgenciesCursor into @.Name, @.AddressLine1,
@.AddressLine2, @.AddressLine3, @.City,
@.State, @.Zip, @.Country, @.IATA, @.AgencyEmail, @.New_AccountStatusNew,
@.AgencyFax, @.TTC_Id, @.Phone
End
Close TravelAgenciesCursor
deallocate TravelAgenciesCursor
As regards the use of the temp tables of table variables, I did write a
version of the same stored procedure using a table variable instead of a
cursor, but in most of the cases the query optimizer either showed either
very little or no improvement or worse query performance using the table
variable.
I'm guessing the use of a uniqueidentifier as the primary key was to
maintain global uniqueness for each record, which in this situation isn't
really necessary. I would have wanted to use an int value instead but making
any changes to the table or database design is out of the question.
"EL" wrote:
> Here's the script for the staging table that I use (simplified down)
> Create table TempBookings (
> Agency_Name varchar(150) NULL,
> Agency_Addr1 varchar(150) NULL,
> Agency_Addr2 varchar(150) NULL,
> Agency_Addr3 varchar(150) NULL,
> Agency_City varchar(150) NULL,
> Agency_State varchar(150) NULL,
> Agency_Zip varchar(150) NULL,
> Agency_Country varchar (150) NULL,
> Agency_IATA_Number varchar(25) NULL,
> Agency_Email varchar(100) NULL,
> Agency_Barred varchar(10) NULL,
> Agency_Fax varchar (50) NULL,
> Agency_Phone varchar(50) NULL,
> Agency_Code varchar (30) NULL,
> TTC_Agency_Id int NULL
> )
> Here's some insert sql code for inserting values into the staging table
> insert into TempBookings values ('Test 1', 'Test_1_Addr1', 'Test_1_Addr2',
> 'Test_1_Addr3', 'Test_1_City', 'Test_1_State', 'Test_1_Zip',
> 'Test_1_Country', 'Test_1_IATA', 'Test_1_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_1_Code', 50)
> insert into TempBookings values ('Test 2', 'Test_2_Addr1', 'Test_2_Addr2',
> 'Test_2_Addr3', 'Test_2_City', 'Test_2_State', 'Test_2_Zip',
> 'Test_2_Country', 'Test_2_IATA', 'Test_2_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_2_Code', 52)
> insert into TempBookings values ('Test 3', 'Test_3_Addr1', 'Test_3_Addr2',
> 'Test_3_Addr3', 'Test_3_City', 'Test_3_State', 'Test_3_Zip',
> 'Test_3_Country', 'Test_3_IATA', 'Test_3_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_3_Code', 65)
> insert into TempBookings values ('Test 4', 'Test_4_Addr1', 'Test_4_Addr2',
> 'Test_4_Addr3', 'Test_4_City', 'Test_4_State', 'Test_4_Zip',
> 'Test_4_Country', 'Test_4_IATA', 'Test_4_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_4_Code', 67)
> insert into TempBookings values ('Test 5', 'Test_5_Addr1', 'Test_5_Addr2',
> 'Test_5_Addr3', 'Test_5_City', 'Test_5_State', 'Test_5_Zip',
> 'Test_5_Country', 'Test_5_IATA', 'Test_5_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_5_Code', 68)
> insert into TempBookings values ('Test 6', 'Test_6_Addr1', 'Test_6_Addr2',
> 'Test_6_Addr3', 'Test_6_City', 'Test_6_State', 'Test_6_Zip',
> 'Test_6_Country', 'Test_6_IATA', 'Test_6_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_6_Code', 69)
> insert into TempBookings values ('Test 7', 'Test_7_Addr1', 'Test_7_Addr2',
> 'Test_7_Addr3', 'Test_7_City', 'Test_7_State', 'Test_7_Zip',
> 'Test_7_Country', 'Test_7_IATA', 'Test_7_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_7_Code', 70)
> insert into TempBookings values ('Test 8', 'Test_8_Addr1', 'Test_8_Addr2',
> 'Test_8_Addr3', 'Test_8_City', 'Test_8_State', 'Test_8_Zip',
> 'Test_8_Country', 'Test_8_IATA', 'Test_8_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_8_Code', 71)
> insert into TempBookings values ('Test 9', 'Test_9_Addr1', 'Test_9_Addr2',
> 'Test_9_Addr3', 'Test_9_City', 'Test_9_State', 'Test_9_Zip',
> 'Test_9_Country', 'Test_9_IATA', 'Test_9_Email@.Test.com', '1', '12345678',
> '12345689', 'Test_9_Code', 72)
> insert into TempBookings values ('Test 10', 'Test_10_Addr1',
> 'Test_10_Addr2', 'Test_10_Addr3', 'Test_10_City', 'Test_10_State',
> 'Test_10_Zip', 'Test_10_Country', 'Test_10_IATA', 'Test_10_Email@.Test.com'
,
> '1', '12345678', '12345689', 'Test_10_Code', 73)
>
> The scripts below have been generated by scripting the destination tables
> involved in one of the storedprocedures
> CREATE TABLE [dbo].[AccountBase] (
> [AccountId] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [Name] [nvarchar] (160) COLLATE Latin1_General_CI_AS NULL ,
> [AccountNumber] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
> [EMailAddress1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
> [Telephone1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Fax] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[AccountExtensionBase] (
> [AccountId] [uniqueidentifier] NOT NULL ,
> [New_AccountStatus] [int] NULL ,
> [New_TTC_Id] [int] NULL ,
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AccountBase] WITH NOCHECK ADD
> CONSTRAINT [cndx_PrimaryKey_Account] PRIMARY KEY CLUSTERED
> (
> [AccountId]
> ) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[AccountExtensionBase] WITH NOCHECK ADD
> CONSTRAINT [PK_AccountExtensionBase] PRIMARY KEY CLUSTERED
> (
> [AccountId]
> ) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> CREATE INDEX [ndx_for_cascaderelationship_account_par
ent_account] ON
> [dbo].[AccountBase]([ParentAccountId]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[CustomerAddressBase] (
> [ParentId] [uniqueidentifier] NOT NULL ,
> [CustomerAddressId] uniqueidentifier ROWGUIDCOL NOT NULL ,
> [AddressNumber] [int] NULL ,
> [Name] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
> [Line1] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Line2] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Line3] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [City] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [StateOrProvince] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [Country] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [PostalCode] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[CustomerAddressBase] WITH NOCHECK ADD
> CONSTRAINT [cndx_PrimaryKey_CustomerAddress] PRIMARY KEY CLUSTERED
> (
> [CustomerAddressId]
> ) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
>
> CREATE INDEX [ndx_for_cascaderelationship_Contact_Cus
tomerAddress] ON
> [dbo].[CustomerAddressBase]([ParentId]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
>
> "Stu" wrote:
>|||The design and your specification doesn't give much hope of preserving
integrity. If you are forced to accept it you may just have to make the
best of a bad job. The following is an example although probably not a
very good one. A good solution depends on whether there is some
uniqueness in the source data that can be preserved even despite the
missing keys. From your sample data I can only guess
(Agency_IATA_Number for example?).
I didn't read your code to identify which columns mapped to which, I
just guessed. Hopefully this will give you some ideas.
What I'd also do is document the fact that the model may make a
nonsense of the data. Unfortunately it's often not until the business
customers suffer that IT departments take note of data quality issues.
INSERT INTO dbo.AccountBase
(AccountId, Name, AccountNumber, EMailAddress1, Telephone1, Fax)
SELECT NEWID(), MAX(Agency_Name), Agency_IATA_Number,
MAX(Agency_Email), MAX(Agency_Phone), MAX(Agency_Fax)
FROM dbo.TempBookings
GROUP BY Agency_IATA_Number ;
INSERT INTO dbo.AccountExtensionBase (AccountId, New_AccountStatus,
New_TTC_Id)
SELECT A.AccountId, 0, MIN(T.TTC_Agency_Id)
FROM dbo.TempBookings AS T
JOIN dbo.AccountBase AS A
ON T.Agency_IATA_Number = A.AccountNumber
GROUP BY A.AccountId ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||try this.. hope this helps.
-- setting up the staging table for my example
create table #staging (a int, b int)
insert into #staging values(1,1)
insert into #staging values(1,3)
insert into #staging values(2,1)
insert into #staging values(1,2)
insert into #staging values(2,3)
insert into #staging values(3,3)
-- till here was the setup to the staging table
alter table #staging add c uniqueidentifier not null constraint df1 default
newid()
--do you bulk insert here.. now that you have the guid
-- get your staging table back to its original form
alter table #staging drop constraint df1
alter table #staging drop column c
-- this is for my example :)
drop table #staging|||OK,
So based off your stored procedure, all of your relationships between
entities are 1-to-1. You have two choices; either alter your staging
table AFTER your bulk insert in order to add a uniqueidentifier for
your data, OR move the data from your tempstaging table to a second
staging table with the uniqueidentifier already inserted.
Assuming that you want to do the former (should perform the best), you
can do something like this (sorry for the pseudo-code; running out the
door to work):
CREATE proc...
BEGIN TRANSACTION
ALTER TABLE TempStaging
ADD UNID uniqueidentifier DEFAULT newid WITH VALUES
INSERT INTO table1
SELECT table 1 columns
INSERT INTO table2...
INSERT INTO table3...
ALTER TABLE TempStaging
DROP COLUMN UNID
END TRANSACTION
Maybe that will get you started.
HTH,
Stu

Tuesday, March 20, 2012

other possibilities than cursor

Wednesday, March 7, 2012

osql in a cmd file, with SQL statements coming from the same cmd f

Orphaned Sessions and locks

Blog Archive

About Me