August 24, 2010 at 8:05 am
I have little to no SQL experience so bear with me on this.
I have a table say table1 with columns Title,surname,forename,address1,telephone,mobile,country and so on say 40 columns populated with 1000 rows.
How can i move the records to a table called rejects where address1 in table1 is null. Rejects table should also get populated with rejectdate and rejectreason as 'address1 blank'.
Thanks in advance
August 24, 2010 at 8:37 am
thisisdeepthy (8/24/2010)
I have little to no SQL experience so bear with me on this.I have a table say table1 with columns Title,surname,forename,address1,telephone,mobile,country and so on say 40 columns populated with 1000 rows.
How can i move the records to a table called rejects where address1 in table1 is null. Rejects table should also get populated with rejectdate and rejectreason as 'address1 blank'.
Thanks in advance
Your "rejects" table will need to have all columns of "table1" plus columns
rejectdate datetime and rejectreason varchar(255).
Please note: varchar(255) is just an example and you can size it as you want.
The sql to get your rejected raws from table1 to rejects table will be like:
INSERT dbo.rejects ( Col1, Col2, address1, ... ColN, rejectdate, rejectreason)
SELECT Col1, Col2, address1, ... ColN, GETUTCDATE(), 'address1 blank'
FROM dbo.table1
WHERE address1 = ''
OR address1 IS NULL
August 24, 2010 at 8:46 am
But after inserting in to rejects, the inserted rejected record no longer should be in Table1.
Any Ideas?
August 24, 2010 at 11:05 am
Do you want to learn SQL or just looking for a quick answer?
If the first, you can have a look two methods:
1. Do insert and then delete in two separate steps/queries.
2. Use DELETE with OUTPUT clause to achieve the same in one step
If you want quick answer, I can only do it tomorrow as it's my "time-to-go-home"!.
Or, wait for someone else to help...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply