Insert in every ID

29 Jun 2006 2:57 PM

Hitesh

Hi,

I have a table with following raws.

account   Channel

abc1           PT
abc1           ST
abc1           ZZ
xyz2           PT
xyz2           ST
xyz2           ZZ
abc2           PT
abc2           ST
abc2           ZZ
xyz3           PT
xyz3           ST
xyz3           ZZ

I want to insert two more channels, let's say BD and JK for every
account in this table...
I know I could do this manually one by one account but I have so many
accounts in this table and it could take few hours to do that way... is
there any other way I can do this in one click?

Thank you in advance,
hj

29 Jun 2006 3:02 PM

SQL Menace

One way

insert table (account,Channel )
select distinct account,'BD'
from table
union all
select distinct account,'JK'
from table

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Hitesh wrote:
Show quote

> Hi,
>
> I have a table with following raws.
>
> account   Channel
>
> abc1           PT
> abc1           ST
> abc1           ZZ
> xyz2           PT
> xyz2           ST
> xyz2           ZZ
> abc2           PT
> abc2           ST
> abc2           ZZ
> xyz3           PT
> xyz3           ST
> xyz3           ZZ
>
>
> I want to insert two more channels, let's say BD and JK for every
> account in this table...
> I know I could do this manually one by one account but I have so many
> accounts in this table and it could take few hours to do that way... is
> there any other way I can do this in one click?
>
> Thank you in advance,
> hj

29 Jun 2006 3:29 PM

Hitesh

Thank you for the reply. That works fine but here is a lilttle twist
that I should have mentioned first time....

There are account which already has two channel BD and JK... I do not
want to create dup entries for them....

I tried to run your query on sample table in northwind and it works
like charm...
but with creates dup

hj

SQL Menace wrote:
Show quote

> One way
>
> insert table (account,Channel )
> select distinct account,'BD'
> from table
> union all
> select distinct account,'JK'
> from table
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> Hitesh wrote:
> > Hi,
> >
> > I have a table with following raws.
> >
> > account   Channel
> >
> > abc1           PT
> > abc1           ST
> > abc1           ZZ
> > xyz2           PT
> > xyz2           ST
> > xyz2           ZZ
> > abc2           PT
> > abc2           ST
> > abc2           ZZ
> > xyz3           PT
> > xyz3           ST
> > xyz3           ZZ
> >
> >
> > I want to insert two more channels, let's say BD and JK for every
> > account in this table...
> > I know I could do this manually one by one account but I have so many
> > accounts in this table and it could take few hours to do that way... is
> > there any other way I can do this in one click?
> >
> > Thank you in advance,
> > hj

29 Jun 2006 3:32 PM

SQL Menace

add a WHERE clause

insert table (account,Channel )
select distinct account,'BD'
from table
where Channel <> 'BD'
union all
select distinct account,'JK'
from table
where Channel <> 'JK'

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Hitesh wrote:
Show quote

> Thank you for the reply. That works fine but here is a lilttle twist
> that I should have mentioned first time....
>
> There are account which already has two channel BD and JK... I do not
> want to create dup entries for them....
>
> I tried to run your query on sample table in northwind and it works
> like charm...
> but with creates dup
>
> hj
>
>
> SQL Menace wrote:
> > One way
> >
> > insert table (account,Channel )
> > select distinct account,'BD'
> > from table
> > union all
> > select distinct account,'JK'
> > from table
> >
> > Denis the SQL Menace
> > http://sqlservercode.blogspot.com/
> >
> >
> > Hitesh wrote:
> > > Hi,
> > >
> > > I have a table with following raws.
> > >
> > > account   Channel
> > >
> > > abc1           PT
> > > abc1           ST
> > > abc1           ZZ
> > > xyz2           PT
> > > xyz2           ST
> > > xyz2           ZZ
> > > abc2           PT
> > > abc2           ST
> > > abc2           ZZ
> > > xyz3           PT
> > > xyz3           ST
> > > xyz3           ZZ
> > >
> > >
> > > I want to insert two more channels, let's say BD and JK for every
> > > account in this table...
> > > I know I could do this manually one by one account but I have so many
> > > accounts in this table and it could take few hours to do that way... is
> > > there any other way I can do this in one click?
> > >
> > > Thank you in advance,
> > > hj

