SQL Server Query to Add Column Titles to Existing Table in Server

AlC 1 Reputation point
2023-01-28T13:15:12.93+00:00

I imported a csv table that does not have column titles in a header row, as illustrated in the .png snip below:

MS_Q&A

As you will note, the column titles are actually populated with data.

I would like to obtain a SQL Server query to insert the following column titles: RPT, WKSHT,LINE,CLMN,AMT above the column row that currently contains the following data 667603,S200001,00300,00400,1.

I have tried to accomplish this task by creating a second table with the desired column titles and running an append query to merger the two table. The query executed but resulted in duplication of many values.

Thank you.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,639 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2023-01-28T14:06:10.2033333+00:00

    No, you don't do that. First of all, a table is an unordered entity, so you cannot add rows on "top". You can add rows, but they will just be there somewhere.

    And, second, a table contains data, not metadata. The first column, for instance, seem to have numeric values. RPT would be out of there.

    What you want is to have the columns to be named RPT, WKSHT,LINE,CLMN and AMT rather than 667603, S20001, 0030, 0040 and 1, which are quite weird column names. (And presumably you want that data to be in the table).

    I don't know exactly what means you use to import the file, but make sure you create the table first, or at least specify the column names. And make sure that you deselect any option about "my file has column names".

    0 comments
  2. CosmogHong-MSFT 22,616 Reputation points Microsoft Vendor
    2023-01-30T03:00:45.64+00:00

    Hi @AIC

    Not sure about the datatype, but you might try this:

    CREATE TABLE New_Table(RPT VARCHAR(20),WKSHT VARCHAR(20),LINE VARCHAR(20),CLMN VARCHAR(20),AMT VARCHAR(20)) 
INSERT INTO New_Table VALUES('667603','S200001','00300','00400','1')
INSERT INTO New_Table SELECT * FROM CSV_Table

SELECT * FROM New_Table

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments