|
|
Posted by pbd22 on 06/05/07 00:58
On Jun 4, 3:21 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> The syntax error is because of the commas in the WHERE clause. The
> conditions in the WHERE clause are logical expressions and you have to use
> AND or OR between expressions based on what you need to filter. A trimmed
> down example is:
>
> INSERT INTO Table1
> (column1,
> column2)
> SELECT column1,
> column2
> FROM Table2
> WHERE column1 = @column1
> AND column2 = @column2
>
> All that said, I am a bit puzzled why you decided to write this stored
> procedure and the purpose of passing those column parameters. If you just
> need to copy the Table2 to Table1, then directly run the statement like
> this:
>
> INSERT INTO Table1
> (column1,
> column2,
> -- ... the rest of the columns go here
> column17)
> SELECT column1,
> column2,
> -- ... the rest of the columns go here
> column17
> FROM Table2
>
> And then if you have any filters that you need to apply to the columns from
> Table2, you can add the WHERE clause. Also, you could wrap that statement in
> a stored procedure, but I just do not see the purpose of passing all those
> column parameters to the SP. Can you explain why you added them and how you
> plan to execute the SP, and maybe an example of what parameters you pass?
>
> If you are trying to perform something like dynamic searching (that is
> filter on multiple variable conditions), then you may want to read Erland
> Sommarskog's article on dynamic search conditions:http://www.sommarskog.se/dyn-search.html
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
Hi Plamen,
Thanks - you have been a ton of help.
OK, the situation is that I have a page that is a "click-back" from
a registration page. The user finds the code in his inbox and pastes
it in his http:// box for registration confirmation - you know the
deal.
Once that happens, the code I have been writing moves the data
the user input for registration from a temp table to the official
registered
users table. This is what we have been discussing in this thread.
So, all the values are registration values (reg date, firstName,
lastName,
city, state, zip code, security question, security answer, etc). There
is no identity column in common because the userID identity column
in the destination table will automatically increment upon insertion.
As for the SPROC decision, I decided to use a SPROC because of the
the size of the SQL statement - i thought it was a bit lengthly and
involved
so, i figured it turn it into a SPROC. I am guessing this is a poor
reason
to create a SPROC... maybe you could tell me when is the best time to
use them? I am kind of learning as I go.
Anyway, below is the original statement (inside the SqlConnection
statement). It works when I run it in SQL Express. Let me know if you
think it is more forgiving to do it this way.
Thanks again for your help.
MyConn As New
SqlConnection(ConfigurationManager.ConnectionStrings("myConnStr").ConnectionString)
Dim MyCmd As New SqlCommand("INSERT INTO Users (regdate, pass, role,
squestion, sanswer, zcode, altemail, email, bdaymonth, bdayday,
bdayyear, gender, sitename, city, state, country, lastName, firstName)
SELECT regdate, pass, role, squestion, sanswer, zcode, altemail,
email, bdaymonth, bdayday, bdayyear, gender, sitename, city, state,
country, lastName, firstName FROM TempRegistration t WHERE t.confirm
= '8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077'", MyConn)
Navigation:
[Reply to this message]
|