Post reply

SP to copy data from view to a new table in SQL Server 2008

  • October 12, 2009 at 6:47 pm

    #227004

    I am trying to create a Stored Proc and pass a parameter as a filename. Then create a table with the structure and data from a view. I tried table types (new in SQL Server 2008) and they don't work with parameters. Any ideas? TIA!

    David L. Crooks

  • October 12, 2009 at 8:06 pm

    #1065003

    little too thin on the details to offer any help so far;

    what does the filename have to do with anything?

    I'm trying to read between the lines, so help me out here:

    is this what you want to do:

    send a proc a file name, BULK INSERT the data from the file into a table, then create a view for the table?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 13, 2009 at 8:35 am

    #1065217

    Sorry for the lack of details. You are close.

    I have a view with some data.

    I want to create an empty table with the same structure as the view with the name of the parameter variable.

    Then populate the new table with the data from the view.

    I have tried:

    SELECT * INTO @VARFILENAME FROM V_MYVIEW

    And it doesn't like the variable filename.

    Thanks!

  • October 13, 2009 at 8:53 am

    #1065234

    davidlcrooks (10/13/2009)

    Sorry for the lack of details. You are close.

    I have a view with some data.

    I want to create an empty table with the same structure as the view with the name of the parameter variable.

    Then populate the new table with the data from the view.

    I have tried:

    SELECT * INTO @VARFILENAME FROM V_MYVIEW

    And it doesn't like the variable filename.

    Thanks!

    in that case, you have to use dynamic SQL:

    DECLARE @sql VARCHAR(max)

    SET @sql = 'SELECT * INTO ' + @VARFILENAME + ' FROM V_MYVIEW '

    print @sql

    exec(@sql)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 13, 2009 at 9:07 am

    #1065245

    Thanks a lot! That was the key...

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

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