Marking copied records

I have a big problem now. I need to write a SQL statement to copy some
records from a table to another table. At the process of copying, I want to
update a field in both table. The field is to identify whether the record is
the 1st, 2nd, 3rd, 4th, ... record that I've copied, which will be in runnin
g
sequence. There should be repeating numbers. The reason for doing this is if
a user modifies a record in the new table in the future, I will still know
how it originally was by referring back by that number.
Do you get what I mean? I have no idea whether SQL can do that. And whether
it can be settled in a statement. Can someone help me? Give me some guide?
Thank you.You could do this by adding a DateCopied (datetime -default getdate() )
column to the tables -perhaps even adding a WhoChanged (varchar(50) -default
system_user) column.
Other options include a Sequence (timeStamp datatype) Column.
Either of these choices would allow you to always restructure the sequence
of data changes.
Then you just add a TRIGGER to the primary table to copy the old version to
the archive table whenever there is a data change.
You might google "SQL Server" and "Audit Trail". Here's an article to get
you started:
http://expertanswercenter.techtarge...i980058,00.html
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"wrytat" <wrytat@.discussions.microsoft.com> wrote in message
news:8D4B4729-8170-436E-BE05-D87933758E59@.microsoft.com...
>I have a big problem now. I need to write a SQL statement to copy some
> records from a table to another table. At the process of copying, I want
> to
> update a field in both table. The field is to identify whether the record
> is
> the 1st, 2nd, 3rd, 4th, ... record that I've copied, which will be in
> running
> sequence. There should be repeating numbers. The reason for doing this is
> if
> a user modifies a record in the new table in the future, I will still know
> how it originally was by referring back by that number.
> Do you get what I mean? I have no idea whether SQL can do that. And
> whether
> it can be settled in a statement. Can someone help me? Give me some guide?
> Thank you.

Mark duplicate records in a Select statement ?

I have a requirement to mark duplicate records when I pull them from the database.

However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one.

The code I have below creates a column called Dupes but marks all the duplicates - including the first one.

Is there a way to only mark the 2nd, 3rd, 4th etc record ?

SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber,

Dupes = (select count(id)
from CallLogs
where (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone)
OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone)
AND DealerId= 'hdsh'
AND CSRStatus IS NULL
and datediff(d, logdate, getdate()) <= 21),

FROM CallLogs cl
left Join CallStatus cs on cs.Id = cl.CSRstatus
left Join CallStatus cs2 on cs2.Id = cl.Customerstatus
Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0)
And DealerId='hdsh'
And Logdate Between '08/01/2007' And '08/31/2007'

Can't you just decrement the 'count' statement by 1 before assigning it to Dupes?

1Dupes = (SELECTcount(id)
2FROM CallLogs
3WHERE(CustomerHomePhone !=''AND cl.CustomerHomePhone = CustomerHomePhone)
4 OR (CustomerBusinessPhone !=''AND cl.CustomerBusinessPhone = CustomerBusinessPhone)
5AND DealerId='hdsh'
6 AND CSRStatusISNULL
7 ANDdatediff(d, logdate,getdate()) <= 21))- 1

That way it just accounts for itself by subtracting 1 from the total 

|||

The problem is that both records get the same Dupe number.

I need the 2nd "dupe" to be marked as 2, the 3rd "dupe" to be marked as 3 etc

Many thanks in advance! - Simple Date function - Please Help!

Hi All,

Does anyone know how to return a date the sql query analyser like (Aug 2, 2004)

Right now, the following statement returns (Aug 2, 2004 8:40PM). This is now good because I need to do a specific date search that doesn't include the time.

Many thanks in advance!!
Brad

--------------
declare @.today DateTime
Select @.today = GetDate()
print @.todaylook at the Convert function...something like...

select convert(varchar, getdate(), 6)

|||Heres a tutorial that I thought was helpful
http://www.easerve.com/developer/tutorials/asp-net-tutorials-dates.aspx

manipulate field value from select statement

Hi all,

any assistance will be much appreciated on this one .... a bit clueless at the mo!

I've been trying to execute the code below in which part of my select statement is a calculated value i.e. Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE

code:

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) & "/" & SUBSTRING([SD],5,2) & "/" & Left([SD],4) AS STARTDATE,
Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE, vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

however this error message keeps coming up at runtime:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ENDDATE'.

My guess is it's happening when I try to get the date difference (DATEDIFF)....

help!!

Try this..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

|||

Sh... should have seen that one.

Cheers mate .. however I'm still having an error from that code:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_spend'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_dates'.

Is there some sort of restriction on selecting from a view in sql server?

