How to insert dynamic query into table using c#

Question

i need to insert my dynamic query which i am generating and saving into sql-server database table, but without where condition its working fine but with where condition its giving me error, please anyone can help me on this.

INSERT INTO [dbo].[tablename] ([DataQuery],[DataQueryName],[EditedDate],[LastUsedDate],[UserId])
 VALUES ('SELECT * from tablename WHERE tablename.UID < '10' AND tablename.status = 'Active' ORDER BY Id'
       ,'aa',GETDATE(),GETDATE(),'1541')

What error? BTW, Do INSERT with SELECT instead, skip VALUES. — jarlh, May 09 '19 at 12:56
SELECT * from tablename WHERE tablename.UID < '10' AND tablename.status = 'Active' ORDER BY Id this is my first parameter which should insert into [DataQuery] column — Ethan, May 09 '19 at 13:01
Is rewriting the SQL statement possible or is it a bogus statement? AS stated it look like a [insert select](https://stackoverflow.com/questions/25969/insert-into-values-select-from). without the need of escaping the querry — xdtTransform, May 09 '19 at 13:06
Are you storing queries in a column? This really looks like that is what you trying to do. Why??? — Sean Lange, May 09 '19 at 13:12
And when you get an error stating "its giving me error" is worthless unless you also provide the error message. — Sean Lange, May 09 '19 at 13:33

score 2 · Answer 1 · answered May 09 '19 at 13:00

2

You need to escape the single quotes with single quotes in your SQL statement:

INSERT INTO [dbo].[tablename] ([DataQuery],[DataQueryName],[EditedDate],[LastUsedDate],[UserId])
 VALUES ('SELECT * from tablename WHERE tablename.UID < ''10'' AND tablename.status = ''Active'' ORDER BY Id'
       ,'aa',GETDATE(),GETDATE(),'1541')

answered May 09 '19 at 13:00

user212514

3,110
1
15
11

Correct, but when i need to run that inserted query it won't work. – Ethan May 09 '19 at 13:03
Have you tried it? When you pull that query back out of the database it will have only one quotation mark. The `''` tells SQL Server that this is a quotation mark in the string, *not* the end of the string. – user212514 May 09 '19 at 13:05
Correct, thanks its working, when i am pull the query back – Ethan May 09 '19 at 13:09

score 0 · Answer 2 · edited May 09 '19 at 13:36

It could be that the single quotes in your WHERE clause need to be escaped. Try replacing single-quotes in your query with \' to escape the quote. Example:

INSERT INTO [dbo].[tablename]
([DataQuery],[DataQueryName],[EditedDate],[LastUsedDate],[UserId])
 VALUES ('SELECT * from tablename WHERE tablename.UID < \'10\' AND tablename.status = \'Active\' ORDER BY Id'
       ,'aa',GETDATE(),GETDATE(),'1541')

How to insert dynamic query into table using c#

2 Answers2