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.

coosing more than 1 value in QUERY PARAMETERS Dialogue Box

You know how there is a Query Parameter Dialogue Box in the Data Tab in Reporting services. In other words, if you have a query with a parameter and want to run your query, this dialogue box appears and wants you to enter the value for the parameter. How can I choose more than 1 parameter in Query parameter dialogue box. I mean, I have a SalesPerson parameter in my query, and whenever I enter John as the value for SalesPerson Parameter I am OK. Whenever I enter Bob as the value I am OK too. How can I see the results for both John and Bob?

John, Bob seems not to work

IN (John, Bob) Seems not to work either.

What is the correct syntax.

Pleaseee.(I'm going crazy)

Thank you

This problem would better be solved by viewing these scenarios in the preview tab.

converting varchar to int

Syntax error converting the varchar value '3.1.7.4.3.9.' to a column of data type int...

how to overcome this problem ..

Hello Raj,

What should '3.1.7.4.3.9.' be, ayou wanting to remove the .'s to have it be 317439? Try this:

=cInt(Replace(Fields!Field1.Value, ".", ""))

Jarret

|||when i run the query it throws the error... i have to handle the error in the data tab itself... can you help me how to find the exact field in which the irregular datatype is located ...is there any way to track down the issue...|||

It is clearly an SQL error which cannot be solved in SSRS using expressions.

I'm assuming that your dataset is formed by a stored procedure, not plain SQL text.

The error is because you are trying to put an invalid integer value into a table (temporary table or user table) column which is of INT datatype. If possible, get the SQL text of the stored procedure and check all insert/update statements and all the columns of INT datatype involved in it. Also check If there is an implicit conversion using CONVERT or CAST function.

Shyam

|||can you tell me the problem is with varchar or datetime... because i didnt find any varchar like '3.1.7.4.3.9.'|||

Yes, it is definitely a varchar. You may have to look out for columns in the table that is being used as source to insert/update the value in the table which has a corresponding column of INT datatype.

Shyam

|||thank you shyam .. i will check it out...

converting to numeric (6,3)

Hey guys,

I am trying to achive the following:

Truncate the values 1680.390000000000000000

to numeric (6,3)

All works ok until i hit a value over 1000, (as above)

The code i was using is:

cos1 = 1680.390000000000000000000

CONVERT (NUMERIC(6 , 3), ROUND(COS1, 3))

I have tried a few others, but nothing seems to work..

I get the following error. "Arithmatic Overflow"

Has anyone got any suggestions?

Thanks guys,

Scotty

A numeric(6,3) would only accept a number as large as 999.999 -six digits total, three digits to the right of the decimal.

Your value, 1680.xxxx exceeds the maximum size and creates an overflow condition -resulting in an error.

Perhaps you need a larger number, like maybe, just guessing here, numeric(8,3)?

|||

Brilliant!!!!

thanks vry much Archie.. this has been confusing me all day,, the way they explain it looks to me as 6 to the left, and 3 to the right...

Thanks again

Scotty

Converting to Hex

Hi All,

I'm needing to take a value inputted by a user via html form and convert it to a hex value upon inserting into SQL2000 db. I only need to store the 8 chars after "0x". Is there any T-SQL that can pull this off? CAST or CONVERT? Sorry if this is a silly question and hope I supplied enough info...

Thx,
Mike
Check this post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=386406&SiteID=1) for Umachandar's solution (which are always great!)

converting the varchar value to int

All,
I am running this statement and getting the error below. I am assuming
that the issue is the CLCCHRGE.CCOUNT data type is Varchar and needs to
be converted using a CONVERT clause or a CASE statement but I am not
sure I am on the right path here and if I am then I am not sure of the
syntax.
SELECT CLCHRGE.CCOUNT, CLCHRGE.CPTPRT, CLCHRGE.XCDATE,
CLMSTER.PLNAME, CLMSTER.PFNAME, procdesc, dignosis
FROM CLCHRGE INNER JOIN
CLMATER ON CLCHRGE.CCOUNT = CLMSTER.CCOUNT
WHERE (CLCHRGE.XACDATE BETWEEN '2005-01-01' AND '2005-1-31')
AND (CLCHRGE.CPTPINT = '90801') AND CLCHRGE.CCOUNT in (608685, 477078,
608201, 204739)
order by xacdate, plname desc
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'C171913' to a column of data
type int.Try changing this
CLCHRGE.CCOUNT in (608685, 477078,
608201, 204739)
to this
CLCHRGE.CCOUNT in ('608685', '477078',
'608201', '204739')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Success!, Thank you for the tip!! You are a Lifesaver!

Converting the value to data format

Hello Everyone,

Please guide me in converting the value to date format.

from source i'm getting the value (which is acchally a data value) '20070730'.

I need this value to be in date format '2007/07/30'

Please help me in getting it done.

thank you

Hi

Select Cast('20070730' as datetime)

Beware of collation configured in your SQL instance and your database

Jean-Pierre

|||

Sorry! I had posted my question in the wrong path.

i wanted this to be done thru Integration services.

Converting the string value to data format

Hello Everyone,

Please guide me in converting the value to date format.

from source i'm getting the value (which is acchally a data value) '20070730'.

