Click here to Skip to main content
14,974,040 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
When i execute this query iam getting error like in below...
SQL
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

SQL
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

.
Posted
Updated 27-May-15 21:41pm
v2

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.
SQL
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.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900