September 23, 2014 at 8:38 am
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
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 23, 2014 at 8:48 am
Or
INSERT INTO dbo.T1 (column_2)
select col2 from YourTable
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 23, 2014 at 8:53 am
Use the INSERT/SELECT syntax from my second example.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 23, 2014 at 8:58 am
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.
September 23, 2014 at 9:02 am
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
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