.Net Dynamic table name in SQL query?

Question

How can I use a dynamic table name in the below SQL query?

"INSERT INTO `tableName` (`ADUC`, `DHCP`, `DISK`, `DNS`, `Exchange`, `HOSTNAME`, `HWID`, `IP`, `OS`) values (@aduc,@dhcp,@disk,@dns,@exchange,@hostname,@hwid,@ip,@os)"

Currently I am declaring tableName via

Dim tableName = My.Computer.Name.ToString

I want the table name to reflect the computers name as that's what I've created within PhpMyAdmin, so any thoughs on how to do this?

Please note: that the ``needs to stay either side of the table name to support hyphenated table names in the query.

Mudassir Hasan · Accepted Answer · 2014-04-18 09:03:43Z

2

"INSERT INTO `"+tableName+"` (`ADUC`, `DHCP`, `DISK`, `DNS`, `Exchange`, `HOSTNAME`, `HWID`, `IP`, `OS`) values (@aduc,@dhcp,@disk,@dns,@exchange,@hostname,@hwid,@ip,@os)"

answered Apr 18, 2014 at 9:03

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

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

2 Comments

Samuel Nicholson Over a year ago

My god, it was that simple... Thank you m Hasan I will accept when it lets me.

Bouke Versteegh Over a year ago

Be careful that this method does not protect against SQL injection. If the tableName variable is provided externally (e.g. a user), the query can be manipulated into multiple queries, and any desired query may be performed on the database, including dropping all data.

Ronak Shah · Accepted Answer · 2014-04-18 09:20:25Z

You can create store procedure like below and pass tablename and values as parameter because you already know the table schema.

create table dynamictest

create table dynamictest(id varchar(max));

Now you need to insert value but table name is dynamic so you need to pass it.

create PROCEDURE [dbo].[mssql_DynamicTableTest]
@LogTable VARCHAR(1024),
@code NVARCHAR(Max)


AS
BEGIN

DECLARE 

    @sql    NVARCHAR(MAX),
    @params NVARCHAR(Max);

SET @params = N'@LogTable VARCHAR(1024),@code NVARCHAR(Max)';

 SET @sql = N'INSERT INTO '+@LogTable+' VALUES ('''+@code+''')';
 select @sql;


  EXEC sp_executesql @sql, @params, @LogTable,@code;



END

Now execute able store procedure by passing table name "dynamictest" and value "test".

Collectives™ on Stack Overflow

.Net Dynamic table name in SQL query?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related