copy data from one table to another with addition insert value

Hi,

I was wondering if you can help.

In my vb.net form I am running a query to insert data from one database table to another.

However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command.

I have managed to use @.@. identity to get the id of my first sql insert statement

but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @.@.identity value to my second sql statement.

My second sql statement is a follows:

sql2 ="INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" &" select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId

I hope you can help

Cheers

Mark :)

If you need the ID of the first insert, for the second query then you cannot do a batch insert. There are ways to be a little creative such as doing the 2 inserts separately and doing a batch update on the second query. It depends on your data though.

|||

Thanks Dinakar,

Yes i need the id of the first insert for the second query.

As I need to alter one field value in the select data I am copying from do you think I will need to do a batch update after the second query. Is there no way of doing the insert,select and batch update all on the second query?

Many thanks

Mark :)

|||

markbpriv:

Hi,

I was wondering if you can help.

In my vb.net form I am running a query to insert data from one database table to another.

However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command.

I have managed to use @.@. identity to get the id of my first sql insert statement

but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @.@.identity value to my second sql statement.

My second sql statement is a follows:

sql2 ="INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" &" select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId

I hope you can help

Cheers

Mark :)

Do your first insert, then get the identity, then do your second insert.

Example:

declare myIdentityintINSERT INTO Table1 (col1, col2)VALUES ('test1','test2')SET @.myIdentity =@.@.IDENTITYINSERT INTO Table2SELECT myIdentity ,'test','another test'

Good luck.

|||

There is a way you can trick the SQL Server. Do you have any column in the first table that has any column which has a column like "userid" or "username" that you hardcode from your source data? or any column that has a fixed value across all rows?

|||

Hi,

Thanks for your help.

I keep getting the following error when trying to do @.@.identity

Could not find stored procedure 'False'.

Any idea why?

Cheers

Mark :)

|||

markbpriv:

Hi,

Thanks for your help.

I keep getting the following error when trying to do @.@.identity

Could not find stored procedure 'False'.

Any idea why?

Cheers

Mark :)

Please chekout the seconf post here:http://www.aspspider.com/rss/Rss19894.aspx

If not help:
Make sure the stored procedure is exists in your database.
Execute the SP with the owner name: MyName.MyStoredProcedureName

I guess, you are concatenating a value to a SELECT statment maybe and that value is getting you False.

I found this link which support my guess:http://p2p.wrox.com/topic.asp?TOPIC_ID=1773

Please let me know if this help you or not.

Good luck.