Copy Row Of Data From Table to Table In Same DB
Copy Row Of Data From Table to Table In Same DB
am 04.06.2007 21:19:23 von pbd22
Hi.
Like the title says - how do i do this?
I was given the following example:
INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A'
The above statement threw the following error:
An explicit value for the identity column in table 'TABLE2' can only
be specified when a column list is used and IDENTITY_INSERT is ON.
Then, after filling in all the column names in my above select
statement I kept getting an error to the effect that the number of
source and destination columns don't match. This is because one column
"confirm_hash" does not exist in the destination table, just the
source table.
could somebody show me how to get this to work?
thanks!
PS - MS SQL SERVER EXPRESS 2005
Re: Copy Row Of Data From Table to Table In Same DB
am 04.06.2007 22:54:14 von Plamen Ratchev
The syntax to use INSERT INTO is like this:
INSERT INTO TABLE2
()
SELECT
FROM TABLE1
WHERE COL1 = 'A'
A few brief notes:
- the will list your columns (like COL1, COL2, COL3, etc.)
- the must contain the same number of columns in both clauses
(INSERT INTO and SELECT)
- if you do not specify the in the INSERT INTO clause (as you
did in your sample query), then the in SELECT must much all
columns in TABLE2
- the columns have to be of the same data type and size, being able to
implicitly convert, or explicitly converted via CAST/CONVERT
- in your case if the "confirm_hash" column does not exists in the
destination table, then you have to drop it from the column list (or alter
TABLE2 before the insert to add the column)
- you do not have to list the IDENTITY column as it will get automatically
the value based on the IDENTITY (of if you want to force a value in that
column, run before the query SET IDENTITY_INSERT TABLE2 ON)
If you post your CREATE TABLE statements for both tables, some sample data
and desired results you can get much better help.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Re: Copy Row Of Data From Table to Table In Same DB
am 04.06.2007 23:46:26 von pbd22
On Jun 4, 1:54 pm, "Plamen Ratchev" wrote:
> The syntax to use INSERT INTO is like this:
>
> INSERT INTO TABLE2
> ()
> SELECT
> FROM TABLE1
> WHERE COL1 = 'A'
>
> A few brief notes:
> - the will list your columns (like COL1, COL2, COL3, etc.)
> - the must contain the same number of columns in both clauses
> (INSERT INTO and SELECT)
> - if you do not specify the in the INSERT INTO clause (as you
> did in your sample query), then the in SELECT must much all
> columns in TABLE2
> - the columns have to be of the same data type and size, being able to
> implicitly convert, or explicitly converted via CAST/CONVERT
> - in your case if the "confirm_hash" column does not exists in the
> destination table, then you have to drop it from the column list (or alter
> TABLE2 before the insert to add the column)
> - you do not have to list the IDENTITY column as it will get automatically
> the value based on the IDENTITY (of if you want to force a value in that
> column, run before the query SET IDENTITY_INSERT TABLE2 ON)
>
> If you post your CREATE TABLE statements for both tables, some sample data
> and desired results you can get much better help.
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
Thanks Plamen,
I have followed your directions and that works (tested in QA).
Since the query is now getting kind of detailed, I have decided to
create a stored procedure out of this. I am getting an error:
Msg 102, Level 15, State 1, Procedure sp_MyStoredProcedure, Line 75
Incorrect syntax near ','.
Would you mind telling me why I am getting this error (and checking my
SPROC in general)? One note - I have added a final column (column18)
in my sproc that exists in Table2, but not in Table1.
Thanks, I really appreciate any feedback you can provide.
Peter
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE sp_MyStoredProcedure
@column1 DATETIME = NULL,
@column2 VARCHAR(50) = NULL,
@column3 VARCHAR(50) = NULL,
@column4 VARCHAR(50) = NULL,
@column5 VARCHAR(50) = NULL,
@column6 INT = NULL,
@column7 VARCHAR(50) = NULL,
@column8 VARCHAR(50) = NULL,
@column9 INT = NULL,
@column10 INT = NULL,
@column11 INT = NULL,
@column12 VARCHAR(50) = NULL,
@column13 VARCHAR(50) = NULL,
@column14 VARCHAR(50) = NULL,
@column15 VARCHAR(50) = NULL,
@column16 VARCHAR(50) = NULL,
@column17 VARCHAR(50) = NULL,
@column18 VARCHAR(50) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO Table1
(column1,
column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17)
SELECT column1,
column2,
column3,
column4,
column5,
column6,
column7,
column8,
column9,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17
FROM Table2 t2
WHERE t2.column1 = @column1,
column2 = @column2,
column3 = @column3,
column4 = @column4,
column5 = @column5,
column6 = @column6,
column7 = @column7,
column8 = @column8,
column9 = @column9,
column10 = @column10,
column11 = @column11,
column12 = @column12,
column13 = @column13,
column14 = @column14,
column15 = @column15,
column16 = @column16,
column17 = @column17,
column18 = @column18
END
GO
Re: Copy Row Of Data From Table to Table In Same DB
am 05.06.2007 00:21:42 von Plamen Ratchev
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 Ratchev
http://www.SQLStudio.com
Re: Copy Row Of Data From Table to Table In Same DB
am 05.06.2007 02:58:45 von pbd22
On Jun 4, 3:21 pm, "Plamen Ratchev" 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("myConn Str").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)
Re: Copy Row Of Data From Table to Table In Same DB
am 05.06.2007 05:15:35 von Plamen Ratchev
Ok, now it is more clear what you are trying to do... :)
Yes, stored procedure is best here, as it can reuse previously cached
execution plan. Perhaps something like this:
CREATE PROCEDURE ConfirmUserRegistration
@confirmation_cd NVARCHAR(50)
AS
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
INSERT INTO Users
(egdate,
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
WHERE confirm = @confirmation_cd;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRAN;
END
ELSE IF (XACT_STATE()) = 1
BEGIN
COMMIT TRAN;
END
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT,
@ErrorNumber INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200),
@ErrMessage NVARCHAR(4000);
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = COALESCE(ERROR_PROCEDURE(), '-');
SET @ErrMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+
N'Message: '+ ERROR_MESSAGE();
RAISERROR(
@ErrMessage,
@ErrorSeverity,
1,
@ErrorNumber,
@ErrorSeverity,
@ErrorState,
@ErrorProcedure,
@ErrorLine
);
END CATCH;
GO
Then in your code call the SP like this (I just typed here, please check for
syntax, I've been using more C# lately and could be missing something):
Using connection As New
SqlConnection(ConfigurationManager.ConnectionStrings("myConn Str").ConnectionString)
Try
Dim command As SqlCommand = New SqlCommand( _
"ConfirmUserRegistration",
connection)
command.CommandType = CommandType.StoredProcedure
Dim parameter As SqlParameter = command.Parameters.Add( _
"@confirmation_cd ",
SqlDbType.NVarChar, _
50)
parameter.Value = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077"
command.Connection.Open()
command.ExecuteNonQuery()
Catch exSQL As SqlException
' Log and show error
Catch exGen As Exception
' Log and show error
End Try
End Using
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Re: Copy Row Of Data From Table to Table In Same DB
am 05.06.2007 16:17:15 von pbd22
On Jun 4, 8:15 pm, "Plamen Ratchev" wrote:
> Ok, now it is more clear what you are trying to do... :)
>
> Yes, stored procedure is best here, as it can reuse previously cached
> execution plan. Perhaps something like this:
>
> CREATE PROCEDURE ConfirmUserRegistration
> @confirmation_cd NVARCHAR(50)
> AS
>
> SET NOCOUNT ON;
>
> BEGIN TRY
>
> BEGIN TRAN
>
> INSERT INTO Users
> (egdate,
> 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
> WHERE confirm = @confirmation_cd;
>
> COMMIT TRAN;
>
> END TRY
> BEGIN CATCH
>
> IF (XACT_STATE()) = -1
> BEGIN
> ROLLBACK TRAN;
> END
> ELSE IF (XACT_STATE()) = 1
> BEGIN
> COMMIT TRAN;
> END
>
> DECLARE
> @ErrorMessage NVARCHAR(4000),
> @ErrorSeverity INT,
> @ErrorState INT,
> @ErrorNumber INT,
> @ErrorLine INT,
> @ErrorProcedure NVARCHAR(200),
> @ErrMessage NVARCHAR(4000);
>
> SELECT
> @ErrorMessage = ERROR_MESSAGE(),
> @ErrorSeverity = ERROR_SEVERITY(),
> @ErrorState = ERROR_STATE(),
> @ErrorNumber = ERROR_NUMBER(),
> @ErrorLine = ERROR_LINE(),
> @ErrorProcedure = COALESCE(ERROR_PROCEDURE(), '-');
>
> SET @ErrMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
> +
> N'Message: '+ ERROR_MESSAGE();
>
> RAISERROR(
> @ErrMessage,
> @ErrorSeverity,
> 1,
> @ErrorNumber,
> @ErrorSeverity,
> @ErrorState,
> @ErrorProcedure,
> @ErrorLine
> );
>
> END CATCH;
>
> GO
>
> Then in your code call the SP like this (I just typed here, please check for
> syntax, I've been using more C# lately and could be missing something):
>
> Using connection As New
> SqlConnection(ConfigurationManager.ConnectionStrings("myConn Str").ConnectionString)
>
> Try
> Dim command As SqlCommand = New SqlCommand( _
> "ConfirmUserRegistration",
> connection)
> command.CommandType = CommandType.StoredProcedure
>
> Dim parameter As SqlParameter = command.Parameters.Add( _
> "@confirmation_cd ",
> SqlDbType.NVarChar, _
> 50)
> parameter.Value = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077"
>
> command.Connection.Open()
> command.ExecuteNonQuery()
>
> Catch exSQL As SqlException
> ' Log and show error
>
> Catch exGen As Exception
> ' Log and show error
>
> End Try
>
> End Using
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
Thanks again Plamen. This thread has been very helpful.
I have a final question. How do I handle cases where the confirmation
code
doesn't exist? Say a user is trying to guess a code - How would the
stored procedure catch a mismatch and return the result to VB.NET so
the appropriate message can be sent to the user?
Thanks again for all your help.
Peter
Re: Copy Row Of Data From Table to Table In Same DB
am 05.06.2007 16:50:17 von pbd22
On Jun 5, 7:17 am, pbd22 wrote:
> On Jun 4, 8:15 pm, "Plamen Ratchev" wrote:
>
>
>
> > Ok, now it is more clear what you are trying to do... :)
>
> > Yes, stored procedure is best here, as it can reuse previously cached
> > execution plan. Perhaps something like this:
>
> > CREATE PROCEDURE ConfirmUserRegistration
> > @confirmation_cd NVARCHAR(50)
> > AS
>
> > SET NOCOUNT ON;
>
> > BEGIN TRY
>
> > BEGIN TRAN
>
> > INSERT INTO Users
> > (egdate,
> > 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
> > WHERE confirm = @confirmation_cd;
>
> > COMMIT TRAN;
>
> > END TRY
> > BEGIN CATCH
>
> > IF (XACT_STATE()) = -1
> > BEGIN
> > ROLLBACK TRAN;
> > END
> > ELSE IF (XACT_STATE()) = 1
> > BEGIN
> > COMMIT TRAN;
> > END
>
> > DECLARE
> > @ErrorMessage NVARCHAR(4000),
> > @ErrorSeverity INT,
> > @ErrorState INT,
> > @ErrorNumber INT,
> > @ErrorLine INT,
> > @ErrorProcedure NVARCHAR(200),
> > @ErrMessage NVARCHAR(4000);
>
> > SELECT
> > @ErrorMessage = ERROR_MESSAGE(),
> > @ErrorSeverity = ERROR_SEVERITY(),
> > @ErrorState = ERROR_STATE(),
> > @ErrorNumber = ERROR_NUMBER(),
> > @ErrorLine = ERROR_LINE(),
> > @ErrorProcedure = COALESCE(ERROR_PROCEDURE(), '-');
>
> > SET @ErrMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
> > +
> > N'Message: '+ ERROR_MESSAGE();
>
> > RAISERROR(
> > @ErrMessage,
> > @ErrorSeverity,
> > 1,
> > @ErrorNumber,
> > @ErrorSeverity,
> > @ErrorState,
> > @ErrorProcedure,
> > @ErrorLine
> > );
>
> > END CATCH;
>
> > GO
>
> > Then in your code call the SP like this (I just typed here, please check for
> > syntax, I've been using more C# lately and could be missing something):
>
> > Using connection As New
> > SqlConnection(ConfigurationManager.ConnectionStrings("myConn Str").ConnectionString)
>
> > Try
> > Dim command As SqlCommand = New SqlCommand( _
> > "ConfirmUserRegistration",
> > connection)
> > command.CommandType = CommandType.StoredProcedure
>
> > Dim parameter As SqlParameter = command.Parameters.Add( _
> > "@confirmation_cd ",
> > SqlDbType.NVarChar, _
> > 50)
> > parameter.Value = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a712077"
>
> > command.Connection.Open()
> > command.ExecuteNonQuery()
>
> > Catch exSQL As SqlException
> > ' Log and show error
>
> > Catch exGen As Exception
> > ' Log and show error
>
> > End Try
>
> > End Using
>
> > HTH,
>
> > Plamen Ratchevhttp://www.SQLStudio.com
>
> Thanks again Plamen. This thread has been very helpful.
>
> I have a final question. How do I handle cases where the confirmation
> code
> doesn't exist? Say a user is trying to guess a code - How would the
> stored procedure catch a mismatch and return the result to VB.NET so
> the appropriate message can be sent to the user?
>
> Thanks again for all your help.
> Peter
Actually, I have a bit of an addition to the above "final
question" :) .
I am also wondering where in the SPROC that you have provided
I could place a confirmation that the insert statement has happened
successfully? Or, how do I include a check within the SPROC to
verify successful insertion? I ask because, once the data has been
successfully moved from the Temp table to the Users table, I will need
to delete the source row in the Temp table. I can figure out how to
code the deletion but am not quite sure how the "onSuccess" statement
looks that indicates that it is OK to go ahead and delete the row.
Thanks again!
Re: Copy Row Of Data From Table to Table In Same DB
am 05.06.2007 17:36:46 von Plamen Ratchev
I will try to sketch here the answer to both questions:
1). To detect that the confirmation code exists, you can check the number of
rows affected by the insert (using @@rowcount), and then return that value
to the client using an output parameter. If the number of rows is 1 (I
assume you have either a primary key or UNIQUE constraint on the
confirmation code column so duplicates are not possible), then you know you
had a code match, if 0 then there was no match. Here is an abbreviated code
of the SP:
CREATE PROCEDURE ConfirmUserRegistration
@confirmation_cd NVARCHAR(50),
@numrows INT OUTPUT
AS
-- ....
BEGIN TRAN
INSERT INTO Users
(egdate,
pass,
-- ...
firstName)
SELECT regdate,
pass,
-- ...
firstName
FROM TempRegistration
WHERE confirm = @confirmation_cd;
SET @numrows = @@rowcount;
DELETE FROM TempRegistration
WHERE confirm = @confirmation_cd;
COMMIT TRAN;
Note that you can directly perform the DELETE without checking the result of
the INSERT, because if there is no match then there will be no rows deleted.
If you want you can have an IF @numrows > 0 before executing the DELETE
statement to run it only when there is a match.
2). On your client side, you have to define the output parameter and then
check the results, abbreviated code here:
'... connection, command and first parameter initialization go here
' now add the output parameter
parameter = command.Parameters.Add( _
"@numrows",
SqlDbType.Int)
parameter.Direction = ParameterDirection.Output
'... open the connection and execute command go here
' retrieve the output value
If (command.Parameters("@numrows").Value = 1) Then
' we have a match and confirmation is complete
Else
' confirmation code is invalid - show alert
End If
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Re: Copy Row Of Data From Table to Table In Same DB
am 05.06.2007 19:16:31 von pbd22
On Jun 5, 8:36 am, "Plamen Ratchev" wrote:
> I will try to sketch here the answer to both questions:
>
> 1). To detect that the confirmation code exists, you can check the number of
> rows affected by the insert (using @@rowcount), and then return that value
> to the client using an output parameter. If the number of rows is 1 (I
> assume you have either a primary key or UNIQUE constraint on the
> confirmation code column so duplicates are not possible), then you know you
> had a code match, if 0 then there was no match. Here is an abbreviated code
> of the SP:
>
> CREATE PROCEDURE ConfirmUserRegistration
> @confirmation_cd NVARCHAR(50),
> @numrows INT OUTPUT
> AS
> -- ....
> BEGIN TRAN
>
> INSERT INTO Users
> (egdate,
> pass,
> -- ...
> firstName)
> SELECT regdate,
> pass,
> -- ...
> firstName
> FROM TempRegistration
> WHERE confirm = @confirmation_cd;
>
> SET @numrows = @@rowcount;
>
> DELETE FROM TempRegistration
> WHERE confirm = @confirmation_cd;
>
> COMMIT TRAN;
>
> Note that you can directly perform the DELETE without checking the result of
> the INSERT, because if there is no match then there will be no rows deleted.
> If you want you can have an IF @numrows > 0 before executing the DELETE
> statement to run it only when there is a match.
>
> 2). On your client side, you have to define the output parameter and then
> check the results, abbreviated code here:
>
> '... connection, command and first parameter initialization go here
> ' now add the output parameter
> parameter = command.Parameters.Add( _
> "@numrows",
> SqlDbType.Int)
> parameter.Direction = ParameterDirection.Output
>
> '... open the connection and execute command go here
> ' retrieve the output value
> If (command.Parameters("@numrows").Value = 1) Then
> ' we have a match and confirmation is complete
> Else
> ' confirmation code is invalid - show alert
> End If
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
Thanks a ton Plamen,
This thread was immensely helpful. I really appreciate it.
As a final note, for anybody that is using this thread for their own
registration system, you need to comment out NOCOUNT ON to
get the appropriate response from the SPROC (at least, I think
that is what solved my "no response" problem).
Thanks again Plamen!
Re: Copy Row Of Data From Table to Table In Same DB
am 05.06.2007 19:57:23 von Joe Celko
>> Like the title says - how do i do this? .. An explicit value for the identity column in table 'TABLE2' can only be specified when a column list is used and IDENTITY_INSERT is ON. <<
Let us go back to RDBMS basics. A row models a complete fact ("John
bought a squid on 2007-05-12"). This fact should appear in one
table, one time and in one way in the entire schema. This is the
foundation of normalization.
In the old days, with punch cards, paper files, etc. we would
physically move these physical records from one physical location to
another physical location. We had redundancy and we want to get rid
of it. You want to increase it.
You should never use IDENTITY in an RDBMS; you want to have a
relational key. A key has nothing to do with the physical location of
the data in the hardware; it is based on the nature of the data being
modeled.
You entire approach is that of someone managing a 1950's paper file
system. Please read a book before you code again. If you want
stinking dirty kludges, then you can get them in Newsgroup; but being
a good RDBMS programmer will take YEARS of hard work. Be better than
that.