Post reply

How to create a table on the fly? Is it even possible?

  • June 8, 2010 at 7:21 am

    #222477

    Hi all,

    I will have to import text files into sqlserver.

    I will use OpenDataSource as described in this article:

    Reading Ad Hoc Text Files with OpenDataSource

    The files can have around 90 columns.

    I wonder wether it would possible to create the destination table on the fly?

    How would do that?

    TIA,

    Philippe

  • June 8, 2010 at 7:26 am

    #1177458

    sure, it's just like any regular INTO NEWTABLENAME select statement: the column definitions will be derived from the output of the results of the SELECT.

    SELECT TOP 3 *

    INTO MyNewTable

    FROM sys.tables

    SELECT *

    INTO AnotherNewTable

    FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',

    'Set FmtOnly OFF; EXEC dbo.sp_Who')

    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!

  • June 8, 2010 at 7:36 am

    #1177468

    works fine! thank you...

  • June 8, 2010 at 7:47 am

    #1177477

    SELECT ... INTO will create a table based on the data retreived from the SELECT clause. I use it when generating log tables. It works the first time I run it, then I move the INTO clause up above the SELECT, and put an INSERT in front of it.

    --J

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

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