英文:
Creating and inserting into a DB using Dynamic SQL
问题
对于读到这封信的人，
基本上，我正在尝试使用动态SQL创建一个数据库，然后插入数据。问题是，我找不到“GO”的替代方法，因为当我运行它时，会出现一个错误，说新创建的数据库不存在。我知道它不能在动态SQL中使用，因为它不被识别为T-SQL。我还尝试添加“;”，但错误仍然存在。
DECLARE @TargetDB sysname
DECLARE @TargetSchema sysname
DECLARE @TargetTable sysname
DECLARE @SourceDB sysname
DECLARE @SourceSchema sysname
DECLARE @SourceTable sysname
DECLARE @sql NVARCHAR(max)
SET @SourceDB = 'AYOOO'
SET @TargetDB = @SourceDB + 'SandBox'
SET @SourceTable = 'GUCCI'
SET @TargetTable = @SourceTable + 'SandBox'
SET @sql = N'CREATE DATABASE ' + @TargetDB + ';' --创建新数据库
SET @sql = @sql + N' SELECT * INTO ' + @TargetDB + '.dbo.' + @TargetTable + ' FROM ' + @SourceDB + '.dbo.' + @SourceTable; --这两行是用于复制数据到新表的
PRINT @sql
EXEC sys.sp_executesql @sql
错误：
数据库 'AYOOOSandBox' 不存在。
我知道这是一个愚蠢的问题，但我想找到一个好的替代方法来代替“GO”，或者一个更好的动态SQL实践。
谢谢。
英文:
To whoever reads this,
Basically using dynamic SQL, i am trying to create a database and then insert into it. Problem is that I cant find an alternative to 'GO' since when i run it i get an error saying the newly created DB doesn't exist. I know that it cant be used in dynamic sql as it is not recognized as T-SQL. I've also tried adding ";" but error still persists.
DECLARE @TargetDB sysname
DECLARE @TargetSchema sysname
DECLARE @TargetTable sysname
DECLARE @SourceDB sysname
DECLARE @SourceSchema sysname
DECLARE @SourceTable sysname
DECLARE @sql NVARCHAR(max)
SET @SourceDB = 'AYOOO'
SET @TargetDB = @SourceDB + 'SandBox'
SET @SourceTable = 'GUCCI'
SET @TargetTable = @SourceTable + 'SandBox'
SET @sql = N'CREATE DATABASE ' + @TargetDB + '; ' --create new db
SET @sql = @sql + N' SELECT * INTO ' + @TargetDB +'.dbo.'+@TargetTable+' FROM ' + @SourceDB+'.dbo.'+@SourceTable; --these 2 lines are for copying data into new tables
PRINT @sql
EXEC sys.sp_executesql @sql
Error:
Database 'AYOOOSandBox' does not exist.
I know its a stupid question but I'd like to find a good alternative to "GO" or a better practice for Dynamic SQL.
Thanks
答案1
得分: 4
在修复了您的SQL以避免出现严重的注入问题后，通过正确引用您的对象，您需要将语句分为两个命令。然后，您可以使用一个命令来创建您的数据库，然后使用另一个命令来插入数据。
DECLARE @TargetDB sysname,
@TargetSchema sysname,
@TargetTable sysname,
@SourceDB sysname,
@SourceSchema sysname,
@SourceTable sysname,
@sql nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SourceDB = 'AYOOO';
SET @TargetDB = @SourceDB + 'SandBox';
SET @SourceTable = 'GUCCI';
SET @TargetTable = @SourceTable + 'SandBox';
SET @sql = N'CREATE DATABASE ' + QUOTENAME(@TargetDB) + N';'; --创建新数据库
EXEC sys.sp_executesql @sql;
SET @sql = N'SELECT *' + @CRLF +
N'INTO ' + QUOTENAME(@TargetDB) + N'.dbo.' + QUOTENAME(@TargetTable) + @CRLF +
N'FROM ' + QUOTENAME(@SourceDB) + N'.dbo.' + QUOTENAME(@SourceTable) + N';'; --将数据复制到新表中
EXEC sys.sp_executesql @sql;
请注意，上述代码中的注释已被翻译成了中文。
英文:
After fixing your SQL to not be a huge injection issue, by properly quoting your objects, you need to separate the statements into 2 commands. Then you can CREATE your database in one command, and then INSERT in another.
DECLARE @TargetDB sysname,
@TargetSchema sysname,
@TargetTable sysname,
@SourceDB sysname,
@SourceSchema sysname,
@SourceTable sysname,
@sql nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SourceDB = 'AYOOO';
SET @TargetDB = @SourceDB + 'SandBox';
SET @SourceTable = 'GUCCI';
SET @TargetTable = @SourceTable + 'SandBox';
SET @sql = N'CREATE DATABASE ' + QUOTENAME(@TargetDB) + N';'; --create new db
EXEC sys.sp_executesql @sql;
SET @sql = N'SELECT *' + @CRLF +
N'INTO ' + QUOTENAME(@TargetDB) + N'.dbo.' + QUOTENAME(@TargetTable) + @CRLF +
N'FROM ' + QUOTENAME(@SourceDB) + N'.dbo.' + QUOTENAME(@SourceTable) + N';'; --copying data into new tables
EXEC sys.sp_executesql @sql;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论