I need this value to be in date format '2007/07/30'

Please help me in getting it done.

thank you

Hi

Code Snippet

select cast('20070730' as datetime)

Note : beware of collation used in your SQL instance or your database.

--

Jean-Pierre

|||

THANK YOU FOR YOUR REPLY.

bUT MY SOURCE IS FALAT FILE

|||

HI, you van use a derived column with the following expression:

(DT_DBTIMESTAMP)(SUBSTRING(Column,1,4) + "-" + SUBSTRING(Column,5,2) + "-" + SUBSTRING(Column,7,2))

HTH,

Ccote

Converting sp outcome

Hi all,

i have an stored procedure that returns a value, i want to convert that value into smallmoney, is this possible and i yes how?

here is my code:

CREATE proc CP_avgloss_total
@.mID varchar(10),
@.startdate datetime,
@.enddate datetime

as
select

case

((sum(playtime))/ 3600)
when 0
then 0
else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
end avgloss
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)

GO

The value that needs to convert is avgloss

Hope someone can help me with this.
Cheers Wimselect cast(P1.avgloss as smallmoney) as avgloss from

(select

case

((sum(playtime))/ 3600)
when 0
then 0
else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
end avgloss
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)) P1|||Originally posted by marp
select cast(P1.avgloss as smallmoney) as avgloss from

(select

case

((sum(playtime))/ 3600)
when 0
then 0
else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
end avgloss
from dbo.total
where
machineID = @.mID
and convert(varchar,njdate,121)
between convert(varchar,@.startdate,121)
and convert(varchar,@.enddate,121)) P1

It worked thanx but i still get as outcome 4 digit behind the break
Do you know how to get 2 digits?

Thanx|||As far as I know, the smallmoney data type uses by default 4 decimal digits. If different number of decimal places are needed, "Books Online" recommends using the Decimal data type.|||Originally posted by marp
As far as I know, the smallmoney data type uses by default 4 decimal digits. If different number of decimal places are needed, "Books Online" recommends using the Decimal data type.

Thanx Man this is everything i need.
Your help was fantastic!
Kind regards Wim

Converting problem (Stored Procedure)

I need to link together string (NT-) and integer variable (1). The value of variable2 should be NT-1. I always got such an error message:

Conversion failed when converting the varchar value 'NT-1' to data type int.

DECLARE @.variable1 int;
SET @.variable1 = 1;

DECLARE @.variable2 varchar(10);
SET @.variable2 = CONVERT(varchar, 'NT-1')+ CONVERT(varchar, @.variable1);

i tried the code that you posted and do not encounter the conversion error.

However, you can simply the code and remove the convert for 'NT-1' as it is a string so you do not need to convert to varchar. And for the @.variable1, you might want to specify the varchar size or it will defaulted to a certain size. (cannot remember what is the default size)
SET @.variable2 = 'NT-1' + CONVERT(varchar(10), @.variable1)

Converting numeric values to String in MDX

Is it possible to convert a numeric value to a string using MDX functions? I am defining a goal for my KPI and i need to display two values for the goal, (a percentage value like 75% and an actual value e.g. ￡200K) so i was thinking of converting both values to a string and then concatenate the two strings for the KPI goal. Is it possible to do this OR is there a better way of doing it?

Thanks in advance.

I'm not sure if this is the best way of handling the issue but it is technically possible. Here is a code sample illustrating one string assembly:

Code Snippet

with member [Measures].[Category Sales String] as

"*** " + CSTR([Measures].[Reseller Sales Amount].Value) + " ***"

select

[Measures].[Category Sales String] on 0,

[Product].[Category].Memberson 1

from [Adventure Works]

|||

Yes, certainly - just use the Cstr function (which is, by the way, native MDX although it behaves the same as the VBA function of the same name - see http://www.e-tservice.com/Files/vba_functions_in_as2005.doc) to cast the values to a string. For example:

Code Snippet

with member measures.test as

cstr([Measures].[Internet Sales Amount]) + " Hello!"

select measures.test on 0 from

[Adventure Works]

However, I'm not sure that concatenating these values is really a good idea. Why not create an extra dimension, or an attribute on a dimension, which has one real member and one calculated member and then allow the users to switch between slicing these members to display actual values and percentages? This is what's referred to as a shell dimension or a time utility dimension, and you can find out more about how to implement one of these here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

HTH,

Chris

|||Thanks a lot this is a real life saver for me. I want to concatenate because, I am displaying these values on a dashboard and i just want to display everything on the same screen.

Converting negative value

Hi!

I have a large table width values like this:

000024634300
000-37500000
002783868891
000009603857
000-60000000
000001672396
000000195200
000010315112
000017000000

I need to convert the numbers into an integer-type field. Is this
possible with just native sql-commands? I don't want to make an
active-x script because it will be very slow on the large table.

EspenSELECT col,
CASE WHEN col LIKE '%-%' THEN -1 ELSE 1 END*
CAST(REPLACE(col,'-','') AS NUMERIC(12))
FROM SomeTable

The value 2,783,868,891 exceeds the maximum for an INTEGER column so I've
used NUMERIC here. You could also use BIGINT.

--
David Portas
SQL Server MVP
--