29 Jun 2006 3:52 PM

Hitesh

some how it doesn't like that...
when I ran the query it still created dups..

so I deleted all the BD and JK from the table and ran following query:

use my_db
select distinct account,'BD'
from table
where channel <> 'BD'
ORDER BY Account

and to my surprise I saw the same number of accounts in result...

and when I raw

select * from table where channel='Bd' or channel ='JK'
Order BY Account, Channel

I do see dups.

but if I run this one:

use my_db
select distinct account,'BD'
from table
where channel = 'BD'
ORDER BY Account

and same raws comes up in the result pan..

SQL Menace wrote:
Show quote

> add a WHERE clause
>
> insert table (account,Channel )
> select distinct account,'BD'
> from table
> where Channel <> 'BD'
> union all
> select distinct account,'JK'
> from table
> where Channel <> 'JK'
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>

29 Jun 2006 3:47 PM

Roy Harvey

insert table (account,Channel )
SELECT *
FROM
(select distinct account,'BD'
from table
union all
select distinct account,'JK'
from table) as X
WHERE NOT EXISTS
(select * from table as T
where X.account = T.account
and X.Channel = T.Channel)

Roy Harvey
Beacon Falls, CT

Show quote

On 29 Jun 2006 08:29:06 -0700, "Hitesh" <hitesh***@gmail.com> wrote:

>
>Thank you for the reply. That works fine but here is a lilttle twist
>that I should have mentioned first time....
>
>There are account which already has two channel BD and JK... I do not
>want to create dup entries for them....
>
>I tried to run your query on sample table in northwind and it works
>like charm...
>but with creates dup
>
>hj
>
>
>SQL Menace wrote:
>> One way
>>
>> insert table (account,Channel )
>> select distinct account,'BD'
>> from table
>> union all
>> select distinct account,'JK'
>> from table
>>
>> Denis the SQL Menace
>> http://sqlservercode.blogspot.com/
>>
>>
>> Hitesh wrote:
>> > Hi,
>> >
>> > I have a table with following raws.
>> >
>> > account   Channel
>> >
>> > abc1           PT
>> > abc1           ST
>> > abc1           ZZ
>> > xyz2           PT
>> > xyz2           ST
>> > xyz2           ZZ
>> > abc2           PT
>> > abc2           ST
>> > abc2           ZZ
>> > xyz3           PT
>> > xyz3           ST
>> > xyz3           ZZ
>> >
>> >
>> > I want to insert two more channels, let's say BD and JK for every
>> > account in this table...
>> > I know I could do this manually one by one account but I have so many
>> > accounts in this table and it could take few hours to do that way... is
>> > there any other way I can do this in one click?
>> >
>> > Thank you in advance,
>> > hj

29 Jun 2006 4:13 PM

Hitesh

I am getting an error:
Server: Msg 8155, Level 16, State 2, Line 2
No column was specified for column 2 of 'X'.

Roy Harvey wrote:
Show quote

