June 27, 2011 at 2:32 pm
Hi All,
I am creating a table using cursor and it gets created in tempdb. Even if i specify the database, it gets ignored. How can I create a table in a particular database?
June 27, 2011 at 2:52 pm
If you're using the # prefix SQL Server will ignore it and use tempdb anyway. This:
CREATE TABLE AdventureWorks.dbo.#tbl (a INT);
Results in a table being created in tempdb and this warning message:
Database name 'AdventureWorks' ignored, referencing object in tempdb.
This:
CREATE TABLE AdventureWorks.dbo.tbl (a INT);
Will result in a table being created in AdventureWorks DB
If you have questions please provide the code you're trying.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 9:50 am
Thanks a lot for your rely..it helped!!
Does it also applies if I have a tablename like ##temp1?
June 28, 2011 at 9:52 am
yes
June 28, 2011 at 1:13 pm
Ok. Thank you!!
Have another question...
I want to insert results from sp_depends into a table
SET @sql = 'USE '+@db_name+' EXEC sp_depends '+ @table_name
insert into ##tmp
EXEC (@sql)
But with that I also need the database name into that table. How can I use EXEC and insert into together?
June 28, 2011 at 1:38 pm
anjali3386 (6/28/2011)
Ok. Thank you!!Have another question...
I want to insert results from sp_depends into a table
SET @sql = 'USE '+@db_name+' EXEC sp_depends '+ @table_name
insert into ##tmp
EXEC (@sql)
But with that I also need the database name into that table. How can I use EXEC and insert into together?
A couple options:
1. create another temp table that looks like ##tmp plus a column for db name
2. insert the data from ##tmp into the new temp table while appending the db name to each row
-or-
1. make db_name a nullable column in ##tmp situated ordinally after the last column that captures output from sp_depends
2. after sp_depends output is captured issue an update to set db_name in ##tmp to the db name wherever its NULL
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 2:48 pm
I tried the second way but it gives me the following error.
Insert Error: Column name or number of supplied values does not match table definition.
June 28, 2011 at 2:53 pm
try like this:
insert into ##tmp (ColumnListMatchingsp_dependsOutput)
EXEC (@sql)
if you identify the specific columns, you can have more columns in the table than the proc would insert.
Lowell
June 28, 2011 at 2:54 pm
Hmmm, something must be out of order on your side. I tested the technique before I posted. Here is my POC code:
USE tempdb
GO
CREATE PROC dbo.proc_a
AS
BEGIN
SELECT 1 AS a
END
GO
CREATE TABLE #tmp (a INT, b SYSNAME NULL) ;
GO
INSERT INTO #tmp
(a)
EXEC dbo.proc_a
GO
UPDATE #tmp
SET b = DB_NAME() ;
GO
SELECT *
FROM #tmp
go
DROP PROC dbo.proc_a
GO
DROP TABLE #tmp
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 28, 2011 at 3:26 pm
@Opc.three & Lowell: Thank you so much!
July 5, 2011 at 3:58 pm
Hi All,
When I run the code as discussed in above posts, I get the error " Insert Error: Column name or number of supplied values does not match table definition."
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply