Insert rows into a table without dropping it

Question

Post reply

Insert rows into a table without dropping it

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
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>

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Just do the INSERT without using any CREATEs or DROPs:

INSERT INTO dbo.T1 (column_2)

VALUES ('Explicit value');

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Or

INSERT INTO dbo.T1 (column_2)

select col2 from YourTable

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

Use the INSERT/SELECT syntax from my second example.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 5

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

cbrammer1219 SSCarpal Tunnel Points: 4476 More actions · Answer 6

cbrammer1219

SSCarpal Tunnel

Points: 4476

September 23, 2014 at 9:11 am

#1747531

Thank You, this explained just what I needed..