Insert multiple values by using Dynamic SQL from ASP.NET application

Question

In SQL Server 2005 we can insert multiple values as

INSERT INTO
TableName
(col1, col2, col3)
SELECT
'text1', 'text2', 1
UNION ALL
'text3', 'text4', 2

How can I insert it using dynamic sql using EXEC @sql

When I run

EXEC '  INSERT INTO
    TableName
    (col1, col2, col3)
    SELECT
    ''text1'', ''text2'', 1 -- using double quotation marks to escape
    UNION ALL
    ''text3'', ''text4'', 2
 '

I get error saying

Incorrect syntax near

'INSERT INTO Tablename(col1, col2, col3) SELECT 'text1', 'text2', 1 UNION ALL SELECT '.

Why it doesn't execute? Also, why the sql is not printing the whole sql statement that I typed? why it trims after the second select?

Can anyone help me write this?

EDIT

After I tried Devart's solution. It worked fine in SQL Server, and also from my ASP.NET application. But the problem comes when there is a space in any of the text say

EXEC ('
 INSERT INTO TableName (col1, col2, col3)
 SELECT ''text1'' AS t1, ''text2'' AS t2, 1 AS t3
 UNION ALL
 SELECT ''text3'', ''text4'', 2
')

this works fine...

but

EXEC ('
 INSERT INTO TableName (col1, col2, col3)
 SELECT ''text1'' AS t1, ''text2'' AS t2, 1 AS t3
 UNION ALL
 SELECT ''text3 with space in it'', ''text4 more spaces'', 2
')

works fine only in SSMS but not from asp.net application.

The code is as follows..

var cmd = new SqlCommand 
{ commandText = "myProcedure", commandType = CommandType.StoredProcedure, connection = myGlobalConnection };

cmd.Parameters.AddWithValue("@sqlParam", thatLongDynamicString);
cmd.ExecuteNonQuery();

throw's an Incorrect syntax error near with and near more, but if there is no space in text column. It works fine.

Why is this happening?

Answer 1

Try this one -

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL =  '
 INSERT INTO TableName (col1, col2, col3)
 SELECT ''text1'' AS t1, ''text2'' AS t2, 1 AS t3
 UNION ALL
 SELECT ''text3'', ''text4'', 2'

EXEC sys.sp_executesql @SQL

Or try this -

EXEC ('
 INSERT INTO TableName (col1, col2, col3)
 SELECT ''text1'' AS t1, ''text2'' AS t2, 1 AS t3
 UNION ALL
 SELECT ''text3'', ''text4'', 2
')

Update:

I think using Dynamic SQL in your case is bad practice. Try to use generate XML from your client and exec stored procedure with this XML as parameter. For example:

IF OBJECT_ID ('dbo.usp_InsertData') IS NOT NULL
   DROP PROCEDURE dbo.usp_InsertData
GO

CREATE PROCEDURE dbo.usp_InsertData
    @Data XML
AS BEGIN

    --INSERT INTO <your_table> (...)
    SELECT 
          t.c.value('@col1', 'NVARCHAR(MAX)') 
        , t.c.value('@col2', 'NVARCHAR(MAX)') 
        , t.c.value('@col3', 'INT') 
    FROM @Data.nodes('root/item') t(c)

END
GO

DECLARE @XML XML
SELECT @XML = '
<root>
    <item col1="text3" col2="text4" col3="1"/>
    <item col1="text3 with space in it" col2="text4 more spaces" col3="2"/>
</root>'

EXEC dbo.usp_InsertData @XML

In output: