copy data from 1 tbl to another tbl

i have a claims_tbl that i am inserting into with claim information, the tbl contains a claim_seq number that i want to copy over to a user_info_tbl at the same time insert user information below is what i have tried with no luck

insert into user_info(claim_seq)
select Max(claim_seq)
from claim_tbl

update user_info
set lname =upper(@.lname), fname =upper(@.fname), mname =upper(@.mname), personnel = @.personnel, p_position =upper(@.position)
where claim_seq = (Select Max(claim_seq) from user_info)

i am getting duplicate rows of information ........can someone help

thanks

I think you only need an INSERT statement. Perhaps your INSERT statement should look more like this?

INSERT INTO
user_info
(
claim_seq,
lname,
fname,
mname,
personnel,
p_position
)
SELECT
MAX(claim_seq),
upper(@.lname),
upper(@.fname),
upper(@.mname),
@.personnel,
upper(@.position)
FROM
claim_tbl
WHERE
NOT EXISTS (SELECT claim_seq FROM user_info WHERE claim_tbl.claim_seq = user_info.claim_seq)

This will only insert claim_seq values which do not already exists inthe user_info table, and will insert your parameter values at the sametime as the claim_seq.
|||

thanks for the help

the solution i use is below:

Select @.temp_seq = Max(incident_seq) from temp_id

insert into user_info(temp_seq, lname, fname, mname, personnel)
values(@.temp_seq, @.lname, @.fname,@.mname,@.personnel,)

Copy data between DB with DTS

Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.Lookup Data Driven Query task in SQL BOL. It will allow you to check for the existence of a record on the target server and skip that record when processing. It's a little complex to use, but it works well.

One more caution, since it is a script-driven task, you may experience performance issues with very large record sets.

You may also consider using straight T-SQL and not use DTS at all. Even if the two databases are are separate servers, you can link them and use T-SQL.

Regards,

Hugh Scott

Originally posted by jrundber
Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.|||You can use query statement to copy the data into db2.

Originally posted by jrundber
Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.|||Originally posted by jrundber
Hi,

My problem is regarding copy data between two databases with an DTS package.
I have two DB, DB1 and DB2 and they both contains a tbl
named companies. Every week new companies is added two DB1
and I want a DTS package to copy that new data to DB2.
The problem is that I only wants to copy new data, otherwise I get a primary key violation. You can't add a company with the same key as another.

Hi,

i can recommend a tool for this problem:
http://www.sqlscripter.com

Regards
Thomas

Converting varchar to Money

I have a a Case statement which sometimes fails when converting a Varchar
column which contains numeric values to Money. I can understand why it fail
s
when "1.05E+07" is passed in, but other values appear to fail also. I have
not located the other offending values yet (500,000 rows to sift through)
but converting them to Float first avoids the errors. Any ideas?
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
"Server: Msg 235, Level 16, State 1, Line 1
Cannot convert a char value to money. The char value has incorrect syntax."
However the following code, which converts to Float, and then to Money, does
not fail.
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
CONVERT(FLOAT,c1_PriorSalePrice) )(a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
the contents can be converted to any numeric type. See
http://www.aspfaq.com/2390 for the long-winded version of this.
(b) if using convert(money, convert(float())) works, then what is wrong with
using that?
(c) STOP STORING NUMERIC VALUES AS STRINGS!
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>I have a a Case statement which sometimes fails when converting a Varchar
> column which contains numeric values to Money. I can understand why it
> fails
> when "1.05E+07" is passed in, but other values appear to fail also. I
> have
> not located the other offending values yet (500,000 rows to sift through)
> but converting them to Float first avoids the errors. Any ideas?
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
> "Server: Msg 235, Level 16, State 1, Line 1
> Cannot convert a char value to money. The char value has incorrect
> syntax."
> However the following code, which converts to Float, and then to Money,
> does
> not fail.
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
> CONVERT(FLOAT,c1_PriorSalePrice) )
>
>|||Brother AAron,
The data in question is being provided in bulk by an outside vendor, so I
have no choice in how the data is provided or in what format. I have never
seen this situation before and it may have implications for other procedures
.
I will go read the link you provided.
Thanks
Michael
"Aaron Bertrand [SQL Server MVP]" wrote:

