如何将数据插入临时表..? [英] how to insert data into temporary table..?
本文介绍了如何将数据插入临时表..?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当我执行此查询时,我会收到如下所示的错误...
创建 table #usercount(dbname varchar ( 100 ), count varchar ( 100 ))
DECLARE @ command varchar ( 1000 )
insert into #usercount
SELECT @ command =
' 使用? SELECT(SELECT DB_NAME()),count(c_code)来自tbl_user_info'
EXEC sp_MSforeachdb @命令
喜欢此错误..
msg 199 ,等级 15 ,状态 1 ,Line 0
INSERT 语句不能包含 SELECT 语句将值 分配给变量
。
解决方案
在SP中声明2个变量
@dbname和@counts
然后在创建临时表之后使用现有表格中的select cmd put值,并在其中插入这些值
例如。
声明 @ ID int
声明 @ Name nvarchar ( 50 )
SELECT @ ID = ID,@ Name = Name from X_tbl
create table #mytable(id int ,name nvarchar ( 50 ))
insert into #mytable values ( @ ID , @Name )
select * into temptable_name from maintable_name
INSERT语句不能包含为变量赋值的SELECT语句
这正是你想要做的......
你不能这样做:这意味着试图设置@的值命令变量为多个值,而不是单个值。
When i execute this query iam getting error like in below...
create table #usercount(dbname varchar(100),counts varchar(100))
DECLARE @command varchar(1000)
insert into #usercount
SELECT @command =
'USE ? SELECT (SELECT DB_NAME()), count(c_code) from tbl_user_info'
EXEC sp_MSforeachdb @command
like this error..
msg 199, Level 15, State 1, Line 0
An INSERT statement cannot contain a SELECT statement that assigns values to a variable
.
解决方案
Write SP in that declare 2 variables
@dbname and @counts
then using select cmd put values in it from ur existing table after that create ur temp table and insert those value in it
eg.
Declare @ID int Declare @Name nvarchar(50) SELECT @ID=ID,@Name=Name from X_tbl create table #mytable (id int,name nvarchar(50)) insert into #mytable values(@ID,@Name)
select * into temptable_name from maintable_name
The error is pretty explicit:
An INSERT statement cannot contain a SELECT statement that assigns values to a variable
And that is exactly what you are trying to do...
You can't do that: it would mean trying to set the value of the @Command variable to a number of values, rather than a single value.
这篇关于如何将数据插入临时表..?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文