Click to See Complete Forum and Search --> : SP for data append
David Satz
04-10-2002, 09:43 AM
you need to do an INSERT/SELECT statement and use the LEFT() function to
make sure that the data fits into each column without truncation.
also, you can use this SQL to build a list of columns in your table
SELECT ',' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your table name here'
order by ORDINAL_POSITION
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"rkbnair" <rkannale@sol.com> wrote in message news:3cb4389d$1@10.1.10.29...
>
> Hi,
>
> I'm new to the Stored procedures. I want to establish the following.
>
> I want to append data from Table_A to Table_B. Both of these tables have
> identical columns except that Table_B have some extra fields. Also,
Table_B
> field size may be different for some fields.
>
> Where can I hunt a sample code in order to do this?
>
> Finally I want to call this from VB.
>
> Thanks.
>
rkbnair
04-10-2002, 10:05 AM
Hi,
I'm new to the Stored procedures. I want to establish the following.
I want to append data from Table_A to Table_B. Both of these tables have
identical columns except that Table_B have some extra fields. Also, Table_B
field size may be different for some fields.
Where can I hunt a sample code in order to do this?
Finally I want to call this from VB.
Thanks.
rkbnair
04-10-2002, 11:58 AM
1. SELECT lng_id,str_client FROM myTable
What will be the syntax if I need myTable inside a varible?
also,
FETCH NEXT FROM cur_adrdts INTO
@lng_id, @str_client
Can I put a * in place of '@lng_id, @str_client' ?
Thanks.
"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>you need to do an INSERT/SELECT statement and use the LEFT() function to
>make sure that the data fits into each column without truncation.
>
>also, you can use this SQL to build a list of columns in your table
>
>SELECT ',' + COLUMN_NAME
>FROM INFORMATION_SCHEMA.COLUMNS
>WHERE TABLE_NAME = 'your table name here'
>order by ORDINAL_POSITION
>
>--
>HTH,
>David Satz
>Principal Web Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>"rkbnair" <rkannale@sol.com> wrote in message news:3cb4389d$1@10.1.10.29...
>>
>> Hi,
>>
>> I'm new to the Stored procedures. I want to establish the following.
>>
>> I want to append data from Table_A to Table_B. Both of these tables have
>> identical columns except that Table_B have some extra fields. Also,
>Table_B
>> field size may be different for some fields.
>>
>> Where can I hunt a sample code in order to do this?
>>
>> Finally I want to call this from VB.
>>
>> Thanks.
>>
>
>
David Satz
04-10-2002, 12:55 PM
1. EXEC( "SELECT lng_id,str_client FROM " + myTable )
see also http://vyaskn.tripod.com/programming_faq.htm#q9
2. no - AFAIK - FETCH requires variables
"rkbnair" <rkannale@sol.com> wrote in message news:3cb45316$1@10.1.10.29...
>
> 1. SELECT lng_id,str_client FROM myTable
>
> What will be the syntax if I need myTable inside a varible?
>
> also,
> FETCH NEXT FROM cur_adrdts INTO
> @lng_id, @str_client
> Can I put a * in place of '@lng_id, @str_client' ?
> Thanks.
>
> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
> >you need to do an INSERT/SELECT statement and use the LEFT() function to
> >make sure that the data fits into each column without truncation.
> >
> >also, you can use this SQL to build a list of columns in your table
> >
> >SELECT ',' + COLUMN_NAME
> >FROM INFORMATION_SCHEMA.COLUMNS
> >WHERE TABLE_NAME = 'your table name here'
> >order by ORDINAL_POSITION
> >
> >--
> >HTH,
> >David Satz
> >Principal Web Engineer
> >Hyperion Solutions
> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
> >(Please reply to group only - emails answered rarely)
> >-----------------------------------------------------------------
> >"rkbnair" <rkannale@sol.com> wrote in message
news:3cb4389d$1@10.1.10.29...
> >>
> >> Hi,
> >>
> >> I'm new to the Stored procedures. I want to establish the following.
> >>
> >> I want to append data from Table_A to Table_B. Both of these tables
have
> >> identical columns except that Table_B have some extra fields. Also,
> >Table_B
> >> field size may be different for some fields.
> >>
> >> Where can I hunt a sample code in order to do this?
> >>
> >> Finally I want to call this from VB.
> >>
> >> Thanks.
> >>
> >
> >
>
David Satz
04-10-2002, 05:17 PM
you can do EXEC( "SELECT * FROM " + myTable ) but will not be able to use a
cursor for this. I would need to know a lot more info/details to answer...
"rkbnair" <rkan@sol.com> wrote in message news:3cb4a153$1@10.1.10.29...
>
> What happends if I do not know how many columns are and want to retrieve
all
> fields? Should I create 100 of them?
>
>
> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
> >1. EXEC( "SELECT lng_id,str_client FROM " + myTable )
> >
> >see also http://vyaskn.tripod.com/programming_faq.htm#q9
> >
> >2. no - AFAIK - FETCH requires variables
> >
> >"rkbnair" <rkannale@sol.com> wrote in message
news:3cb45316$1@10.1.10.29...
> >>
> >> 1. SELECT lng_id,str_client FROM myTable
> >>
> >> What will be the syntax if I need myTable inside a varible?
> >>
> >> also,
> >> FETCH NEXT FROM cur_adrdts INTO
> >> @lng_id, @str_client
> >> Can I put a * in place of '@lng_id, @str_client' ?
> >> Thanks.
> >>
> >> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
> >> >you need to do an INSERT/SELECT statement and use the LEFT() function
> to
> >> >make sure that the data fits into each column without truncation.
> >> >
> >> >also, you can use this SQL to build a list of columns in your table
> >> >
> >> >SELECT ',' + COLUMN_NAME
> >> >FROM INFORMATION_SCHEMA.COLUMNS
> >> >WHERE TABLE_NAME = 'your table name here'
> >> >order by ORDINAL_POSITION
> >> >
> >> >--
> >> >HTH,
> >> >David Satz
> >> >Principal Web Engineer
> >> >Hyperion Solutions
> >> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
> >> >(Please reply to group only - emails answered rarely)
> >> >-----------------------------------------------------------------
> >> >"rkbnair" <rkannale@sol.com> wrote in message
> >news:3cb4389d$1@10.1.10.29...
> >> >>
> >> >> Hi,
> >> >>
> >> >> I'm new to the Stored procedures. I want to establish the following.
> >> >>
> >> >> I want to append data from Table_A to Table_B. Both of these tables
> >have
> >> >> identical columns except that Table_B have some extra fields. Also,
> >> >Table_B
> >> >> field size may be different for some fields.
> >> >>
> >> >> Where can I hunt a sample code in order to do this?
> >> >>
> >> >> Finally I want to call this from VB.
> >> >>
> >> >> Thanks.
> >> >>
> >> >
> >> >
> >>
> >
> >
>
rkbnair
04-10-2002, 05:32 PM
What happends if I do not know how many columns are and want to retrieve all
fields? Should I create 100 of them?
"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>1. EXEC( "SELECT lng_id,str_client FROM " + myTable )
>
>see also http://vyaskn.tripod.com/programming_faq.htm#q9
>
>2. no - AFAIK - FETCH requires variables
>
>"rkbnair" <rkannale@sol.com> wrote in message news:3cb45316$1@10.1.10.29...
>>
>> 1. SELECT lng_id,str_client FROM myTable
>>
>> What will be the syntax if I need myTable inside a varible?
>>
>> also,
>> FETCH NEXT FROM cur_adrdts INTO
>> @lng_id, @str_client
>> Can I put a * in place of '@lng_id, @str_client' ?
>> Thanks.
>>
>> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >you need to do an INSERT/SELECT statement and use the LEFT() function
to
>> >make sure that the data fits into each column without truncation.
>> >
>> >also, you can use this SQL to build a list of columns in your table
>> >
>> >SELECT ',' + COLUMN_NAME
>> >FROM INFORMATION_SCHEMA.COLUMNS
>> >WHERE TABLE_NAME = 'your table name here'
>> >order by ORDINAL_POSITION
>> >
>> >--
>> >HTH,
>> >David Satz
>> >Principal Web Engineer
>> >Hyperion Solutions
>> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>> >(Please reply to group only - emails answered rarely)
>> >-----------------------------------------------------------------
>> >"rkbnair" <rkannale@sol.com> wrote in message
>news:3cb4389d$1@10.1.10.29...
>> >>
>> >> Hi,
>> >>
>> >> I'm new to the Stored procedures. I want to establish the following.
>> >>
>> >> I want to append data from Table_A to Table_B. Both of these tables
>have
>> >> identical columns except that Table_B have some extra fields. Also,
>> >Table_B
>> >> field size may be different for some fields.
>> >>
>> >> Where can I hunt a sample code in order to do this?
>> >>
>> >> Finally I want to call this from VB.
>> >>
>> >> Thanks.
>> >>
>> >
>> >
>>
>
>
rkbnair
04-11-2002, 06:44 PM
David,
What is the best way to append data from one table to another table?
The table has at least one column which is of type identity(autoincrement)
?
"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>you can do EXEC( "SELECT * FROM " + myTable ) but will not be able to use
a
>cursor for this. I would need to know a lot more info/details to answer...
>
>"rkbnair" <rkan@sol.com> wrote in message news:3cb4a153$1@10.1.10.29...
>>
>> What happends if I do not know how many columns are and want to retrieve
>all
>> fields? Should I create 100 of them?
>>
>>
>> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >1. EXEC( "SELECT lng_id,str_client FROM " + myTable )
>> >
>> >see also http://vyaskn.tripod.com/programming_faq.htm#q9
>> >
>> >2. no - AFAIK - FETCH requires variables
>> >
>> >"rkbnair" <rkannale@sol.com> wrote in message
>news:3cb45316$1@10.1.10.29...
>> >>
>> >> 1. SELECT lng_id,str_client FROM myTable
>> >>
>> >> What will be the syntax if I need myTable inside a varible?
>> >>
>> >> also,
>> >> FETCH NEXT FROM cur_adrdts INTO
>> >> @lng_id, @str_client
>> >> Can I put a * in place of '@lng_id, @str_client' ?
>> >> Thanks.
>> >>
>> >> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>> >> >you need to do an INSERT/SELECT statement and use the LEFT() function
>> to
>> >> >make sure that the data fits into each column without truncation.
>> >> >
>> >> >also, you can use this SQL to build a list of columns in your table
>> >> >
>> >> >SELECT ',' + COLUMN_NAME
>> >> >FROM INFORMATION_SCHEMA.COLUMNS
>> >> >WHERE TABLE_NAME = 'your table name here'
>> >> >order by ORDINAL_POSITION
>> >> >
>> >> >--
>> >> >HTH,
>> >> >David Satz
>> >> >Principal Web Engineer
>> >> >Hyperion Solutions
>> >> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
>> >> >(Please reply to group only - emails answered rarely)
>> >> >-----------------------------------------------------------------
>> >> >"rkbnair" <rkannale@sol.com> wrote in message
>> >news:3cb4389d$1@10.1.10.29...
>> >> >>
>> >> >> Hi,
>> >> >>
>> >> >> I'm new to the Stored procedures. I want to establish the following.
>> >> >>
>> >> >> I want to append data from Table_A to Table_B. Both of these tables
>> >have
>> >> >> identical columns except that Table_B have some extra fields. Also,
>> >> >Table_B
>> >> >> field size may be different for some fields.
>> >> >>
>> >> >> Where can I hunt a sample code in order to do this?
>> >> >>
>> >> >> Finally I want to call this from VB.
>> >> >>
>> >> >> Thanks.
>> >> >>
>> >> >
>> >> >
>> >>
>> >
>> >
>>
>
>
David Satz
04-12-2002, 09:52 AM
you can use an INSERT/SELECT statement. If there is an identity column you
want insert into the table (there can only be 1 identity column per table),
then you need to SET IDENTITY_INSERT ON before the insert
HTH,
Dave
"rkbnair" <rkannale@sol.com> wrote in message news:3cb603c7$1@10.1.10.29...
>
> David,
> What is the best way to append data from one table to another table?
>
> The table has at least one column which is of type identity(autoincrement)
> ?
>
> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
> >you can do EXEC( "SELECT * FROM " + myTable ) but will not be able to use
> a
> >cursor for this. I would need to know a lot more info/details to
answer...
> >
> >"rkbnair" <rkan@sol.com> wrote in message news:3cb4a153$1@10.1.10.29...
> >>
> >> What happends if I do not know how many columns are and want to
retrieve
> >all
> >> fields? Should I create 100 of them?
> >>
> >>
> >> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
> >> >1. EXEC( "SELECT lng_id,str_client FROM " + myTable )
> >> >
> >> >see also http://vyaskn.tripod.com/programming_faq.htm#q9
> >> >
> >> >2. no - AFAIK - FETCH requires variables
> >> >
> >> >"rkbnair" <rkannale@sol.com> wrote in message
> >news:3cb45316$1@10.1.10.29...
> >> >>
> >> >> 1. SELECT lng_id,str_client FROM myTable
> >> >>
> >> >> What will be the syntax if I need myTable inside a varible?
> >> >>
> >> >> also,
> >> >> FETCH NEXT FROM cur_adrdts INTO
> >> >> @lng_id, @str_client
> >> >> Can I put a * in place of '@lng_id, @str_client' ?
> >> >> Thanks.
> >> >>
> >> >> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
> >> >> >you need to do an INSERT/SELECT statement and use the LEFT()
function
> >> to
> >> >> >make sure that the data fits into each column without truncation.
> >> >> >
> >> >> >also, you can use this SQL to build a list of columns in your table
> >> >> >
> >> >> >SELECT ',' + COLUMN_NAME
> >> >> >FROM INFORMATION_SCHEMA.COLUMNS
> >> >> >WHERE TABLE_NAME = 'your table name here'
> >> >> >order by ORDINAL_POSITION
> >> >> >
> >> >> >--
> >> >> >HTH,
> >> >> >David Satz
> >> >> >Principal Web Engineer
> >> >> >Hyperion Solutions
> >> >> >{ SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
> >> >> >(Please reply to group only - emails answered rarely)
> >> >> >-----------------------------------------------------------------
> >> >> >"rkbnair" <rkannale@sol.com> wrote in message
> >> >news:3cb4389d$1@10.1.10.29...
> >> >> >>
> >> >> >> Hi,
> >> >> >>
> >> >> >> I'm new to the Stored procedures. I want to establish the
following.
> >> >> >>
> >> >> >> I want to append data from Table_A to Table_B. Both of these
tables
> >> >have
> >> >> >> identical columns except that Table_B have some extra fields.
Also,
> >> >> >Table_B
> >> >> >> field size may be different for some fields.
> >> >> >>
> >> >> >> Where can I hunt a sample code in order to do this?
> >> >> >>
> >> >> >> Finally I want to call this from VB.
> >> >> >>
> >> >> >> Thanks.
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >
> >> >
> >>
> >
> >
>
devx.com
Copyright Internet.com Inc. All Rights Reserved