> insert table (account,Channel )
> SELECT *
> FROM
> (select distinct account,'BD'
> from table
> union all
> select distinct account,'JK'
> from table) as X
> WHERE NOT EXISTS
> (select * from table as T
> where X.account = T.account
> and X.Channel = T.Channel)
>
> Roy Harvey
> Beacon Falls, CT
>
> On 29 Jun 2006 08:29:06 -0700, "Hitesh" <hitesh***@gmail.com> wrote:
>
> >
> >Thank you for the reply. That works fine but here is a lilttle twist
> >that I should have mentioned first time....
> >
> >There are account which already has two channel BD and JK... I do not
> >want to create dup entries for them....
> >
> >I tried to run your query on sample table in northwind and it works
> >like charm...
> >but with creates dup
> >
> >hj
> >
> >
> >SQL Menace wrote:
> >> One way
> >>
> >> insert table (account,Channel )
> >> select distinct account,'BD'
> >> from table
> >> union all
> >> select distinct account,'JK'
> >> from table
> >>
> >> Denis the SQL Menace
> >> http://sqlservercode.blogspot.com/
> >>
> >>
> >> Hitesh wrote:
> >> > Hi,
> >> >
> >> > I have a table with following raws.
> >> >
> >> > account   Channel
> >> >
> >> > abc1           PT
> >> > abc1           ST
> >> > abc1           ZZ
> >> > xyz2           PT
> >> > xyz2           ST
> >> > xyz2           ZZ
> >> > abc2           PT
> >> > abc2           ST
> >> > abc2           ZZ
> >> > xyz3           PT
> >> > xyz3           ST
> >> > xyz3           ZZ
> >> >
> >> >
> >> > I want to insert two more channels, let's say BD and JK for every
> >> > account in this table...
> >> > I know I could do this manually one by one account but I have so many
> >> > accounts in this table and it could take few hours to do that way... is
> >> > there any other way I can do this in one click?
> >> >
> >> > Thank you in advance,
> >> > hj

29 Jun 2006 5:03 PM

Roy Harvey

insert table (account,Channel )
SELECT *
FROM
(select distinct account,'BD' as Channel
from table
union all
select distinct account,'JK'
from table) as X
WHERE NOT EXISTS
(select * from table as T
where X.account = T.account
and X.Channel = T.Channel)

Roy

Show quote

On 29 Jun 2006 09:13:06 -0700, "Hitesh" <hitesh***@gmail.com> wrote:

>
>I am getting an error:
>Server: Msg 8155, Level 16, State 2, Line 2
>No column was specified for column 2 of 'X'.
>
>
>
>Roy Harvey wrote:
>> insert table (account,Channel )
>> SELECT *
>> FROM
>> (select distinct account,'BD'
>> from table
>> union all
>> select distinct account,'JK'
>> from table) as X
>> WHERE NOT EXISTS
>> (select * from table as T
>> where X.account = T.account
>> and X.Channel = T.Channel)
>>
>> Roy Harvey
>> Beacon Falls, CT
>>
>> On 29 Jun 2006 08:29:06 -0700, "Hitesh" <hitesh***@gmail.com> wrote:
>>
>> >
>> >Thank you for the reply. That works fine but here is a lilttle twist
>> >that I should have mentioned first time....
>> >
>> >There are account which already has two channel BD and JK... I do not
>> >want to create dup entries for them....
>> >
>> >I tried to run your query on sample table in northwind and it works
>> >like charm...
>> >but with creates dup
>> >
>> >hj
>> >
>> >
>> >SQL Menace wrote:
>> >> One way
>> >>
>> >> insert table (account,Channel )
>> >> select distinct account,'BD'
>> >> from table
>> >> union all
>> >> select distinct account,'JK'
>> >> from table
>> >>
>> >> Denis the SQL Menace
>> >> http://sqlservercode.blogspot.com/
>> >>
>> >>
>> >> Hitesh wrote:
>> >> > Hi,
>> >> >
>> >> > I have a table with following raws.
>> >> >
>> >> > account   Channel
>> >> >
>> >> > abc1           PT
>> >> > abc1           ST
>> >> > abc1           ZZ
>> >> > xyz2           PT
>> >> > xyz2           ST
>> >> > xyz2           ZZ
>> >> > abc2           PT
>> >> > abc2           ST
>> >> > abc2           ZZ
>> >> > xyz3           PT
>> >> > xyz3           ST
>> >> > xyz3           ZZ
>> >> >
>> >> >
>> >> > I want to insert two more channels, let's say BD and JK for every
>> >> > account in this table...
>> >> > I know I could do this manually one by one account but I have so many
>> >> > accounts in this table and it could take few hours to do that way... is
>> >> > there any other way I can do this in one click?
>> >> >
>> >> > Thank you in advance,
>> >> > hj

29 Jun 2006 5:19 PM

Hitesh

Thank you. It worked!

Roy Harvey wrote:
Show quote

