Post reply

Storing the result of a stored procedure to a table

  • February 12, 2009 at 2:39 am

    #128725

    Hi,

    I need to store the result of below statement to a table

    exec sp_tables

    how can I do this in SQL Server 2005?

    (Instead this can be achieved by querying

    INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS VIEW

    Actually I need

    exec sp_tables_ex 'Access_linked'

    and

    exec sp_columns_ex 'Access_linked'

    so that I can get the list of tables, columns and column data types to a table

    Thanks

    Thanks,
    Santhosh

    Human Knowledge Belongs To The World !!

  • February 12, 2009 at 3:12 am

    #942668

    This should put you on the right track...

    IF OBJECT_ID('tempdb..#Worksheets') IS NOT NULL

    DROP TABLE #Worksheets

    CREATE TABLE #Worksheets (TABLE_CAT varchar(30), TABLE_SCHEM VARCHAR(30), TABLE_NAME VARCHAR(50), TABLE_TYPE VARCHAR(20), REMARKS VARCHAR(30))

    INSERT INTO #Worksheets

    EXEC sp_tables_ex @ServerName

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • February 12, 2009 at 3:36 am

    #942680

    Chris Morris (2/12/2009)

    This should put you on the right track...

    IF OBJECT_ID('tempdb..#Worksheets') IS NOT NULL

    DROP TABLE #Worksheets

    CREATE TABLE #Worksheets (TABLE_CAT varchar(30), TABLE_SCHEM VARCHAR(30), TABLE_NAME VARCHAR(50), TABLE_TYPE VARCHAR(20), REMARKS VARCHAR(30))

    INSERT INTO #Worksheets

    EXEC sp_tables_ex @ServerName

    Yes I got it...

    For these kind of things we initially need to know how many columns the result set will be having right?

    Thanks

    Thanks,
    Santhosh

    Human Knowledge Belongs To The World !!

  • February 12, 2009 at 3:39 am

    #942681

    Santhosh (2/12/2009)

    Yes I got it...

    For these kind of things we initially need to know how many columns the result set will be having right?

    Thanks

    Yes, and you can easily do that by examining the sproc.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • February 12, 2009 at 3:44 am

    #942686

    Hi

    In Linked Server(Access database)

    Can we know which columns are having ntext data type?

    (when i went through the sproc sp_columns_ex i came to know that

    MEMO data type of Access will be internally converted to ntext instead of nvarchar(max) which we are not able to compare with other table)

    Thanks

    Thanks,
    Santhosh

    Human Knowledge Belongs To The World !!

  • February 12, 2009 at 5:59 am

    #942752

    Santhosh (2/12/2009)

    Hi

    In Linked Server(Access database)

    Can we know which columns are having ntext data type?

    (when i went through the sproc sp_columns_ex i came to know that

    MEMO data type of Access will be internally converted to ntext instead of nvarchar(max) which we are not able to compare with other table)

    Thanks

    You've answered your own question here Santosh. In any case, you'd import into a staging table, right? Then you would move the data into your destination table with columns and datatypes of your choosing...

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply