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?

You missed parentheses, it should be exec('...')

i-one
– i-one

2013-09-03 10:10:45 +00:00
Commented Sep 3, 2013 at 10:10 — i-one
– i-one, Commented Sep 3, 2013 at 10:10

Devart · Accepted Answer · 2013-09-03 12:13:02Z

2

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:

edited Sep 3, 2013 at 12:13

answered Sep 3, 2013 at 10:11

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

i-one Over a year ago

+1, though, aliasing columns in select list is not necessary for insert into (...) select (in contrast to select ... into ..)

Devart Over a year ago

@i-one you are right, but anyway using alias for columns is the good practice.

user2614504 Over a year ago

@Devart: Really thanks! That solved the problem, but now I am stuck in something else, can you please check if you can help me with that?

AB Vyas · Accepted Answer · 2013-09-03 12:35:24Z

You can try this. This will work.

DECLARE @Sql VARCHAR(MAX)

SET @Sql = '
insert into temp (id,name,address) 
SELECT 1,''text1'', ''text2'' 
UNION ALL 
select 2,''text3'', ''text4'''

--PRINT @Sql
EXEC (@Sql)

SELECT *
FROM temp

You are missing Select after Union all

SQL Fiddle

**EDIT**

Try This C# code

    StringBuilder sbQuery = new StringBuilder();
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    if (con.State == ConnectionState.Closed)
    {
        con.Open();
    }
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con; //Your connection string"        
    sbQuery.Append("insert into temp (id,name,address) SELECT 1,'text1', 'text2' UNION ALL select 2,'text3', 'text4'"); // your query goes here
    cmd.CommandText = "Testing1"; // your procedure name
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@SQL",sbQuery.ToString()));
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    DataTable dt = new DataTable();
    dt = ds.Tables[0];
    GridView1.DataSource = dt;
    GridView1.DataBind();

Your Store Procedure

CREATE PROCEDURE Testing1
@SQL varchar(Max)
AS
/* SET NOCOUNT ON */        
exec (@SQL)
select * from temp
RETURN

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related