retrieve autonumbers for append insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

retrieve autonumbers for append insert

Author

Topic

DesiGal
Starting Member

31 Posts

Posted - 2010-03-02 : 11:44:42

--Consider I have two tables
Customers(CustomerId,Name,VehicleData,.....)
CustomerId is the Identity field(Primary Key,autonumber)

Vehicle(CustomerId,Name,VehicleData,...)
customerId column in null in this table

This is just test example.(somewhat similar to my scenario)
Here I am trying to insert all the rows from vehicle table into the Customers table,retrieve the auto populated customerId for each row from the customers table and update the CustomerId in the Vehicle table. The problem is ,there is no unique field to create a join on,hence i have to perform both the operations in the same transaction.

BEGIN TRANSACTION
DECLARE @CustomerID int
insert into Customers(Name,VehicleData)
select Name,VehicleData from Vehicle
SELECT @CustomerID = scope_identity()
Update Vehicle
set Vehicle.CustomerId=@CustomerID
COMMIT

The problem with this is ,for all the rows the Customer Id is the same,Last generated autonumber in the customers table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:02:02

[code]
BEGIN TRANSACTION

DECLARE @INSERTED_CUSTOMERS table
(
Customer_ID int,
VehicleData varchar(8000)
)

INSERT Customers (Name,VehicleData)
OUTPUT INSERTED.CustomerID,INSERTED.VehicleData INTO @INSERTED_CUSTOMERS
SELECT Name,VehicleData from Vehicle

UPDATE v
SET v.CustomerId= i.CustomerID
FROM Vehicle v
INNER JOIN @INSERTED_CUSTOMERS i
ON i.VehicleData = v.VehicleData

COMMIT
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-03-02 : 12:04:56

can't you do the following, remove the scope_identity select:

Update V
set V.CustomerId=C.CustomerID
From Vehicle V Inner Join Customer C
On V.Name=C.Name And V.VehicleData=C.VehicleDate

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:09:46

I cant understand why you need to include vehicle information in your customer masters table though!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DesiGal
Starting Member

31 Posts

Posted - 2010-03-02 : 12:24:57

@visakh16

This is just a test example.My database is lot different. This is a simplified version of what I am looking for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 12:28:52

Ok . Otherwise it doesnt make any sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DesiGal
Starting Member

31 Posts

Posted - 2010-03-02 : 13:04:25

visakh16

Yes I know.The problem with this query is that
Vehicle(CustomerId,Name,VehicleData,...)
There is no guarantee that (Name,VehicleData)columns in the Vehicle table are unique.It does insert the row in the Customers table even if the combination(Name,VehicleData) is repeated,but it does not update the correct customerID in this case

example

Before Insert the vehicle table is
CustomerId Name VehicleData
null abc testData
null xyz testData2
null xyz testData2

Customers table is empty

After insert customer table contains
CustomerId Name VehicleData
1 abc testData
2 xyz testData2
3 xyz testData2

vehicle table should be
CustomerId Name VehicleData
1 abc testData
2 xyz testData2
3 xyz testData2

i get the following for the Vehicle table

CustomerId Name VehicleData
1 abc testData
2 xyz testData2
2 xyz testData2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 13:06:14

dont you have any unique valued column in vehicle table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DesiGal
Starting Member

31 Posts

Posted - 2010-03-02 : 13:13:56

nope,that's the problem.Every time I insert a customer...vehicle row gets inserted..What I am trying to do here is not normal data insertion I am basically trying to import data from other table and hence this trouble to sync the data

DesiGal
Starting Member

31 Posts

Posted - 2010-03-02 : 13:45:43

I am looking for something similar to foreach row added in Customers table from the vehicle table update its customerID(In the vehicle table) to the newly generated autonumber in the customers table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources