Need script help!

Question

Hello.

I need script help. I know the insert statement.

Insert into table(col) values();

But I now need to use dynamic SQL for insertion.

I know nothing about dynamic sql, can someone share the idea?

Thanks in advance!

Answer 1

Hi @15431565

You can try this script.

DECLARE @SQL nvarchar(400)
DECLARE @TableName nvarchar(max)
DECLARE @ColName nvarchar(max)
DECLARE @Insertvalue nvarchar(max)
set @TableName = 'test'
set @ColName = 'colB'
set @Insertvalue = 'just a test'
set @SQL = N'INSERT INTO ' + @TableName + '(' + @ColName + ')' + ' VALUES (' +  QUOTENAME(@Insertvalue,'''') + ');'
print @SQL;
Exec sp_executesql @SQL,N'@TableName nvarchar(max),@ColName nvarchar(max),@Insertvalue nvarchar(max)',@TableName,@ColName,@Insertvalue;

select * from test;

Output:

If you need to write into stored procedures, you can refer to this official document, which contains an example of dynamic insertion.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver16

Best regards,

Percy Tang

Answer 2

If you want to learn about dynamic SQL, there is an article on my web site: The Curse and Blessings of Dynamic SQL

Yes, it's long, but dynamic SQL is an advanced feature that easily lends itself to abuse.