March 31, 2010 at 3:04 am
Hi,
I need to extract the all user tables with five 10 samples rows into to Excel from Database?
I tried this below but not working can you give me your TSQL
SET QUOTED_IDENTIFIER OFF
DECLARE @TABLE AS TABLE (NAME VARCHAR(150))
INSERT INTO @TABLE(NAME) SELECT '['+NAME+']' FROM SYSOBJECTS WHERE XTYPE ='U'
--SELECT * FROM @TABLE
DECLARE @NAME AS VARCHAR(150)
DECLARE @sql AS VARCHAR(150)
DECLARE CC CURSOR FOR SELECT NAME FROM @TABLE
OPEN CC
FETCH NEXT FROM CC INTO @NAME
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @sql = "SELECT TOP 10 * FROM '"+@NAME+"'"
EXECUTE (@SQL)
FETCH NEXT FROM CC INTO @NAME
END
CLOSE CC
DEALLOCATE CC
GO
March 31, 2010 at 3:15 am
Your code seems to work.. u missed out on the apostrophes at the end.. here is the working version (same ur code with slight modification)..
SET QUOTED_IDENTIFIER OFF
DECLARE @TABLE AS TABLE (NAME VARCHAR(150))
INSERT INTO @TABLE(NAME) SELECT '['+NAME+']' FROM SYSOBJECTS WHERE XTYPE ='U'
--SELECT * FROM @TABLE
DECLARE @NAME AS VARCHAR(150)
DECLARE @sql AS VARCHAR(150)
DECLARE CC CURSOR FOR SELECT NAME FROM @TABLE
OPEN CC
FETCH NEXT FROM CC INTO @NAME
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @sql = 'SELECT TOP 10 * FROM '+@NAME
---PRINT @sql
EXECUTE (@SQL)
FETCH NEXT FROM CC INTO @NAME
END
CLOSE CC
DEALLOCATE CC
GO
to export the data to an excel, i am not sure how to do 🙁
March 31, 2010 at 3:56 am
Thank you so much...........
March 31, 2010 at 3:57 am
What query or process you used for exporting data into sql server??
March 31, 2010 at 6:15 am
To avoid the cursor you can use this query as below
SET QUOTED_IDENTIFIER OFF
DECLARE @TABLE AS TABLE (Seq int identity(1,1), NAME VARCHAR(150))
INSERT INTO @TABLE(NAME) SELECT '['+NAME+']' FROM SYSOBJECTS WHERE XTYPE ='U'
--SELECT * FROM @TABLE
DECLARE @NAME AS VARCHAR(150)
DECLARE @sql AS VARCHAR(150)
WHILE EXISTS( SELECT 1 FROM @TABLE)
BEGIN
SET @sql = 'SELECT TOP 10 * FROM '+ ( SELECT TOP 1 NAME FROM @TABLE )
--SELECT @sql
EXECUTE (@SQL)
DELETE TOP(1) FROM @TABLE
END
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 31, 2010 at 6:42 am
If you can take advantage of VARCHAR(MAX) then the following SQL will remove the need for any kind of manual 'loop':
DECLARE @sql VARCHAR(MAX)
SET @sql = ''
SELECT @sql = @sql + 'SELECT TOP 10 * FROM ' + QUOTENAME(name) + '; '
FROM sys.objects
WHERE type = 'U'
EXEC (@SQL)
It is still not addressing the requirement to extract the results into Excel (as Vijay stated).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply