Copy Row Of Data From Table to Table In Same DB

[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

[Plamen Ratchev]

The syntax to use INSERT INTO is like this:

INSERT INTO TABLE2
(<column_list>)
SELECT <column_list>
FROM TABLE1
WHERE COL1 = 'A'

A few brief notes:
- the <column_listwill list your columns (like COL1, COL2, COL3, etc.)
- the <column_listmust contain the same number of columns in both clauses
(INSERT INTO and SELECT)
- if you do not specify the <column_listin the INSERT INTO clause (as you
did in your sample query), then the <column_listin 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

[pbd22]

On Jun 4, 1:54 pm, "Plamen Ratchev" <[e-mail]:

The syntax to use INSERT INTO is like this:

INSERT INTO TABLE2
(<column_list>)
SELECT <column_list>
FROM TABLE1
WHERE COL1 = 'A'

A few brief notes:
- the <column_listwill list your columns (like COL1, COL2, COL3, etc.)
- the <column_listmust contain the same number of columns in both clauses
(INSERT INTO and SELECT)
- if you do not specify the <column_listin the INSERT INTO clause (as you
did in your sample query), then the <column_listin 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: <Author: Last, First>
-- Create date: <Create Date: 4 June 2007>
-- Description: <Description: Table To Table Copy>
-- =============================================

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

[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

[pbd22]

On Jun 4, 3:21 pm, "Plamen Ratchev" <[e-mail]:

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.ConnectionStrin gs("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)

[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.ConnectionStrin gs("myConnStr").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 = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a7120 77"

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

[pbd22]

On Jun 4, 8:15 pm, "Plamen Ratchev" <[e-mail]:

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.ConnectionStrin gs("myConnStr").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 = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a7120 77"

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

[pbd22]

On Jun 5, 7:17 am, pbd22 <[e-mail]:

On Jun 4, 8:15 pm, "Plamen Ratchev" <[e-mail]:

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.ConnectionStrin gs("myConnStr").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 = "8c37a0737eegd64532rgdf56g5ec3f75aab5d9e7a7120 77"

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!

[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

[pbd22]

On Jun 5, 8:36 am, "Plamen Ratchev" <[e-mail]:

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!

[--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.