Monday, March 26, 2012
Duplicate record removal
this
SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
FROM USERS
GROUP BY [USER_NUMBER], [EMAIL]
HAVING Count([USER_NUMBER]) > 1
I can't figure out how to delete duplicate records and keep one distinct
record with smallest ID
Example:
TABLE: USERS
ID USER_NUMBER EMAIL
1 12 a@.a.com
2 12 a@.a.com
3 13 b@.b.com
4 14 c@.c.com
5 14 c@.c.com
Output:
ID USER_NUMBER EMAIL
1 12 a@.a.com
3 13 b@.b.com
4 14 c@.c.com
Thanks,
HowardDELETE FROM users
WHERE EXISTS
(SELECT *
FROM users AS U
WHERE U.user_number = users.user_number
AND U.email = users.email
AND U.id < users.id) ;
Nulls, if any, will be ignored.
David Portas
SQL Server MVP
--|||Hi Howard,
Try this statement
Delete From USERS
Where [USER_NUMBER] NOT In
(Select [USER_NUMBER] From USERS Users_Out Where [USER_NUMBER]
IN (Select Min(Users_In.[USER_NUMBER]) From USERS Users_In Where
Users_In.[EMAIL] = Users_Out.[EMAIL]
)
)
PLEASE NOTE : I have not tested this statement so please run the following
query and ensure that this is what you want to be deleted.
Select * From USERS
Where [USER_NUMBER] NOT In
(Select [USER_NUMBER] From USERS Users_Out Where [USER_NUMBER]
IN (Select Min(Users_In.[USER_NUMBER]) From USERS Users_In Where
Users_In.[EMAIL] = Users_Out.[EMAIL]
)
)
Do mail if this post help s you.
Vishal Khajuria
9886170165
IBM Bangalore
"Howard" wrote:
> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>
>|||thanks for your replies
I solved the problem.
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OlFbAlQ6FHA.3588@.TK2MSFTNGP15.phx.gbl...
>I can identify the duplicate rows and show number of duplication by using
>this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>|||Hi,
This will solve your query ...
delete from test where test.id not in (select top 1 b.id from test b
where (test.user_number=b.user_number) and
(test.email=b.email))
Regards,
Predrag Stojanovic
Analist/Programer
"Howard" <howdy0909@.yahoo.com> wrote in message
news:OlFbAlQ6FHA.3588@.TK2MSFTNGP15.phx.gbl...
> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>|||You missed out the ORDER BY. TOP 1 may not retrieve the minimum value
of ID unless you use ORDER BY.
Also, notice that this version will not delete any rows for a
particular user_name if a NULL ID exists for that user_name. Probably
that's not likely - I'd guess that ID is the PRIMARY KEY - but that's
one potential catch with TOP and with NOT IN.
David Portas
SQL Server MVP
--|||Here is the process:
1. SELECT * INTO #Temp1 FROM [Table1]
2. TRUNCATE TABLE [Table1]
3. CREATE UNIQUE INDEX [Index1] ON [Table1] (Unique Column Names) WITH
IGNORE_DUP_KEY
4. INSERT INTO [Table1] (Column Names) SELECT (Column Names) FROM [Table1]
5. DROP INDEX [Table1].[Index1]
Substitute your own names for those within the [].
Unique Column Names are those columns that uniquely identify each row.
Column Names is the full list of columns in your table.
HTH,
Mike
"Howard" wrote:
> I can identify the duplicate rows and show number of duplication by using
> this
> SELECT [USER_NUMBER], [EMAIL], Count([USER_NUMBER]) AS NUM
> FROM USERS
> GROUP BY [USER_NUMBER], [EMAIL]
> HAVING Count([USER_NUMBER]) > 1
>
> I can't figure out how to delete duplicate records and keep one distinct
> record with smallest ID
> Example:
> TABLE: USERS
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 2 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> 5 14 c@.c.com
>
> Output:
> ID USER_NUMBER EMAIL
> 1 12 a@.a.com
> 3 13 b@.b.com
> 4 14 c@.c.com
> Thanks,
> Howard
>
>
>sql
duplicate number
imported them into a table that is a clone of Axapta table.
I see that i have rows where account number is the same. So it appears that
they have inserted same customer with same accountnumber several times.
So lets say the columns are:
accountnr (varchar), customername (varchar), address(varchar)
Typical data is then:
1000, companyA, streetA
1000, companyA, streetB
1001, companyB, streetC
1001, companyB, streetE
So how can i select only one of them so that i have
1000, companyA, streetA
1001, companyB, streetC
DISTINCT wont do it since street is all different. In real table there are
some 93 columns so i simplifyed a lil. It appears to me that in duplicate
company registrations there are only small changes.
It does not matter wich one that gets selected in the SQl expression i am
looking for.
I have an SQL 2000 std server.
best regards
TrondTry:
select
accountnr
, customername
, min (address)
from
Customers
group by
accountnr
, customername
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Trond Hoiberg" <trond@.montanis.com> wrote in message
news:e0ds7O4EGHA.1032@.TK2MSFTNGP11.phx.gbl...
I have a table with customers that is imported from Axapta 3.0. I have
imported them into a table that is a clone of Axapta table.
I see that i have rows where account number is the same. So it appears that
they have inserted same customer with same accountnumber several times.
So lets say the columns are:
accountnr (varchar), customername (varchar), address(varchar)
Typical data is then:
1000, companyA, streetA
1000, companyA, streetB
1001, companyB, streetC
1001, companyB, streetE
So how can i select only one of them so that i have
1000, companyA, streetA
1001, companyB, streetC
DISTINCT wont do it since street is all different. In real table there are
some 93 columns so i simplifyed a lil. It appears to me that in duplicate
company registrations there are only small changes.
It does not matter wich one that gets selected in the SQl expression i am
looking for.
I have an SQL 2000 std server.
best regards
Trond|||Well yes i could but i simplifyed the real case since there are so many
columns. In some cases also address is the same. and the only variation
could appear in any of the remaining 90 columns.
So i thin i have to solve it using the accountnr.
Best regards
Trond
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:us8cyV4EGHA.3348@.TK2MSFTNGP10.phx.gbl...
> Try:
> select
> accountnr
> , customername
> , min (address)
> from
> Customers
> group by
> accountnr
> , customername
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Trond Hoiberg" <trond@.montanis.com> wrote in message
> news:e0ds7O4EGHA.1032@.TK2MSFTNGP11.phx.gbl...
> I have a table with customers that is imported from Axapta 3.0. I have
> imported them into a table that is a clone of Axapta table.
> I see that i have rows where account number is the same. So it appears
> that
> they have inserted same customer with same accountnumber several times.
> So lets say the columns are:
> accountnr (varchar), customername (varchar), address(varchar)
> Typical data is then:
> 1000, companyA, streetA
> 1000, companyA, streetB
> 1001, companyB, streetC
> 1001, companyB, streetE
> So how can i select only one of them so that i have
> 1000, companyA, streetA
> 1001, companyB, streetC
> DISTINCT wont do it since street is all different. In real table there are
> some 93 columns so i simplifyed a lil. It appears to me that in duplicate
> company registrations there are only small changes.
> It does not matter wich one that gets selected in the SQl expression i am
> looking for.
> I have an SQL 2000 std server.
> best regards
> Trond
>
>
>|||Hi
If you had a method of uniquely identifying each row then you could do
something like say an id column
SELECT A.accountnr, A.customername, A.address
FROM accounts A
JOIN ( SELECT MIN(B.id), B.accountnr, B.customername
FROM Accounts B
GROUP BY B.accountnr, B.customername ) D ON A.accountnr =
D.accountnr AND A.customername = D.customername
Alternatively you can just use (say) MIN for each column that you are not
grouping by:
SELECT A.accountnr, A.customername, MIN(A.address)
FROM accounts A
GROUP BY A.accountnr, A.customername
John
"Trond Hoiberg" <trond@.montanis.com> wrote in message
news:e0ds7O4EGHA.1032@.TK2MSFTNGP11.phx.gbl...
>I have a table with customers that is imported from Axapta 3.0. I have
>imported them into a table that is a clone of Axapta table.
> I see that i have rows where account number is the same. So it appears
> that they have inserted same customer with same accountnumber several
> times.
> So lets say the columns are:
> accountnr (varchar), customername (varchar), address(varchar)
> Typical data is then:
> 1000, companyA, streetA
> 1000, companyA, streetB
> 1001, companyB, streetC
> 1001, companyB, streetE
> So how can i select only one of them so that i have
> 1000, companyA, streetA
> 1001, companyB, streetC
> DISTINCT wont do it since street is all different. In real table there are
> some 93 columns so i simplifyed a lil. It appears to me that in duplicate
> company registrations there are only small changes.
> It does not matter wich one that gets selected in the SQl expression i am
> looking for.
> I have an SQL 2000 std server.
> best regards
> Trond
>
>
>|||Well, you'll have to come up with some other business rules to break the
ties.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Trond Hoiberg" <trond@.montanis.com> wrote in message
news:OzoFUZ4EGHA.3100@.tk2msftngp13.phx.gbl...
Well yes i could but i simplifyed the real case since there are so many
columns. In some cases also address is the same. and the only variation
could appear in any of the remaining 90 columns.
So i thin i have to solve it using the accountnr.
Best regards
Trond
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:us8cyV4EGHA.3348@.TK2MSFTNGP10.phx.gbl...
> Try:
> select
> accountnr
> , customername
> , min (address)
> from
> Customers
> group by
> accountnr
> , customername
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Trond Hoiberg" <trond@.montanis.com> wrote in message
> news:e0ds7O4EGHA.1032@.TK2MSFTNGP11.phx.gbl...
> I have a table with customers that is imported from Axapta 3.0. I have
> imported them into a table that is a clone of Axapta table.
> I see that i have rows where account number is the same. So it appears
> that
> they have inserted same customer with same accountnumber several times.
> So lets say the columns are:
> accountnr (varchar), customername (varchar), address(varchar)
> Typical data is then:
> 1000, companyA, streetA
> 1000, companyA, streetB
> 1001, companyB, streetC
> 1001, companyB, streetE
> So how can i select only one of them so that i have
> 1000, companyA, streetA
> 1001, companyB, streetC
> DISTINCT wont do it since street is all different. In real table there are
> some 93 columns so i simplifyed a lil. It appears to me that in duplicate
> company registrations there are only small changes.
> It does not matter wich one that gets selected in the SQl expression i am
> looking for.
> I have an SQL 2000 std server.
> best regards
> Trond
>
>
>|||Hi
It does not matter if the values in the columns are the same as the minimum
of two equal values will be that value. As account number is always constant
(between rows you wish to differentiate) you will either have to use a
different column e.g. something like the id column in my previous post or
generate the means to differentiate the columns such as inserting all the
values into a temporary table that has an id column.
John
"Trond Hoiberg" <trond@.montanis.com> wrote in message
news:OzoFUZ4EGHA.3100@.tk2msftngp13.phx.gbl...
> Well yes i could but i simplifyed the real case since there are so many
> columns. In some cases also address is the same. and the only variation
> could appear in any of the remaining 90 columns.
> So i thin i have to solve it using the accountnr.
> Best regards
> Trond
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:us8cyV4EGHA.3348@.TK2MSFTNGP10.phx.gbl...
>|||Hi
You may want to look at Itzik's article on assigning Row numbers to
non-unique rows
http://www.windowsitpro.com/Article...5828/45828.html
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:OhAtGF5EGHA.3384@.TK2MSFTNGP12.phx.gbl...
> Hi
> It does not matter if the values in the columns are the same as the
> minimum of two equal values will be that value. As account number is
> always constant (between rows you wish to differentiate) you will either
> have to use a different column e.g. something like the id column in my
> previous post or generate the means to differentiate the columns such as
> inserting all the values into a temporary table that has an id column.
> John
> "Trond Hoiberg" <trond@.montanis.com> wrote in message
> news:OzoFUZ4EGHA.3100@.tk2msftngp13.phx.gbl...
>|||Trond Hoiberg (trond@.montanis.com) writes:
> I have a table with customers that is imported from Axapta 3.0. I have
> imported them into a table that is a clone of Axapta table.
> I see that i have rows where account number is the same. So it appears
> that they have inserted same customer with same accountnumber several
> times.
> So lets say the columns are:
> accountnr (varchar), customername (varchar), address(varchar)
> Typical data is then:
> 1000, companyA, streetA
> 1000, companyA, streetB
> 1001, companyB, streetC
> 1001, companyB, streetE
> So how can i select only one of them so that i have
> 1000, companyA, streetA
> 1001, companyB, streetC
> DISTINCT wont do it since street is all different. In real table there are
> some 93 columns so i simplifyed a lil. It appears to me that in duplicate
> company registrations there are only small changes.
> It does not matter wich one that gets selected in the SQl expression i am
> looking for.
Are you sure of that? What if some information is old and no longer
current and some is new?
From the technical presumptions you have given you can do this:
ALTER TABLE tbl ADD ident int IDENTITY
Then:
SELECT a.accountnr, a.customername, ...
FROM tbl a
JOIN (SELECT accountnr, ident = MAX(ident)
FROM tbl
GROUP BY accountnr) AS b ON a.accountnr = b.accountnr
AND a.ident = b.ident
But whether this really is right from a business perspective, I am not
so sure.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Friday, February 24, 2012
DTS/SSIS too many tables warning and subsequent error
I need to bring over a large number of tables' records (200+ tables) with the Import/Export Wizard. The tables are being imported from MS Access. A separate script run previously will create the tables, so the DTS wizard is only to bring over the data from the Access tables into the empty SQL ones.
First, I get the warning that indicates "a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".Well, I proceed with the DTS and it tries to validate and takes a fair bit, but then it errors indicating:
"Error 0xc0202009: {2F0FABA0-5F4B-4310-97C0-76EA19893547}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)"
Can anyone shed any light on why I receive an "unspecified error" when tring to DTS a larger number of tables. It does not error, if I import 40 or so tables.
This was never an issue with SQL 2000 DTS.
Thanks
"a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".
The wizard told you that it may not be able to do it.
Hey,
Hah! Nice man. Well I hear you on that one, but why is this an issue with the SQL 2005 DTS and not the older SQL 2000 DTS, and is there any way around it?
Thanks
Sunday, February 19, 2012
DTS Wizard SQL 2005 - Enable Identity Insert
The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.
With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.
My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.
Thank you,
Ben
We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.
Unfortunately, there is no way to change this value for more than one table at the time, in the released
Thanks.
Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?
Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!
Any ideas as to when the service pack will be available?
Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!
Cheers.|||
I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.
It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?
Any help would be apprieciated.
Thanks.
|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.
Does anyone from Microsoft plan to address this?
DTS Wizard SQL 2005 - Enable Identity Insert
The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.
With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.
My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.
Thank you,
Ben
We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.
Unfortunately, there is no way to change this value for more than one table at the time, in the released
Thanks.
Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?
Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!
Any ideas as to when the service pack will be available?
Thanks!
|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!
Cheers.|||
I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.
It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?
Any help would be apprieciated.
Thanks.
|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||
It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.
Does anyone from Microsoft plan to address this?
|||Enable Identity Insert Still does not work when you set it for multiple tables! When will this be fixed?DTS Wizard SQL 2005 - Enable Identity Insert
The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.
With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.
My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.
Thank you,
Ben
We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.
Unfortunately, there is no way to change this value for more than one table at the time, in the released
Thanks.
Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?
Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!
Any ideas as to when the service pack will be available?
Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!
Cheers.|||
I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.
It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?
Any help would be apprieciated.
Thanks.
|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.
Does anyone from Microsoft plan to address this?
DTS Wizard SQL 2005 - Enable Identity Insert
The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.
With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.
My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.
Thank you,
Ben
We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.
Unfortunately, there is no way to change this value for more than one table at the time, in the released
Thanks.
Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?
Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!
Any ideas as to when the service pack will be available?
Thanks!|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!
Cheers.|||
I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.
It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?
Any help would be apprieciated.
Thanks.
|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.
Does anyone from Microsoft plan to address this?
DTS Wizard SQL 2005 - Enable Identity Insert
The problem is this: with SQL 2000 when bringing over the tables, the check box for 'Enable Identity Insert' for each of the tables was checked, this worked fine.
With SQL 2005, this checkbox is not checked hence, I have to click 'edit' for each of the table data being brought over and check the 'Enable Identity Insert' box manualy.
My question is whether there is a way to have SQL 2005 have this 'Enable Identity Insert Box' checked by default as it did with SQL 2000.
Thank you,
Ben
We had to turn off the Enable Identity Insert to make it consistent with the default value for Keep Identity property on OLE DB Source.
Unfortunately, there is no way to change this value for more than one table at the time, in the released
Thanks.
Just to confirm, with SP1 for SQL 2005, we will be able to do bulk edits and change the transfer settings, include the 'Enable Identity Insert'?
Thanks again.|||Yes, you will be able to set Enable Identity Insert for more than one table at once, and plus bulk edit a few other parameters (Drop and Recreate Table, Truncate Table and destination schemas).|||Thank you very much for your response Bob!
Any ideas as to when the service pack will be available?
Thanks!
|||I am not sure if an official date is published yet, but it should be in the first half of the next year.|||Appreciate it Bob!
Cheers.|||
I am having a very strange problem using the DTS Wizard. When I set Enable Identity Insert, the actual ID's on the source server are being changed by the target table. It is my understanding that when we set identity insert to on that the identity column on the target database will accept whatever ID is being passed to it.
It is not working that way with SP1 applied. But it used to work. Am I doing something wrong?
Any help would be apprieciated.
Thanks.
|||I'm having the same problem exporting from SQL 2005 to SQL 2000. It doesn't matter whether "Optimize for Many Tables" is checked or not, even if I am only copying 1 table- the identity values are not preserved.|||Just checking whether you have updated to the latest 2005 SP1. This should let you select batch tables and change the Identity insert properties for the DTS.
|||Yes, I am using SQL Studio with SP1.|||i have also the same problem, is any one know how to resolve that issue ?|||
It doesn't seem to work. It isn't changing my source tables, but it definately won't insert the appropriate value into the target table. I'm using sp 1 as well.
Does anyone from Microsoft plan to address this?