|||

Bolugbe wrote:

My guess is it's happening when I try to get the date difference (DATEDIFF)....

You're right, the problem is in the DATEDIFF statement.
You cannot use just assigned aliases in calculations, so you should either copy/paste the formula for getting ENDDATE into DATEDIFF function or use nested select statements|||

Try this one..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), Convert(datetime,Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4))) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

Management Studio: "select *",column names automatically filled in?

I am new to SQL 2005 and can't figgure this out. If I open a table, the
results are displayed. If I then modify the query statement (leaving
select *) and then click on execute, all of the column names are filled
in. Quite annoing. Is there any way to turn this "feature" off and just
leave the "*"?
It would also be nice to have the color coded query designer with an
editable result set, is there any way to do this?
Thanks!Most people don't want the particular feature you request as such
unqualified column fetches do not offer the best performance.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"emde" <emdeusenet@.yahoo.com> wrote in message
news:1160774919.157908.248050@.h48g2000cwc.googlegroups.com...
>I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
>|||Since you are new to SQL, please accept our encouragement to NOT use 'SELECT
*'.
It becomes a crutch because it seems so 'easy', yet over time, it can cause
problems. Too much unnecessary data retrieved (and transmitted), potential
for broken applications when there is a business need to add additional
columns to the table and the application doesn't expect them, etc.
It is a 'Best Practice' to only SELECT the specific columns needed for a
particular purpose.
Welcome to SQL Server, and good luck.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"emde" <emdeusenet@.yahoo.com> wrote in message
news:1160774919.157908.248050@.h48g2000cwc.googlegroups.com...
>I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
>|||emde wrote:
> I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
For reasons of performance and maintainability you should always avoid
using SELECT * in any production-quality code. If you need to do this
for ad-hoc / non-production use you can just save the script as a
query. Click New Query and then save the file.
The fastest way to learn is to ignore the "designer" and "open table"
features and just type your queries directly. The query designer has an
annoying habit of rewriting your queries for you. It also has a lot of
limitations.
--
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
--|||Thanks everyone. I am actually a long time SQL 2000 dba who is finally
making the switch to 2005. I only use select * when trying to debug
apps and make changes to data, etc. Thanks for the insight. I am sure I
will have many questions down the road and it looks like this is a
great group to hang out in.
Take care.

Management Studio: "select *",column names automatically filled in?

I am new to SQL 2005 and can't figgure this out. If I open a table, the
results are displayed. If I then modify the query statement (leaving
select *) and then click on execute, all of the column names are filled
in. Quite annoing. Is there any way to turn this "feature" off and just
leave the "*"?
It would also be nice to have the color coded query designer with an
editable result set, is there any way to do this?
Thanks!Most people don't want the particular feature you request as such
unqualified column fetches do not offer the best performance.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"emde" <emdeusenet@.yahoo.com> wrote in message
news:1160774919.157908.248050@.h48g2000cwc.googlegroups.com...
>I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
>|||Since you are new to SQL, please accept our encouragement to NOT use 'SELECT
*'.
It becomes a crutch because it seems so 'easy', yet over time, it can cause
problems. Too much unnecessary data retrieved (and transmitted), potential
for broken applications when there is a business need to add additional
columns to the table and the application doesn't expect them, etc.
It is a 'Best Practice' to only SELECT the specific columns needed for a
particular purpose.
Welcome to SQL Server, and good luck.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"emde" <emdeusenet@.yahoo.com> wrote in message
news:1160774919.157908.248050@.h48g2000cwc.googlegroups.com...
>I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
>|||emde wrote:
> I am new to SQL 2005 and can't figgure this out. If I open a table, the
> results are displayed. If I then modify the query statement (leaving
> select *) and then click on execute, all of the column names are filled
> in. Quite annoing. Is there any way to turn this "feature" off and just
> leave the "*"?
> It would also be nice to have the color coded query designer with an
> editable result set, is there any way to do this?
> Thanks!
For reasons of performance and maintainability you should always avoid
using SELECT * in any production-quality code. If you need to do this
for ad-hoc / non-production use you can just save the script as a
query. Click New Query and then save the file.
The fastest way to learn is to ignore the "designer" and "open table"
features and just type your queries directly. The query designer has an
annoying habit of rewriting your queries for you. It also has a lot of
limitations.
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
--|||Thanks everyone. I am actually a long time SQL 2000 dba who is finally
making the switch to 2005. I only use select * when trying to debug
apps and make changes to data, etc. Thanks for the insight. I am sure I
will have many questions down the road and it looks like this is a
great group to hang out in.
Take care.