> (a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
> the contents can be converted to any numeric type. See
> http://www.aspfaq.com/2390 for the long-winded version of this.
> (b) if using convert(money, convert(float())) works, then what is wrong wi
th
> using that?
> (c) STOP STORING NUMERIC VALUES AS STRINGS!
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>
>

Converting table date

I am trying to to take data from a table where the Row data contains 24
columns representing hors 12 midnight to 11 PM see (sample below) into a
table that puts 1 hour per row?
Initial table
Name Date 12Min 1Min 2Min 3Min … 11Min
Mickey 12/2/05 625.4 153.2 84635…
Convbert to another table
Name Date Min
Mickey 12/2/05 00:00:00 625.4
Mickey 12/2/05 01:00:00 153.2
Mickey 12/2/05 02:00:00 84635Use unions.
e.g. (note: while you don't need to dateadd(hour,0,[Date]), but this
will work if the datatype is datetime or varchar)
select Name, dateadd(hour, 0, [Date]) as [Date], 12Min as Min from yourtable
union all
select Name, dateadd(hour, 1, [Date]) as [Date], 1Min as Min from yourtable
union all
...
union all
select Name, dateadd(hour, 11, [Date]) as [Date], 11Min as Min from
yourtable
Jim Abel wrote:
> I am trying to to take data from a table where the Row data contains 24
> columns representing hors 12 midnight to 11 PM see (sample below) into a
> table that puts 1 hour per row?
> Initial table
> Name Date 12Min 1Min 2Min 3Min … 11Min
> Mickey 12/2/05 625.4 153.2 84635…
> Convbert to another table
> Name Date Min
> Mickey 12/2/05 00:00:00 625.4
> Mickey 12/2/05 01:00:00 153.2
> Mickey 12/2/05 02:00:00 84635
>

converting rows into columns when the count is not known

i have 2 rows with 3 columns each. ( each rows has a colun called ScoreType which contains different data like Performancelevel,scaledscore

Subject Marks ScoreType
A 2 Performancelevel
A 123 scaledscore
B 4 Performancelevel
B 678 scaledscore

i want this data as

Subject PerformanceLevel ScaledScore
A 123 2
B 678 4

how can i acheive this, Please not that the number of scoretypes is not constant, could be more also

Quote:

Originally Posted by praneethraj

i have 2 rows with 3 columns each. ( each rows has a colun called ScoreType which contains different data like Performancelevel,scaledscore

how can i acheive this, Please not that the number of scoretypes is not constant, could be more also

you didn't point your Database versions. anyway, you can use Cross Tab query.

converting nvarchar to Minutes:Seconds in MS SQL?

I have a field in nvarchar type. It contains data like 0, :23, 1:57, ... all in minutes and seconds. Now, I need to convert it to MM:SS using query and get the Average of this column. How can I do it? I have tried Avg(Convert(nvarchar(20), [Calling Time], 108)) .. but I got error :The average aggregateoperation cannot take a nvarchar data type as an argument.

Help!!!! :(

You need to convert the minutes and seconds into some decimals which can be used in Avg function. Let's say you want to get Avg seconds, then you may try:

SELECT AvgSeconds= AVG(CASE WHEN CHARINDEX(':',[Calling Time])=0
THEN CONVERT(DECIMAL(4,2),[Calling Time])*60
WHEN CHARINDEX(':',[Calling Time])=1
THEN CONVERT(DECIMAL(4,2),RIGHT([Calling Time],LEN([Calling Time])-1))
ELSE CONVERT(DECIMAL(4,2),SUBSTRING([Calling Time],1,CHARINDEX(':',[Calling Time])-1))*60
+CONVERT(DECIMAL(4,2),SUBSTRING([Calling Time],CHARINDEX(':',[Calling Time])+1,LEN([Calling Time])))
END)
FROM test_CntTime