Quick Newbie - WHERE STATEMENT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

High Availability (2008)

Quick Newbie - WHERE STATEMENT

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_ACTIVE

I am very new to SQL so easier instructions is more than welcome!

I have had a go:

INSERT INTO NON_ACTIVE (*.)
SELECT .*
FROM ACTIVE
WHERE Year(Date) =< 2004
WHERE bActive = 0

HELP

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 ACTIVE
WHERE Year(Date) =< <= 2004
WHERE  AND  bActive = 0

Too many red-lines! Let me rewrite it.

INSERT INTO NON_ACTIVE
SELECT *
FROM ACTIVE
WHERE Year(Date) <= 2004
AND 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) =< 2004
AND bActive = 0

starydynamo
Starting Member

12 Posts

Posted - 2011-04-11 : 09:40:13

I am getting the issue

State 1, Line 1
r the identity column in table 'NON_ACTIVE' can only be specified when a column list is used and IDENTITY_INSERT is ON

Any 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)
SELECT
col1, col2
FROM ACTIVE
WHERE Year(Date) <= 2004
AND 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 moment
Year(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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources