A little help with a query? :-)

Hi everyone,
I'm hoping someone could spare a moment to help me with the following:
I have a table with a forename, surname, username and password column.
There are about 18000 rows in this table.
I would like to copy the value from the forename into the username column
and the surname into the password column for each row
I'm sure this can be done using TSQL or perhaps even straight SQL but I havent
a clue how to do it.
If anyone could advise I would greatly appreciate
Kindest Regards and Thanks in Advance
tce
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
CREATE TABLE Foobar
(username VARCHAR(15) NOT NULL PRIMARY KEY,
password VARCHAR(15) NOT NULL,
forename VARCHAR(15) NOT NULL,
surname VARCHAR(15) NOT NULL);
[vbcol=seagreen]
UPDATE Foobar
SET user_name = fore_name,
password = sur_name;
Now, this will blow up if two or more people have the same name.
|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124738964.888566.304500@.g47g2000cwa.googlegr oups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> CREATE TABLE Foobar
> (username VARCHAR(15) NOT NULL PRIMARY KEY,
> password VARCHAR(15) NOT NULL,
> forename VARCHAR(15) NOT NULL,
> surname VARCHAR(15) NOT NULL);
>
> UPDATE Foobar
> SET user_name = fore_name,
> password = sur_name;
> Now, this will blow up if two or more people have the same name.
>

A little help with a query? :-)

Hi everyone,
I'm hoping someone could spare a moment to help me with the following:
I have a table with a forename, surname, username and password column.
There are about 18000 rows in this table.
I would like to copy the value from the forename into the username column
and the surname into the password column for each row
I'm sure this can be done using TSQL or perhaps even straight SQL but I havent
a clue how to do it.
If anyone could advise I would greatly appreciate
Kindest Regards and Thanks in Advance
tcePlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
CREATE TABLE Foobar
(username VARCHAR(15) NOT NULL PRIMARY KEY,
password VARCHAR(15) NOT NULL,
forename VARCHAR(15) NOT NULL,
surname VARCHAR(15) NOT NULL);
>> I would like to copy the value from the forename into the username column and the surname into the password column for each row <<
UPDATE Foobar
SET user_name = fore_name,
password = sur_name;
Now, this will blow up if two or more people have the same name.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124738964.888566.304500@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> CREATE TABLE Foobar
> (username VARCHAR(15) NOT NULL PRIMARY KEY,
> password VARCHAR(15) NOT NULL,
> forename VARCHAR(15) NOT NULL,
> surname VARCHAR(15) NOT NULL);
>> I would like to copy the value from the forename into the username
>> column and the surname into the password column for each row <<
> UPDATE Foobar
> SET user_name = fore_name,
> password = sur_name;
> Now, this will blow up if two or more people have the same name.
>

A little help with a query? :-)

Hi everyone,
I'm hoping someone could spare a moment to help me with the following:
I have a table with a forename, surname, username and password column.
There are about 18000 rows in this table.
I would like to copy the value from the forename into the username column
and the surname into the password column for each row
I'm sure this can be done using TSQL or perhaps even straight SQL but I have
nt
a clue how to do it.
If anyone could advise I would greatly appreciate
Kindest Regards and Thanks in Advance
tcePlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
CREATE TABLE Foobar
(username VARCHAR(15) NOT NULL PRIMARY KEY,
password VARCHAR(15) NOT NULL,
forename VARCHAR(15) NOT NULL,
surname VARCHAR(15) NOT NULL);
UPDATE Foobar
SET user_name = fore_name,
password = sur_name;
Now, this will blow up if two or more people have the same name.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124738964.888566.304500@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> CREATE TABLE Foobar
> (username VARCHAR(15) NOT NULL PRIMARY KEY,
> password VARCHAR(15) NOT NULL,
> forename VARCHAR(15) NOT NULL,
> surname VARCHAR(15) NOT NULL);
>
> UPDATE Foobar
> SET user_name = fore_name,
> password = sur_name;
> Now, this will blow up if two or more people have the same name.
>

A little help with a query? :-)

Hi everyone,
I'm hoping someone could spare a moment to help me with the following:
I have a table with a forename, surname, username and password column.
There are about 18000 rows in this table.
I would like to copy the value from the forename into the username column
and the surname into the password column for each row
I'm sure this can be done using TSQL or perhaps even straight SQL but I have
nt
a clue how to do it.
If anyone could advise I would greatly appreciate
Kindest Regards and Thanks in Advance
tcePlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
CREATE TABLE Foobar
(username VARCHAR(15) NOT NULL PRIMARY KEY,
password VARCHAR(15) NOT NULL,
forename VARCHAR(15) NOT NULL,
surname VARCHAR(15) NOT NULL);
[vbcol=seagreen]
UPDATE Foobar
SET user_name = fore_name,
password = sur_name;
Now, this will blow up if two or more people have the same name.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124738964.888566.304500@.g47g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> CREATE TABLE Foobar
> (username VARCHAR(15) NOT NULL PRIMARY KEY,
> password VARCHAR(15) NOT NULL,
> forename VARCHAR(15) NOT NULL,
> surname VARCHAR(15) NOT NULL);
>
> UPDATE Foobar
> SET user_name = fore_name,
> password = sur_name;
> Now, this will blow up if two or more people have the same name.
>

A Good insert record if non existant Stored Procedure

Can anyone spare me a good SP that ...

checks if the record exists - and if so return the identity.

and if the record does not exist

inserts it and returns the identity.

Mine keep flipping up :) so I rather look at someone else and use theirs.

Thanks in advance.

Create Proc Proc1
(
parameters...,
@.out int = null output
)
as
select @.out = identcolumn from table1 where ... (use other parameters to determine what to look for)
if @.out is not null
 return
insert into table1 (field list)
values (parameters)
set @.out = @.@.identity
return

You could use EXISTS, but in this case you want the identity column. So, if you use EXISTS, you'd have to do a second query to get the identity. Just do one query, if it's there send back the ID, if not, insert it and send back the ID. You could add another output parameter if want to indicate whether you inserted or not.|||Its the same thing I was doing - somehow mine keeps tripping up. Anyway, I was hoping maybe there was a more efficient means aside from two queries.

Thanks for the insight!|||you could combine the insert with the (not) exists into one batch, but really what's the point? It a bit like those mad people that write whole programs in C on one line.

a few questions on the tuning advisor

i decided to give the tuning advisor a go, just as I had some spare time. I ran profiler against a database for about a half hour, then used the resulting trace file as the input for the tuining advisor.. A lot of the sprocs called in the trace file cannot be executed again,as they will cause issues with data integrity and if someone tries to execute them , an error is produced detailing primary key violations. the error tuning advisor actually shows is this:

"51% of consumed workload had syntax errors. Check tuning log for more information".

My question is though:

1. For tuning advisor to be effective, does the code/trace its analysing have to be valid, or will the now invalid data in my trace file work?

2. Does the tuining advisor actually run the sql against the database, effectively performing changes to data, or just run an execution plan of the query?

3. is the error i recieve expected or something im doing wrong?

Thanks all for any answers.

What I have observed while running DTA is that all of the recommendation that you receive at the end are actually have been tried by DTA. All of the indexes/stats were actually created and your procedure execution plans were generated without any data modification to see the performance difference. That is how you get the matrics of before and after implementing recommended changes but for sure no data modification takes place whatsoever.

Receiving error message during tuning is expected and it should not be an issue of concern.