> insert table (account,Channel )
> SELECT *
> FROM
> (select distinct account,'BD' as Channel
> from table
> union all
> select distinct account,'JK'
> from table) as X
> WHERE NOT EXISTS
> (select * from table as T
> where X.account = T.account
> and X.Channel = T.Channel)
>
> Roy
>
> On 29 Jun 2006 09:13:06 -0700, "Hitesh" <hitesh***@gmail.com> wrote:
>
> >
> >I am getting an error:
> >Server: Msg 8155, Level 16, State 2, Line 2
> >No column was specified for column 2 of 'X'.
> >
> >
> >
> >Roy Harvey wrote:
> >> insert table (account,Channel )
> >> SELECT *
> >> FROM
> >> (select distinct account,'BD'
> >> from table
> >> union all
> >> select distinct account,'JK'
> >> from table) as X
> >> WHERE NOT EXISTS
> >> (select * from table as T
> >> where X.account = T.account
> >> and X.Channel = T.Channel)
> >>
> >> Roy Harvey
> >> Beacon Falls, CT
> >>
> >> On 29 Jun 2006 08:29:06 -0700, "Hitesh" <hitesh***@gmail.com> wrote:
> >>
> >> >
> >> >Thank you for the reply. That works fine but here is a lilttle twist
> >> >that I should have mentioned first time....
> >> >
> >> >There are account which already has two channel BD and JK... I do not
> >> >want to create dup entries for them....
> >> >
> >> >I tried to run your query on sample table in northwind and it works
> >> >like charm...
> >> >but with creates dup
> >> >
> >> >hj
> >> >
> >> >
> >> >SQL Menace wrote:
> >> >> One way
> >> >>
> >> >> insert table (account,Channel )
> >> >> select distinct account,'BD'
> >> >> from table
> >> >> union all
> >> >> select distinct account,'JK'
> >> >> from table
> >> >>
> >> >> Denis the SQL Menace
> >> >> http://sqlservercode.blogspot.com/
> >> >>
> >> >>
> >> >> Hitesh wrote:
> >> >> > Hi,
> >> >> >
> >> >> > I have a table with following raws.
> >> >> >
> >> >> > account   Channel
> >> >> >
> >> >> > abc1           PT
> >> >> > abc1           ST
> >> >> > abc1           ZZ
> >> >> > xyz2           PT
> >> >> > xyz2           ST
> >> >> > xyz2           ZZ
> >> >> > abc2           PT
> >> >> > abc2           ST
> >> >> > abc2           ZZ
> >> >> > xyz3           PT
> >> >> > xyz3           ST
> >> >> > xyz3           ZZ
> >> >> >
> >> >> >
> >> >> > I want to insert two more channels, let's say BD and JK for every
> >> >> > account in this table...
> >> >> > I know I could do this manually one by one account but I have so many
> >> >> > accounts in this table and it could take few hours to do that way... is
> >> >> > there any other way I can do this in one click?
> >> >> >
> >> >> > Thank you in advance,
> >> >> > hj

29 Jun 2006 4:26 PM

--CELKO--

>> here is a lilttle twist that I should have mentioned .. <<

Actually, you should have posted DDL, so we could see that you have no
keys and that is not a table at all.

>> There is an account which already has two channel BD and JK... I do not want to create dup entries for them.. <<

That is what keys are for!! If you had a real table instead of a poor
imitation of a sequential file, it might look like this:

CREATE TABLE AccountChannels
(account_nbr INTEGER NOT NULL
REFERENCES Accounts(account_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
channel_cd CHAR(2) NOT NULL
CHECK (channel_cd IN ('BD', 'JK', 'ST', 'ZZ', 'PT'),
PRIMARY KEY (account_nbr, channel_cd));

INSERT INTO AccountChannels (account_nbr, channel_cd
SELECT DISTINCT account_nbr, 'BD'
FROM AccountChannels
WHERE account_nbr NOT IN (98, 99, ..)
UNION ALL
SELECT DISTINCT account_nbr, 'JK'
FROM AccountChannels
WHERE account_nbr NOT IN (98, 99, ..) ;

It is not enough to mop the floor; you must fix the leak.