Ranit Das Ranit Das - 7 months ago 7
SQL Question

Insert into from select query error in sql server

I am using the below query to insert data from one table to another:

DECLARE @MATNO NVARCHAR(10), @GLOBALREV INT, @LOCALREP INT

SET @MATNO = '7AGME'
SET @GLOBALREV = 11
SET @LOCALREP = 1

INSERT INTO CIGARETTE_HEADER VALUES
(SELECT * FROM CIGARETTE_HEADER_BK1 WHERE MATERIAL_NUMBER = @MATNO AND GLOBAL_REVISION = @GLOBALREV AND LOCAL_REVISION = @LOCALREP)


The column in both the tables are same, but I am getting the following error:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.


Can you please let me know the mistake here

Answer

You don't need to use the VALUES () notation. You only use this when you want to insert static values, and only one register. Example: INSERT INTO Table VALUES('value1',12, newid());

Also i recommend writing the name of the columns you plan to insert into, like this:

INSERT INTO Table
(String1, Number1, id)
VALUES('value1',12, newid());

In your case, do the same but only with the select:

DECLARE @MATNO NVARCHAR(10), @GLOBALREV INT, @LOCALREP INT;

SET @MATNO = '7AGME';
SET @GLOBALREV = 11;
SET @LOCALREP = 1;

INSERT INTO CIGARETTE_HEADER
(ColumnName1, ColumnName2)
SELECT ColumnNameInTable1, ColumnNameInTable2
FROM CIGARETTE_HEADER_BK1 
WHERE MATERIAL_NUMBER = @MATNO 
AND GLOBAL_REVISION = @GLOBALREV 
AND LOCAL_REVISION = @LOCALREP);
Comments