Create Stored Procedure help ...

Hello Every1,
I'm trying to create a stored procedure which will do the following.
- Look at the table to determine if a customer has a duplicate value in
a column.
- If Yes, then replace the duplicate with the highest # for that column
for that particular customer.
- Loop through to check & update all customers.
I was reading SQL Server Books, but couldn't find any help.
Any help or suggestions would be highly appreciated.
Thanks
I don't understand what you want to do. Can you show share sample data
"Tony Schplik" wrote:

> Hello Every1,
> I'm trying to create a stored procedure which will do the following.
> - Look at the table to determine if a customer has a duplicate value in
> a column.
> - If Yes, then replace the duplicate with the highest # for that column
> for that particular customer.
> - Loop through to check & update all customers.
> I was reading SQL Server Books, but couldn't find any help.
> Any help or suggestions would be highly appreciated.
>
> Thanks
>
|||Thanks Arun for your quick reply.
Here is the sample of the data.
CUST_ID SEQ_NUM
6000010135 2
6000010135 1
6000010135 1
6000010135 2
6000010135 5
6000020512 1
6000020512 1
6000020512 1
6000020512 4
6000020512 4
6000020512 6
Hope this will give you a better picture. As you can see from the
sample data that I have customer with same SEQ_NUM, which is causing
problem.
What I have to do is to replace the next same SEQ_NUM for the same
customer with the highest.
So for customer '6000010135' after the update in the table in the
SEQ_NUM column I should have the following values.
2
1
6
7
5
Thanks in advance for your help
|||Does SEQ_NO have any implicit meaning in the data? Can we set any values to
this field as long as they are unique?
if so then it is very easy. Just create a temp table
(seq_no int identity(1,1), customerid int) and do this:
insert temp(customerid) select customerid from my_table order by customerid
One problem here is that seq_no field here will not reset to 1 when a new
customer id starts. So your seq_no will be ever increasing. May not be an
issue if your seq_no field does not have any contextual significance.
Anothe easy way is to use a cursor to go over
select customerid, seq_no from mytable order by customerid, seq_no
and iterate through the records. remember the last pair processed. If this
pair is same, update seq_no with max + 1
There should be a set based solution here too. But i have not figured it out
still.
"Tony Schplik" wrote:

> Thanks Arun for your quick reply.
> Here is the sample of the data.
> CUST_ID SEQ_NUM
> 6000010135 2
> 6000010135 1
> 6000010135 1
> 6000010135 2
> 6000010135 5
> 6000020512 1
> 6000020512 1
> 6000020512 1
> 6000020512 4
> 6000020512 4
> 6000020512 6
> Hope this will give you a better picture. As you can see from the
> sample data that I have customer with same SEQ_NUM, which is causing
> problem.
> What I have to do is to replace the next same SEQ_NUM for the same
> customer with the highest.
> So for customer '6000010135' after the update in the table in the
> SEQ_NUM column I should have the following values.
> 2
> 1
> 6
> 7
> 5
> Thanks in advance for your help
>

Create Stored Procedure help ...

Hello Every1,
I'm trying to create a stored procedure which will do the following.
- Look at the table to determine if a customer has a duplicate value in
a column.
- If Yes, then replace the duplicate with the highest # for that column
for that particular customer.
- Loop through to check & update all customers.
I was reading SQL Server Books, but couldn't find any help.
Any help or suggestions would be highly appreciated.
ThanksI don't understand what you want to do. Can you show share sample data
"Tony Schplik" wrote:

> Hello Every1,
> I'm trying to create a stored procedure which will do the following.
> - Look at the table to determine if a customer has a duplicate value in
> a column.
> - If Yes, then replace the duplicate with the highest # for that column
> for that particular customer.
> - Loop through to check & update all customers.
> I was reading SQL Server Books, but couldn't find any help.
> Any help or suggestions would be highly appreciated.
>
> Thanks
>|||Thanks Arun for your quick reply.
Here is the sample of the data.
CUST_ID SEQ_NUM
6000010135 2
6000010135 1
6000010135 1
6000010135 2
6000010135 5
6000020512 1
6000020512 1
6000020512 1
6000020512 4
6000020512 4
6000020512 6
Hope this will give you a better picture. As you can see from the
sample data that I have customer with same SEQ_NUM, which is causing
problem.
What I have to do is to replace the next same SEQ_NUM for the same
customer with the highest.
So for customer '6000010135' after the update in the table in the
SEQ_NUM column I should have the following values.
2
1
6
7
5
Thanks in advance for your help|||Does SEQ_NO have any implicit meaning in the data? Can we set any values to
this field as long as they are unique?
if so then it is very easy. Just create a temp table
(seq_no int identity(1,1), customerid int) and do this:
insert temp(customerid) select customerid from my_table order by customerid
One problem here is that seq_no field here will not reset to 1 when a new
customer id starts. So your seq_no will be ever increasing. May not be an
issue if your seq_no field does not have any contextual significance.
Anothe easy way is to use a cursor to go over
select customerid, seq_no from mytable order by customerid, seq_no
and iterate through the records. remember the last pair processed. If this
pair is same, update seq_no with max + 1
There should be a set based solution here too. But i have not figured it out
still.
"Tony Schplik" wrote:

