September 10, 2010 at 5:07 am
Sure this is a uber-simple question, but as I'm new to SQL I'd rather appear stupid than make a major mistake 😀
I have the following table:
TABLE gift_transaction
(
transaction_id NVARCHAR(50) NULL
,account_id NVARCHAR(50) NULL
,payment_date NVARCHAR(50) NULL
,payment_amount NVARCHAR(50) NULL
,source_code NVARCHAR(50) NULL
,source_type NVARCHAR(50) NULL
,gift_type NVARCHAR(50) NULL
,payment_method NVARCHAR(50) NULL
,donation_category NVARCHAR(50) NULL
,transaction_type NVARCHAR(50) NULL
,joint_account_id NVARCHAR(50) NULL
)
And what I want is to have:
(
transaction_id NVARCHAR(50) NOT NULL
,account_id NVARCHAR(50) NOT NULL
,payment_date DATETIME NOT NULL
,payment_amount MONEY NOT NULL
,source_code NVARCHAR(50)
,source_type NVARCHAR(50)
,gift_type NVARCHAR(50) NULL
,payment_method NVARCHAR(50) NULL
,donation_category NVARCHAR(50) NULL
,transaction_type NVARCHAR(50) NULL
,joint_account_id NVARCHAR(50) NULL
)
Can I simply do this:
CREATE TABLE gift_transaction2
(
transaction_id NVARCHAR(50) NOT NULL
,account_id NVARCHAR(50) NOT NULL
,payment_date DATETIME NOT NULL
,payment_amount MONEY NOT NULL
,source_code NVARCHAR(50)
,source_type NVARCHAR(50)
,gift_type NVARCHAR(50) NULL
,payment_method NVARCHAR(50) NULL
,donation_category NVARCHAR(50) NULL
,transaction_type NVARCHAR(50) NULL
,joint_account_id NVARCHAR(50) NULL
)
INSERT INTO gift_transaction2
SELECT
transaction_id
,account_id
,CONVERT(DATETIME,payment_date,103) AS payment_date
,CONVERT(MONEY,payment_amount) AS payment_amount
,source_code
,source_type
,gift_type
,payment_method
,donation_category
,CASE WHEN(transaction_type = '') THEN NULL ELSE transaction_type END AS transaction_type
,joint_account_id
FROM gift_transaction
DROP TABLE gift_transaction
Or will this completely mess up my data?
Thanks in advance!
September 13, 2010 at 9:38 am
At first glance it looks ok. No reason you can't just try it and see.
September 13, 2010 at 9:48 am
Make sure no record has value NULL for those changed from NULL to NOT NULL, or the insert will fail. Also, it might be wise to comment out the drop table command. Run the drop table command ONLY after you are fully satisfied with the results so you will not mess things up even if a few attempts are not success.
September 13, 2010 at 9:50 am
Thanks both - turns out it was a stupid typo making it not work in the end - d'oh!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply