|
|
Posted by pbd22 on 06/04/07 21:46
On Jun 4, 1:54 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> 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_list> will list your columns (like COL1, COL2, COL3, etc.)
> - the <column_list> must contain the same number of columns in both clauses
> (INSERT INTO and SELECT)
> - if you do not specify the <column_list> in the INSERT INTO clause (as you
> did in your sample query), then the <column_list> 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: <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
Navigation:
[Reply to this message]
|