May 18, 2011 at 3:05 am
I am having sql 2000. I have one insert query like below
insert into tbl1
select f1,f2 from tbl2
Here first field of tbl1 is created as primary key. But first field of tbl2 has the duplicate values. How can i select unique value from tbl2 for inserting into tbl1.
May 18, 2011 at 3:16 am
??
--INSERT INTO tbl1
SELECT f1, f2
FROM tbl2
GROUP BY f1, f2
May 18, 2011 at 6:35 am
There's a deeper problem here than group by.
If you have duplicate data in the insert it means that the query is not correct or you already have bad data in the tables. That's where I'd start looking rather than just doing a distinct.
May 18, 2011 at 9:49 am
Actually tbl2 may have duplicate row. But while selecting itself i want to avoid the duplicate data. It's like below
create table tbl(f int)
insert into tbl value(1)
insert into tbl value(1)
insert into tbl value(2)
insert into tbl value(3)
while using select statement (select f from tbl) it has to give the result as below
1
2
3
4
not like below
1
1
2
3
May 18, 2011 at 10:01 am
Identity columns could do the trick, if not that then row_number()
May 18, 2011 at 10:18 am
yes i agree we can do it by identity column. But i can not change the column constraint(i.e can not alter the column or add constraint) in production.
tbl2 is nothing but tmp table in my scenario and tbl1 is original. tbl1 is having primary key, but tbl2 is not having the contrains. tbl2 will get clean before running the task. while running the task it will populate some data on tbl2 finally tbl2 data will be moved to tbl1. So in this case i may get duplicate record in tbl2 but the insert query as below should not give the pk violation error
insert into tbl1
select f from tbl2
Can you please tell me possibilities or how to approach with this problem?
thanks for giving idea's.....
May 18, 2011 at 10:19 am
One more thing is i am using sql 2000 not 2005. row_number() function will be available in 2005 only.
am i correct?
May 18, 2011 at 10:34 am
SELECT DISTINCT *, IDENTITY(INT, 1, 1) rowid INTO #tmp2
May 18, 2011 at 10:47 am
you mean to say once i populate data on #tmp2 table by using your query ,then i will have to move #tmp2 to tbl2(My tmp table). After that i can use my insert query, which is used to move the data from tbl2 to tbl1 ???????
May 18, 2011 at 10:52 am
I was just showing you the identity function.
The idea is that once you have the "correct" data so just assign the identity with it (using the select into). At that point you can use insert into base from #tmp2.
It's hard to post a fully tested solution without having the table definitions, queries, required output and sample data.
May 19, 2011 at 12:40 am
Hi,
One more doubt. Is it possible to use user defined variable as a seed in identity function. Becuase i have three insert query in my procedure. I am using identity function to generate the id for primary key column in orginal table as below
create proc sample
as
declare @cnt int
begin
set @cnt=1
select identity(int,@cnt,1) as rowid,* #tmp_1 from tbl_tmp_1
insert tbl_org
select identity(int,@cnt,1) as rowid,* #tmp_1
select identity(int,@cnt,1) as rowid,* #tmp_2 from tbl_tmp_2
insert tbl_org
select identity(int,@cnt,1) as rowid,* #tmp_2
select identity(int,@cnt,1) as rowid,* #tmp_2 from tbl_tmp_2
insert tbl_org
select identity(int,@cnt,1) as rowid,* #tmp_2
end
Here i can use 1 instead of @cnt variable. But the second query will give the same 1, then it will give pk violation(Because tbl_org first column is primary key)
is it possible to use variable as a seed in identity function??????
May 19, 2011 at 5:33 am
you could do SET @cnt = @cnt + @@rowcount.
Or you could do
SELECT * IDENTITY() from (
select * 1
union all
select * 2
union all
select * 3) dtAll
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply