Author |
Topic |
starydynamo
Starting Member
12 Posts |
Posted - 2011-04-11 : 09:11:26
|
Hi all, I have a database table called ACTIVE and wish to migrate all the data that is marked as 'non active'(tickbox) and older than 2004 (dd/mm/yyyy) into another table called NON_ACTIVEI am very new to SQL so easier instructions is more than welcome!I have had a go:INSERT INTO NON_ACTIVE (*.)SELECT .*FROM ACTIVEWHERE Year(Date) =< 2004WHERE bActive = 0HELP |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-11 : 09:17:56
|
Couple of changes to the syntax - see highlighted:INSERT INTO NON_ACTIVE (*.)SELECT . *FROM ACTIVEWHERE Year(Date) =< <= 2004WHERE AND bActive = 0 Too many red-lines! Let me rewrite it.INSERT INTO NON_ACTIVESELECT *FROM ACTIVEWHERE Year(Date) <= 2004AND bActive = 0 This assumes that you have the same columns in NON_ACTIVE and ACTIVE tables.Now if you want to delete from the Active table, you can do it as follows. But be VERY careful, before you do this, first check if the data has actually been copied to NON_ACTIVE table. Then, when you issue the command to delete be ABSOLUTELY sure that you have the where clause typed in correctly.DELETE FROM ACTIVE WHERE Year(Date) =< 2004AND bActive = 0 |
|
|
starydynamo
Starting Member
12 Posts |
Posted - 2011-04-11 : 09:40:13
|
I am getting the issueState 1, Line 1r the identity column in table 'NON_ACTIVE' can only be specified when a column list is used and IDENTITY_INSERT is ONAny ideas? Thanks for your help!! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-11 : 09:56:20
|
You need to do two things:1. Turn on identity insert on the table. Type this command and that should do it.SET IDENTITY_INSERT NON_ACTIVE ON 2. Instead of using the * notation, you have to list each column in order. For example, let us say you had only two columns in your ACTIVE and NON_ACTIVE tables. Then you would do this:insert into NON_ACTIVE(col1, col2)SELECTcol1, col2FROM ACTIVEWHERE Year(Date) <= 2004AND bActive = 0 You probably have a lot many more columns than 2, so you would need to list them all. |
|
|
starydynamo
Starting Member
12 Posts |
Posted - 2011-04-11 : 09:58:43
|
Just to add more information as I wanted to be sure 'date' in WHERE Year(Date) <= 2004 related to the column header in the table and not some function (like Year)I have it like this at the momentYear(dtExpiryDate)dtExpiryDate = Column header in both source/destination table.I have more than one date (dtStartDate, dtMembershipDate) but only want to use the dtExpiryDate to migrate the data. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-11 : 09:58:57
|
Now that I think about it, can you post the DDL for the two tables? Brett's page here (http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx) has information on how to get the DDL. I assumed that you are inserting the identity column value from ACTIVE table to the NON_ACTIVE table. That may not be the case - we will be able to tell by looking at the DDL for the table |
|
|
starydynamo
Starting Member
12 Posts |
Posted - 2011-04-11 : 10:30:11
|
The database I am working on is highly sensitive and BIG so can't really post anything.I tried adding those lines from the last message and am still getting the same problem.Hmmmmm?! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-11 : 11:16:57
|
It's hard for me to say exactly what you should do since I can't see the table schema. But, I can tell you in generalities. Follow these steps:1. See if both your tables ACTIVE and NON_ACTIVE have exactly the same columns. If they are the same, then what I said in my posting at 04/11/2011 : 09:56:20 should work. If it does not, copy the error message and post it.2. If the tables do not have the same columns, then, you need to figure out the logic of how the data from one table goes into the other. You will need to look at which columns from the ACTIVE table should go into the NON_ACTIVE table and list them in the insert statements. |
|
|
|