February 2, 2006 at 5:37 am
insert into temptable exec sp_executesql @sqlquery
Iam executing a query and inserting into the temptable,but i need a requirement lke @sqlquery will change in runtime some times it will retrieve 10 columns,some times it will retunr 20 columns.In temptable it consits of 50 columns.When iam trying to insert the output of @sqlquery it is saying "COlumn number of supplied values does not match table definition.
Is there any way to insert into the temptable without defining the column names.
February 2, 2006 at 7:00 am
you could off course always select 50 columns
e.g. set @sqlquery = 'select yourcol1, yourcol2, null, null, null,... from yourtabel '
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 2, 2006 at 8:22 am
Not exactly what your looking for..but here's something I wrote to address a similar issue..
--##1 HAS SOME SUBSET OF THE ATTRIBUTES OF ##2
DECLARE @CHILD_FLAG INT
DECLARE @QUERY NVARCHAR(100)
SELECT * INTO ##2 FROM SYSOBJECTS
DECLARE @MASTER_ATTRIB_LIST TABLE
(
ATTRIB_NAME VARCHAR(1000) UNIQUE
)
DECLARE @CHILD_ATTRIB_LIST TABLE
(
ATTRIB_NAME VARCHAR(1000) UNIQUE
)
INSERT @MASTER_ATTRIB_LIST
SELECT C.NAME FROM
TEMPDB..SYSCOLUMNS C
JOIN TEMPDB..SYSOBJECTS O
ON O.ID = C.ID
AND O.NAME ='##2'
--MAKE ALL FIELDS IN ## NULLABLE
DECLARE @ATTRIB VARCHAR(100)
DECLARE CUR_ATTRIB CURSOR FOR
SELECT ATTRIB_NAME
FROM @MASTER_ATTRIB_LIST
OPEN CUR_ATTRIB
FETCH NEXT FROM CUR_ATTRIB INTO @ATTRIB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @QUERY = 'ALTER TABLE ##2 ALTER COLUMN [' + @ATTRIB + '] VARCHAR(1000) NULL'
EXEC(@QUERY)
FETCH NEXT FROM CUR_ATTRIB INTO @ATTRIB
END
CLOSE CUR_ATTRIB
DEALLOCATE CUR_ATTRIB
SET @QUERY = N'SELECT CACHE,CATEGORY,CRDATE INTO ##1 FROM SYSOBJECTS'
EXECUTE MASTER..SP_EXECUTESQL @QUERY
INSERT @CHILD_ATTRIB_LIST
SELECT C.NAME FROM
TEMPDB..SYSCOLUMNS C
JOIN TEMPDB..SYSOBJECTS O
ON O.ID = C.ID
AND O.NAME ='##1'
SELECT @CHILD_FLAG=COUNT(*) FROM
@CHILD_ATTRIB_LIST WHERE ATTRIB_NAME NOT IN
(
SELECT ATTRIB_NAME FROM @MASTER_ATTRIB_LIST
)
IF @CHILD_FLAG = 0
BEGIN
TRUNCATE TABLE ##2
DECLARE @QUERY2 VARCHAR(8000)
DECLARE CUR_ATTRIB CURSOR FOR
SELECT ATTRIB_NAME
FROM @CHILD_ATTRIB_LIST
SET @QUERY = 'SELECT '
SET @QUERY2 = 'INSERT INTO ##2('
OPEN CUR_ATTRIB
FETCH NEXT FROM CUR_ATTRIB INTO @ATTRIB
WHILE @@FETCH_STATUS = 0
BEGIN
SET @QUERY2 = @QUERY2 + '['+ @ATTRIB + '],'
SET @QUERY = @QUERY + '['+ @ATTRIB + '],'
FETCH NEXT FROM CUR_ATTRIB INTO @ATTRIB
END
SET @QUERY = @QUERY + 'FROM ##1'
SET @QUERY2 = @QUERY2 + '*'
SET @QUERY = REPLACE(@QUERY2,',*',')') + REPLACE(@QUERY,',FROM',' FROM')
EXEC(@QUERY)
CLOSE CUR_ATTRIB
DEALLOCATE CUR_ATTRIB
END
ELSE
BEGIN
PRINT '##1 IS NOT A CHILD OF ##2'
END
SELECT * FROM ##2
DROP TABLE ##1
DROP TABLE ##2
Mathew J Kulangara
sqladventures.blogspot.com
February 2, 2006 at 8:25 am
Aswani: For pre-defined table structure, it cannot be done since that is the physical definition of the table and doing so would require a DDL change. For actual temporary tables, you can do it by incorporating the temp table logic in the select statement by using a select...into #temp approach. i.e.
Iteration # 1(10 columns):
select col1, ...col10 into #temp1 from ....
Iteration # 2 (20 columns):
select col1, ....col20 into #temp1 from ....
But the select -- into clause can cause performance issues as well so use it only if absolutely needed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply