Post reply

Insert rows into a table without dropping it

  • September 23, 2014 at 8:38 am

    #299617

    Hi I am finding it difficult to find an example that allows for insertion of additional rows into a table, without dropping the table I'm inserting into. Or inserting specific values. Like this example..

    http://technet.microsoft.com/en-us/library/ms188263(v=sql.105).aspx

    I have 6 table I am formatting the data to conform to the final table as I'm inserting it into, but none of these examples gives me the example needed. I am using SQL 2012.

    <code>

    SELECT

    CONVERT(VARCHAR(50),[FName]) + ' ' + CONVERT(VARCHAR(50),[LName]) AS [CustName]

    ,CAST('ALARMCOM' as nvarchar(8)) as VendorName

    ,CONVERT(VARCHAR(25),[CUSTOMER_CS_ACCOUNT_NUMBER]) AS [Cust_ID]

    ,CONVERT(VARCHAR(40),[Charge_Description])as [ChargeType]

    ,CASE

    WHEN CONVERT(VARCHAR(45),[Package_Description]) IS NULL THEN CONVERT(VARCHAR(45),[Add_on_Description]) ELSE CONVERT(VARCHAR(45),[Package_Description])

    END AS [ChargeDesc]

    ,getdate() AS [FileDate]

    ,CASE

    WHEN CONVERT(VARCHAR(5),[PACKAGE_ID]) IS NULL THEN CONVERT(VARCHAR(5),ADD_ON_ID) ELSE CONVERT(VARCHAR(5),[PACKAGE_ID])

    END AS [RateType]

    ,CONVERT(VARCHAR(10),[Charge_Amount]) AS [Amount]

    INTO [dbo].[tbl3rdPartyRecon]

    FROM [Helper].[dbo].[tbl1]

    </code>

    <code>

    SELECT

    [SubscriberName] as [CustName]

    ,CAST('Telguard' as nvarchar(8)) as VendorName

    ,[Subscriber] AS [Cust_ID]

    ,CONVERT(VARCHAR(50),[RatePlan]) + CONVERT(VARCHAR(3),[NetworkType]) as [ChargeType]

    ,CONVERT(VARCHAR(50),[RatePlan]) + CONVERT(VARCHAR(3),[NetworkType]) as [ChargeDesc]

    ,getdate() AS [FileDate]

    ,CONVERT(VARCHAR(50),[RatePlan]) AS [RateType]

    ,CONVERT(VARCHAR(10),[RatePlanAmount]) AS [Amount]

    INTO [dbo].[tbl3rdPartyRecon]

    FROM [Helper].[dbo].[tbl2]

    </code>

  • September 23, 2014 at 8:46 am

    #1747509

    Just do the INSERT without using any CREATEs or DROPs:

    INSERT INTO dbo.T1 (column_2)

    VALUES ('Explicit value');

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • September 23, 2014 at 8:48 am

    #1747511

    Or

    INSERT INTO dbo.T1 (column_2)

    select col2 from YourTable

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • September 23, 2014 at 8:53 am

    #1747518

    Use the INSERT/SELECT syntax from my second example.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • September 23, 2014 at 8:58 am

    #1747521

    Just to be clear on what Phil is commenting.

    There's a big difference between SELECT...INTO... and INSERT INTO...SELECT...

    The first option will create a table, the second one will just insert rows. It's documented on the link you posted.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 23, 2014 at 9:02 am

    #1747525

    I need to be able to insert more than just one column, I have 6 staging tables that I have formatted the data to conform to these values...

    I've created stored procedures to format the data before the insertion into the Destination table, but always get the error the tbl2 already exists.

    CustName VendorName Cust_ID ChargeType ChargeDesc FileDate RateType Amount

  • September 23, 2014 at 9:11 am

    #1747531

    Thank You, this explained just what I needed..

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

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