Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Inserting with subqueries

7 posts in General Discussion Last posting was on 2003-06-21 10:41:42.0Z
Richard Posted on 2003-04-24 16:57:20.0Z
From: Richard
Date: Thu, 24 Apr 2003 12:57:20 -0400
Newsgroups: ianywhere.public.general
Subject: Inserting with subqueries
Message-ID: <E55B5615D184FB29005D23A085256D12.005D23D185256D12@webforums>
Lines: 11
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:934
Article PK: 3320

I would like to populate a new table with data using a subquery ...One
column is only needed and the rest will be constant values.

Here is my first attempt <this does not work>

Insert Into DBA.Adm_Users Values
(DEFAULT,DEFAULT,DEFAULT,'DBA','DBA',(SELECT name From sysusers Where uid >
100),1)

Any Ideas?

Thanks!


Greg Fenton Posted on 2003-04-24 16:53:03.0Z
Date: Thu, 24 Apr 2003 12:53:03 -0400
From: Greg Fenton <greg.fenton.NO.SPAM@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.2) Gecko/20030312
X-Accept-Language: en-us, en
MIME-Version: 1.0
Subject: Re: Inserting with subqueries
References: <E55B5615D184FB29005D23A085256D12.005D23D185256D12@webforums>
In-Reply-To: <E55B5615D184FB29005D23A085256D12.005D23D185256D12@webforums>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <e4bXkJoCDHA.194@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: gfenton-t23.sybase.com 172.31.140.220
Lines: 21
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:935
Article PK: 2946


Richard wrote:
> I would like to populate a new table with data using a subquery ...One
> column is only needed and the rest will be constant values.
>
> Here is my first attempt <this does not work>
>
> Insert Into DBA.Adm_Users Values
> (DEFAULT,DEFAULT,DEFAULT,'DBA','DBA',(SELECT name From sysusers Where uid >
> 100),1)
>

How about:

INSERT INTO DBA.Adm_Users(
SELECT COL1_default, COL2_DEFAULT, COL3_DEFAULT,
'DBA','DBA', name, 1
FROM sysusers Where uid > 100)


where COL*_DEFAULT is the default value for that column.

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Richard Posted on 2003-04-24 18:11:16.0Z
From: Richard
Date: Thu, 24 Apr 2003 14:11:16 -0400
Newsgroups: ianywhere.public.general
Subject: Re: Inserting with subqueries
Message-ID: <92E69F3BD1C0991A0063E8B585256D12.00609C1F85256D12@webforums>
References: <E55B5615D184FB29005D23A085256D12.005D23D185256D12@webforums> <e4bXkJoCDHA.194@forums-2-dub>
Lines: 8
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:937
Article PK: 2944

I can't seem to get this to work...I am trying to insert
It doesn't know what the column is because it thinks it is from the
sysusers table.

INSERT INTO DBA.Adm_Users(
SELECT autoincrement, current timestamp ,current timestamp,
'DBA','DBA', name, 1
FROM sysusers Where uid > 100)


Greg Fenton Posted on 2003-04-24 18:21:57.0Z
Date: Thu, 24 Apr 2003 14:21:57 -0400
From: Greg Fenton <greg.fenton.NO.SPAM@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.2) Gecko/20030312
X-Accept-Language: en-us, en
MIME-Version: 1.0
Subject: Re: Inserting with subqueries
References: <E55B5615D184FB29005D23A085256D12.005D23D185256D12@webforums> <e4bXkJoCDHA.194@forums-2-dub> <92E69F3BD1C0991A0063E8B585256D12.00609C1F85256D12@webforums>
In-Reply-To: <92E69F3BD1C0991A0063E8B585256D12.00609C1F85256D12@webforums>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <OuDgP7oCDHA.81@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: gfenton-t23.sybase.com 172.31.140.220
Lines: 18
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:938
Article PK: 3318


Richard wrote:
>
> INSERT INTO DBA.Adm_Users(
> SELECT autoincrement, current timestamp ,current timestamp,
> 'DBA','DBA', name, 1
> FROM sysusers Where uid > 100)

Right, sorry. I didn't think about calculated defaults, I was
just thinking you had hard-value defaults like 'DBA'.

Jason's response has the right answer: don't specify values for
columns that have default values. That's the whole point of
giving a column a default value.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Jason Hinsperger Posted on 2003-04-24 17:50:37.0Z
From: "Jason Hinsperger" <NOJason_HinspergerSPAM@hotmail.com>
References: <E55B5615D184FB29005D23A085256D12.005D23D185256D12@webforums> <e4bXkJoCDHA.194@forums-2-dub>
Subject: Re: Inserting with subqueries
Date: Thu, 24 Apr 2003 13:50:37 -0400
Lines: 55
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <eEIItpoCDHA.220@forums-1-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: hinsperg-pc1.sybase.com 172.31.143.226
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:936
Article PK: 2949

You could also do the follwing (where col4-col7 are the names of the columns
for which you are supplying data):

INSERT INTO DBA.Adm_Users( col4, col5, col6, col7)
SELECT 'DBA','DBA', name, 1
FROM sysusers Where uid > 100

The engine will automatically fill in the default values for those columns
that are not specified.

--
Jason Hinsperger
International and Sustaining Engineering
iAnywhere Solutions

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
--

"Greg Fenton" <greg.fenton.NO.SPAM@ianywhere.com> wrote in message
news:e4bXkJoCDHA.194@forums-2-dub...
> Richard wrote:
> > I would like to populate a new table with data using a subquery ...One
> > column is only needed and the rest will be constant values.
> >
> > Here is my first attempt <this does not work>
> >
> > Insert Into DBA.Adm_Users Values
> > (DEFAULT,DEFAULT,DEFAULT,'DBA','DBA',(SELECT name From sysusers Where
uid >
> > 100),1)
> >
>
> How about:
>
> INSERT INTO DBA.Adm_Users(
> SELECT COL1_default, COL2_DEFAULT, COL3_DEFAULT,
> 'DBA','DBA', name, 1
> FROM sysusers Where uid > 100)
>
>
> where COL*_DEFAULT is the default value for that column.
>
> Hope this helps,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/
>


Richard Posted on 2003-04-24 18:41:58.0Z
From: Richard
Date: Thu, 24 Apr 2003 14:41:58 -0400
Newsgroups: ianywhere.public.general
Subject: Re: Inserting with subqueries
Message-ID: <928E273E0974BAEB0066B82885256D12.0066494185256D12@webforums>
References: <E55B5615D184FB29005D23A085256D12.005D23D185256D12@webforums> <e4bXkJoCDHA.194@forums-2-dub> <eEIItpoCDHA.220@forums-1-dub>
Lines: 1
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:939
Article PK: 2945

That worked...Thanks!


JSmith Posted on 2003-06-21 10:41:42.0Z
From: "JSmith" <minxkitty1@hotmail.com>
References: <E55B5615D184FB29005D23A085256D12.005D23D185256D12@webforums> <e4bXkJoCDHA.194@forums-2-dub> <eEIItpoCDHA.220@forums-1-dub> <928E273E0974BAEB0066B82885256D12.0066494185256D12@webforums>
Subject: Re: Inserting with subqueries
Date: Sat, 21 Jun 2003 03:41:42 -0700
Lines: 6
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <uYii#K#NDHA.363@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: ts46-01-qdr3701.trlck.ca.charter.com 68.116.94.117
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1224
Article PK: 3471


<Richard> wrote in message
news:928E273E0974BAEB0066B82885256D12.0066494185256D12@webforums...
> That worked...Thanks!