SELECT TOP 1 * INTO #tmp FROM table1;
IF OBJECT_ID('tempdb..#tmp') IS NULL
BEGIN 
	SELECT TOP 1 * INTO #tmp FROM table2;
END 

Msg 2714, Level 16, State 1, Line 4
There is already an object named '#tmp' in the database.

SELECT TOP 0 * INTO #tmp FROM table1;
INSERT INTO #tmp SELECT * FROM table1;
INSERT INTO #tmp SELECT * FROM table2;

quote:

---

Originally posted by robvolk

Why not use varchar/nvarchar(max) to construct your dynamic SQL and use EXEC()? That will avoid the limit and likely the need for multiple variables.

---

CREATE TABLE #tmp
(
	col1 NVARCHAR(MAX)
);

DECLARE @sql NVARCHAR(MAX);
SET @sql = CAST('select ''' AS NVARCHAR(MAX))
    + REPLICATE(CAST('a' AS NVARCHAR(MAX)), 10000) 
    + CAST('''' AS NVARCHAR(MAX));
SELECT LEN(@sql);

INSERT INTO #tmp
EXEC sp_executesql @sql;

SELECT LEN(col1)
FROM   #tmp;

DROP TABLE #tmp;

quote:

---

Originally posted by robvolk

Why not use varchar/nvarchar(max) to construct your dynamic SQL and use EXEC()? That will avoid the limit and likely the need for multiple variables.

---

quote:

---

Originally posted by under2811

Hi

Thank you for reply. I am able to proceed with NVARCHAR(MAX) but now the real problem is i am getting error like

Msg 1702, Level 16, State 1, Line 1
CREATE TABLE failed because column 'Column_1016' in table '#Temp' exceeds the maximum of 1024 columns.

Actually in pivoting i am getting almost more than 2700 columns. If I refer Sparce table/Wide table which will allow us to create more than 20000 columns approx 30000 columns. But as i am creating table #Temp dynamically by pivoting it. So i dont have table structure firm to add Sparce column in it.

T.I.A

---

quote:

---

Originally posted by under2811

Hi,

I am using it for SSIS and fill the excel.

T.I.A

---