> Thanks Arun for your quick reply.
> Here is the sample of the data.
> CUST_ID SEQ_NUM
> 6000010135 2
> 6000010135 1
> 6000010135 1
> 6000010135 2
> 6000010135 5
> 6000020512 1
> 6000020512 1
> 6000020512 1
> 6000020512 4
> 6000020512 4
> 6000020512 6
> Hope this will give you a better picture. As you can see from the
> sample data that I have customer with same SEQ_NUM, which is causing
> problem.
> What I have to do is to replace the next same SEQ_NUM for the same
> customer with the highest.
> So for customer '6000010135' after the update in the table in the
> SEQ_NUM column I should have the following values.
> 2
> 1
> 6
> 7
> 5
> Thanks in advance for your help
>

CREATE script that filters out empty columns

All:
Say I need to duplicate a table, but the CREATE script must only
include those columns of the table where the value in ALL the available
rows is not null. For an ad-hoc exercise (one or two tables), this is
easy, but for duplicating, say, 90 tables with the empty columns
filtered out, I assume I need an SP that uses each table's metadata to
test each column individually, for each table. A temp table could then
keep the name of those non-empty columns, and the script would
recreate the new table's script from the resulting set.
If someone can suggest a script to do this, I'll be more than happy...BTW, I do know that information_schema.columns is involved... I know
what the logic should be, I simply don't know how to translate that
logic into T-SQL well enough to be efficient... and maybe VB.NET should
be involved, instead should be something like:
(code to write the beginning of the CREATE TABLE statement, plus the
first bracket)
For all tables in the database
For each column in current_table
SELECT DISTINCT (current_column) , COUNT(*) FROM current_table
GROUP BY (current_column)
If (COUNT(*) >= 1 AND (individual value in the column) <> NULL
then /* This implies that the only value there is not NULL */
(write the name of current_column to a file, plus its data
type and width, and a
comma if not the last column)
end if
next column
next table
(write the closing bracket)
Any suggestions?

Create Duplicate SQL Server with Databases

Hi,
Is there a way to copy a SQL Server along with its databases and all other
configuration to other servers? Please keep in mind that all the other
servers are in different Active Directory/domains.
Thank you.Hi,
Thank you for your reply.
Reinstalling SQL is what I would like to avoid. I have to create SQL/Win2K
servers on a regular basis for our clients. I was trying to find a way so I
can simply ghost the server. The base configuration is the same for all our
server so the database do not change until it's been installed. Even then,
only data changes, not the DBs themselves.
Thank you.
"Stressed" <k@.c.co.uk> wrote in message
news:%23I5LeawYDHA.652@.TK2MSFTNGP10.phx.gbl...
> You may be able to install a new copy of sql server, so all the
> settings/registry keys are up to scratch on the new server, and
> then copy everything to the new server, so long as the other server
> has the same configuration and disk letters.
> I've not tried this, though, HTH.
>
> "Dragon" <nopam_baadil@.hotmail.com> wrote in message
> news:eK9VI4oYDHA.2448@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Is there a way to copy a SQL Server along with its databases and all
other
> > configuration to other servers? Please keep in mind that all the other
> > servers are in different Active Directory/domains.
> >
> > Thank you.
> >
> >
>

Create Duplicate of table ?

I want to create a copy of a table with a new name, what is the best way to
do this. I did notice I could copy a table, but there is no paste !?!SELECT * INTO CopyOfMyTable FROM MyTable WHERE 1=2
1=2 means that only table structure will be copied without data. The
downside is that if you create a new table that way
SQL Server will not transfer PK and FK as well as Indexes and Constraints
created on original table
An Identity property SQL Server does transfer.
"p" <p@.p.com> wrote in message
news:OKMLB2k%23FHA.532@.TK2MSFTNGP15.phx.gbl...
>I want to create a copy of a table with a new name, what is the best way to
>do this. I did notice I could copy a table, but there is no paste !?!
>|||SELECT * INTO Newtablename
FROm OldTableName
HTH, jens Suessmeyer.|||You guys are realy great for coming back so quick with an answer. Thanks to
both of you for the replies.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1133865309.548099.228200@.g43g2000cwa.googlegroups.com...
> SELECT * INTO Newtablename
> FROm OldTableName
> HTH, jens Suessmeyer.
>