Re: Generic Table Copy from one DB to another
SQL Server don't have SELECT INTO clause. Right? Presuming M$ SQL Server, one could write a stored procedure the perform the 'select into'. One note, if the goal is to be able to pass in any source table and any matching destination table then the stored proc would have to build a string and then use 'exec sp_executesql' to execute the string. Doug Gonzo Rock wrote: [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generic Table Copy from one DB to another
SQL Server don't have SELECT INTO clause. Right? Yes, it does. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Generic Table Copy from one DB to another
I'm trying to write a script which will take as an argument a table name, then copy the data from that table in one table to an identical table in another database (assuming that the target database has a blank table with an identical structure). Has anyone attempted this sort of task before?How would I go about starting this project? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generic Table Copy from one DB to another
don't need to do all that, look up select into tis what you want to use. tony -Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Thursday, April 22, 2004 12:03 PM To: CF-Talk Subject: Generic Table Copy from one DB to another I'm trying to write a script which will take as an argument a table name, then copy the data from that table in one table to an identical table in another database (assuming that the target database has a blank table with an identical structure). Has anyone attempted this sort of task before?How would I go about starting this project? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generic Table Copy from one DB to another
are the source and target db's 1) on the same db server? 2) on different db servers local to the same host 3) on different db servers local to different hosts. 4) are you trying to create an exact duplicate of the source in the target or update the target? what db servers? Depending on the above answers, you may be able to: 1) use built-in db server commands/features 2) write a CF query of the source and iterate over it, doing inserts/updates with the target 3) you may have to do some data exchange using WDDX with a stub program on the target host and do the above HTH Dick On Apr 22, 2004, at 9:02 AM, Richard Crawford wrote: I'm trying to write a script which will take as an argument a table name, then copy the data from that table in one table to an identical table in another database (assuming that the target database has a blank table with an identical structure). Has anyone attempted this sort of task before? How would I go about starting this project? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generic Table Copy from one DB to another
Dick Applebaum wrote: are the source and target db's 1) on the same db server? 2) on different db servers local to the same host 3) on different db servers local to different hosts. 4) are you trying to create an exact duplicate of the source in the target or update the target? what db servers? The databases are on two different servers, but I don't believe that's an issue since both are accessible to Cold Fusion, and I believe I can maintain two separate datasources at once.Both databases are in SQL Server. The two databases are... DBA - DBB tab1A tab1B tab2A tab2B ... tabnA tabnB I want to replace all of the data in tab1B with the data in tab1A, then all the data in tab2B with all the data in tab2A, and so on. Ordinarily I would use the bcp utility to take care of this task, but there are a couple of reasons why I can't this time. Depending on the above answers, you may be able to: 1) use built-in db server commands/features Won't work, unfortunately.See above. 2) write a CF query of the source and iterate over it, doing inserts/updates with the target That was my plan.I can't figure out how to insert into the target table without specifically referencing the field names of the source table, which I'm hoping to avoid. 3) you may have to do some data exchange using WDDX with a stub program on the target host and do the above Haven't got a clue how to do that. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generic Table Copy from one DB to another
Well as long as the request is generic. This is a Very generic... Brute force approach.You get the idea. the syntax might need a little help. cfquery datasource=#sourcedatabasename# name=source select * from #tablename# /cfquery cfloop index=i from=1 to=#source.recordcount# cfquery datasource=#targetdatabasename# name=target insert into #tablename# ( #source.columnlist# ) Values ( cfloop index=j from=1 to=#listlen(source.columnlist)# '## #listgetat(source.columnlist, j)# ##' /cfloop ) /cfquery /cfloop At 09:02 AM 4/22/04, you wrote: I'm trying to write a script which will take as an argument a table name, then copy the data from that table in one table to an identical table in another database (assuming that the target database has a blank table with an identical structure). Has anyone attempted this sort of task before?How would I go about starting this project? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generic Table Copy from one DB to another
why not use select into? -Original Message- From: Gonzo Rock [mailto:[EMAIL PROTECTED] Sent: Thursday, April 22, 2004 12:38 PM To: CF-Talk Subject: Re: Generic Table Copy from one DB to another Well as long as the request is generic. This is a Very generic... Brute force approach.You get the idea. the syntax might need a little help. cfquery datasource=#sourcedatabasename# name=source select * from #tablename# /cfquery cfloop index=i from=1 to=#source.recordcount# cfquery datasource=#targetdatabasename# name=target insert into #tablename# ( #source.columnlist# ) Values ( cfloop index=j from=1 to=#listlen(source.columnlist)# '## #listgetat(source.columnlist, j)# ##' /cfloop ) /cfquery /cfloop At 09:02 AM 4/22/04, you wrote: I'm trying to write a script which will take as an argument a table name, then copy the data from that table in one table to an identical table in another database (assuming that the target database has a blank table with an identical structure). Has anyone attempted this sort of task before?How would I go about starting this project? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generic Table Copy from one DB to another
Tony Weeg wrote: why not use select into? I am still looking for information about select into. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generic Table Copy from one DB to another
ok... here ya go...simple but effective. select * INTO server_B.something.dbo.database_B from server_A.something.dbo.database_A that will take everything from a to b, table structure etc. tw -Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Thursday, April 22, 2004 12:49 PM To: CF-Talk Subject: Re: Generic Table Copy from one DB to another Tony Weeg wrote: why not use select into? I am still looking for information about select into. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generic Table Copy from one DB to another
Tony Weeg wrote: ok... here ya go...simple but effective. select * INTO server_B.something.dbo.database_B from server_A.something.dbo.database_A that will take everything from a to b, table structure etc. tw I must need more coffee this morning, because I can't find any reference to this in my CF manuals.Can you point me to an on-line reference with some more information? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generic Table Copy from one DB to another
It's sql not cf http://www.w3schools.com/sql/sql_select_into.asp At 10:08 AM 4/22/04, you wrote: I must need more coffee this morning, because I can't find any reference to this in my CF manuals.Can you point me to an on-line reference with some more information? -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generic Table Copy from one DB to another
Hey Tony, how ya do this with cf?the query extends across two databases... reading source from one and writing to the second... would be great to know. thanks. At 09:51 AM 4/22/04, you wrote: ok... here ya go...simple but effective. select * INTO server_B.something.dbo.database_B from server_A.something.dbo.database_A that will take everything from a to b, table structure etc. tw -Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Thursday, April 22, 2004 12:49 PM To: CF-Talk Subject: Re: Generic Table Copy from one DB to another Tony Weeg wrote: why not use select into? I am still looking for information about select into. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] -- [http://www.houseoffusion.com/lists.cfm/link=t:4Todays Threads] [http://www.houseoffusion.com/lists.cfm/link=i:4:160994This Message] [http://www.houseoffusion.com/lists.cfm/link=s:4Subscription] [http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=664.584.4Fast Unsubscribe] [http://www.houseoffusion.com/signin/User Settings] -- http://www.houseoffusion.com/banners/view.cfm?bannerid=40 [] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generic Table Copy from one DB to another
Presuming M$ SQL Server, one could write a stored procedure the perform the 'select into'. One note, if the goal is to be able to pass in any source table and any matching destination table then the stored proc would have to build a string and then use 'exec sp_executesql' to execute the string. Doug Gonzo Rock wrote: Hey Tony, how ya do this with cf?the query extends across two databases... reading source from one and writing to the second... would be great to know. thanks. At 09:51 AM 4/22/04, you wrote: ok... here ya go...simple but effective. select * INTO server_B.something.dbo.database_B from server_A.something.dbo.database_A that will take everything from a to b, table structure etc. tw -Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Thursday, April 22, 2004 12:49 PM To: CF-Talk Subject: Re: Generic Table Copy from one DB to another Tony Weeg wrote: why not use select into? I am still looking for information about select into. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] -- [http://www.houseoffusion.com/lists.cfm/link=t:4Todays Threads] [http://www.houseoffusion.com/lists.cfm/link=i:4:160994This Message] [http://www.houseoffusion.com/lists.cfm/link=s:4Subscription] [http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=664.584.4Fast Unsubscribe] [http://www.houseoffusion.com/signin/User Settings] -- http://www.houseoffusion.com/banners/view.cfm?bannerid=40 [] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generic Table Copy from one DB to another
Doug James wrote: Presuming M$ SQL Server, one could write a stored procedure the perform the 'select into'. One note, if the goal is to be able to pass in any source table and any matching destination table then the stored proc would have to build a string and then use 'exec sp_executesql' to execute the string. We won't be using stored procedures for this particular task.The goal, for various reasons, is to stick with CF. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generic Table Copy from one DB to another
Presuming Oracle, this could be done using the data dictionary. -Original Message- From: Richard Crawford [mailto:[EMAIL PROTECTED] Sent: Thursday, April 22, 2004 12:50 PM To: CF-Talk Subject: Re: Generic Table Copy from one DB to another Doug James wrote: Presuming M$ SQL Server, one could write a stored procedure the perform the 'select into'. One note, if the goal is to be able to pass in any source table and any matching destination table then the stored proc would have to build a string and then use 'exec sp_executesql' to execute the string. We won't be using stored procedures for this particular task.The goal, for various reasons, is to stick with CF. -- Richard S. Crawford Programmer III, UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu) (916)327-7793 / [EMAIL PROTECTED] _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generic Table Copy from one DB to another
with cf?
cfquery name=get datasource=dsn1
select * from thisTable
/cfquery
cfloop query=get
cfquery name=set datasource=dsn2
insert into newTable
(column1, column2, column3)
values
('#get.value1#','#get.value2#','#get.value3#')
/cfquery
/cfloop
that's just one way...that I usually use!
-Original Message-
From: Gonzo Rock [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 1:14 PM
To: CF-Talk
Subject: RE: Generic Table Copy from one DB to another
Hey Tony, how ya do this with cf?the query extends across two databases...
reading source from one and writing to the second... would be great to know.
thanks.
At 09:51 AM 4/22/04, you wrote:
ok...
here ya go...simple but effective.
select *
INTO server_B.something.dbo.database_B
from server_A.something.dbo.database_A
that will take everything from a to b, table structure etc.
tw
-Original Message-
From: Richard Crawford [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 12:49 PM
To: CF-Talk
Subject: Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
why not use select into?
I am still looking for information about select into.
--
Richard S. Crawford
Programmer III,
UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu)
(916)327-7793 / [EMAIL PROTECTED]
--
[http://www.houseoffusion.com/lists.cfm/link=t:4Todays Threads]
[http://www.houseoffusion.com/lists.cfm/link=i:4:160994This Message]
[http://www.houseoffusion.com/lists.cfm/link=s:4Subscription]
[http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=664.584.4
Fast
Unsubscribe] [http://www.houseoffusion.com/signin/User Settings]
--
http://www.houseoffusion.com/banners/view.cfm?bannerid=40
[]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Generic Table Copy from one DB to another
or you can certainly update, delete or do whatever, but in the scenario he
is looking for, I believe it's an insert...
but seriously a select into is the easiest.
im not sure who the original poster was...but are you looking for a one time
thing to do, or something to do day to day, etc...?
tony
-Original Message-
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 2:09 PM
To: CF-Talk
Subject: RE: Generic Table Copy from one DB to another
with cf?
cfquery name=get datasource=dsn1
select * from thisTable
/cfquery
cfloop query=get
cfquery name=set datasource=dsn2
insert into newTable
(column1, column2, column3)
values
('#get.value1#','#get.value2#','#get.value3#')
/cfquery
/cfloop
that's just one way...that I usually use!
-Original Message-
From: Gonzo Rock [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 1:14 PM
To: CF-Talk
Subject: RE: Generic Table Copy from one DB to another
Hey Tony, how ya do this with cf?the query extends across two databases...
reading source from one and writing to the second... would be great to know.
thanks.
At 09:51 AM 4/22/04, you wrote:
ok...
here ya go...simple but effective.
select *
INTO server_B.something.dbo.database_B
from server_A.something.dbo.database_A
that will take everything from a to b, table structure etc.
tw
-Original Message-
From: Richard Crawford [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 12:49 PM
To: CF-Talk
Subject: Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
why not use select into?
I am still looking for information about select into.
--
Richard S. Crawford
Programmer III,
UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu)
(916)327-7793 / [EMAIL PROTECTED]
--
[http://www.houseoffusion.com/lists.cfm/link=t:4Todays Threads]
[http://www.houseoffusion.com/lists.cfm/link=i:4:160994This Message]
[http://www.houseoffusion.com/lists.cfm/link=s:4Subscription]
[http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=664.584.4
Fast
Unsubscribe] [http://www.houseoffusion.com/signin/User Settings]
--
http://www.houseoffusion.com/banners/view.cfm?bannerid=40
[]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Generic Table Copy from one DB to another
na he was asking for a generic script that could be specified at
runtime what tables were involved... so i gave him a cf routine similar to
yours but generic... mine has a small problem with the insert... but I'm
sure he can figure it out...
I was just curious as to how you would use a select into statement in a
cfquery across databases with cf...
later,
Gonz
At 11:18 AM 4/22/04, you wrote:
or you can certainly update, delete or do whatever, but in the scenario he
is looking for, I believe it's an insert...
but seriously a select into is the easiest.
im not sure who the original poster was...but are you looking for a one time
thing to do, or something to do day to day, etc...?
tony
-Original Message-
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 2:09 PM
To: CF-Talk
Subject: RE: Generic Table Copy from one DB to another
with cf?
cfquery name=get datasource=dsn1
select * from thisTable
/cfquery
cfloop query=get
cfquery name=set datasource=dsn2
insert into newTable
(column1, column2, column3)
values
('#get.value1#','#get.value2#','#get.value3#')
/cfquery
/cfloop
that's just one way...that I usually use!
-Original Message-
From: Gonzo Rock [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 1:14 PM
To: CF-Talk
Subject: RE: Generic Table Copy from one DB to another
Hey Tony, how ya do this with cf?the query extends across two databases...
reading source from one and writing to the second... would be great to know.
thanks.
At 09:51 AM 4/22/04, you wrote:
ok...
here ya go...simple but effective.
select *
INTO server_B.something.dbo.database_B
from server_A.something.dbo.database_A
that will take everything from a to b, table structure etc.
tw
-Original Message-
From: Richard Crawford [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 12:49 PM
To: CF-Talk
Subject: Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
why not use select into?
I am still looking for information about select into.
--
Richard S. Crawford
Programmer III,
UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu)
(916)327-7793 / [EMAIL PROTECTED]
--
[http://www.houseoffusion.com/lists.cfm/link=t:4Todays Threads]
[http://www.houseoffusion.com/lists.cfm/link=i:4:160994This Message]
[http://www.houseoffusion.com/lists.cfm/link=s:4Subscription]
[http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=664.584.4
Fast
Unsubscribe] [http://www.houseoffusion.com/signin/User Settings]
--
http://www.houseoffusion.com/banners/view.cfm?bannerid=40
[]
--
[http://www.houseoffusion.com/lists.cfm/link=t:4Todays Threads]
[http://www.houseoffusion.com/lists.cfm/link=i:4:161013This Message]
[http://www.houseoffusion.com/lists.cfm/link=s:4Subscription]
[http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=664.584.4Fast
Unsubscribe] [http://www.houseoffusion.com/signin/User Settings]
--
http://www.houseoffusion.com/banners/view.cfm?bannerid=36
[]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Generic Table Copy from one DB to another
cfquery name=get datasource=dsn1
select *
INTO server_B.something.dbo.database_B from
server_A.something.dbo.database_A
/cfquery
just like that.
tony
-Original Message-
From: Gonzo Rock [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 2:36 PM
To: CF-Talk
Subject: RE: Generic Table Copy from one DB to another
na he was asking for a generic script that could be specified at
runtime what tables were involved... so i gave him a cf routine similar to
yours but generic... mine has a small problem with the insert... but I'm
sure he can figure it out...
I was just curious as to how you would use a select into statement in a
cfquery across databases with cf...
later,
Gonz
At 11:18 AM 4/22/04, you wrote:
or you can certainly update, delete or do whatever, but in the scenario
he is looking for, I believe it's an insert...
but seriously a select into is the easiest.
im not sure who the original poster was...but are you looking for a one
time thing to do, or something to do day to day, etc...?
tony
-Original Message-
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 2:09 PM
To: CF-Talk
Subject: RE: Generic Table Copy from one DB to another
with cf?
cfquery name=get datasource=dsn1
select * from thisTable
/cfquery
cfloop query=get
cfquery name=set datasource=dsn2
insert into newTable
(column1, column2, column3)
values
('#get.value1#','#get.value2#','#get.value3#')
/cfquery
/cfloop
that's just one way...that I usually use!
-Original Message-
From: Gonzo Rock [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 1:14 PM
To: CF-Talk
Subject: RE: Generic Table Copy from one DB to another
Hey Tony, how ya do this with cf?the query extends across two
databases...
reading source from one and writing to the second... would be great to
know.
thanks.
At 09:51 AM 4/22/04, you wrote:
ok...
here ya go...simple but effective.
select *
INTO server_B.something.dbo.database_B from
server_A.something.dbo.database_A
that will take everything from a to b, table structure etc.
tw
-Original Message-
From: Richard Crawford [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 12:49 PM
To: CF-Talk
Subject: Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
why not use select into?
I am still looking for information about select into.
--
Richard S. Crawford
Programmer III,
UC Davis Extension Distance Learning Group
(http://unexdlc.ucdavis.edu)
(916)327-7793 / [EMAIL PROTECTED]
--
[http://www.houseoffusion.com/lists.cfm/link=t:4Todays Threads]
[http://www.houseoffusion.com/lists.cfm/link=i:4:160994This
Message]
[http://www.houseoffusion.com/lists.cfm/link=s:4Subscription]
[http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=664.584.
4
Fast
Unsubscribe] [http://www.houseoffusion.com/signin/User Settings]
--
http://www.houseoffusion.com/banners/view.cfm?bannerid=40
[]
--
[http://www.houseoffusion.com/lists.cfm/link=t:4Todays Threads]
[http://www.houseoffusion.com/lists.cfm/link=i:4:161013This Message]
[http://www.houseoffusion.com/lists.cfm/link=s:4Subscription]
[http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=664.584.4
Fast
Unsubscribe] [http://www.houseoffusion.com/signin/User Settings]
--
http://www.houseoffusion.com/banners/view.cfm?bannerid=36
[]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
with cf?
cfquery name=get datasource=dsn1
select * from thisTable
/cfquery
cfloop query=get
cfquery name=set datasource=dsn2
insert into newTable
(column1, column2, column3)
values
('#get.value1#','#get.value2#','#get.value3#')
/cfquery
/cfloop
that's just one way...that I usually use!
Spiffy!
Is there a way to do this without specifically listing all of the table
names?
--
Richard S. Crawford
Programmer III,
UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu)
(916)327-7793 / [EMAIL PROTECTED]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Generic Table Copy from one DB to another
uhh, Im not sure what ya mean.I guess you could have a random word
generator, and hope that it matches a table in your database or something
like that.
:) seriously...tho', not sure what you mean, or where/why it would come into
play...im sure you have a valid need...just wondering.
-Original Message-
From: Richard Crawford [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 2:42 PM
To: CF-Talk
Subject: Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
with cf?
cfquery name=get datasource=dsn1
select * from thisTable
/cfquery
cfloop query=get
cfquery name=set datasource=dsn2
insert into newTable
(column1, column2, column3)
values
('#get.value1#','#get.value2#','#get.value3#')
/cfquery
/cfloop
that's just one way...that I usually use!
Spiffy!
Is there a way to do this without specifically listing all of the table
names?
--
Richard S. Crawford
Programmer III,
UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu)
(916)327-7793 / [EMAIL PROTECTED]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
uhh, Im not sure what ya mean.I guess you could have a random word
generator, and hope that it matches a table in your database or something
like that.
:) seriously...tho', not sure what you mean, or where/why it would come into
play...im sure you have a valid need...just wondering.
-Original Message-
From: Richard Crawford [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 2:42 PM
To: CF-Talk
Subject: Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
with cf?
cfquery name=get datasource=dsn1
select * from thisTable
/cfquery
cfloop query=get
cfquery name=set datasource=dsn2
insert into newTable
(column1, column2, column3)
values
('#get.value1#','#get.value2#','#get.value3#')
/cfquery
/cfloop
that's just one way...that I usually use!
Spiffy!
Is there a way to do this without specifically listing all of the table
names?
Gonzo gave me a pretty good start, actually.
But the problem is this: I want to be able to specify a table at
runtime, and not have to worry about its structure.In the script you
gave, you specifically give the column names: column1, column2, etc.I
need a script where I don't have to write out the column names.
In other words, in my simple book database, I have two tables: a table
for authors and a table for titles, both with different structures.I
want to be able to run the same generic script on both tables without
having to make changes to the script itself.
--
Richard S. Crawford
Programmer III,
UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu)
(916)327-7793 / [EMAIL PROTECTED]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Generic Table Copy from one DB to another
A simple solution (though not really the best) is to do a query on the table
that returns zero rows and then use the columnlist from that recordset
object as your column list:
cfquery name=get
SELECT *
FROM #myDynamicTable#
WHERE 1 = 0
/cfquery
cfquery name=getdata
SELECT #get.columnlist#
FROM #myDynamicTable#
/cfquery
cfloop query=getdata
cfquery
INSERT INTO #myDestinationTable#
(#get.columnlist#)
VALUES
(
cfloop list=#get.columnlist# index=col
'#getdata[col][currentrow]#'/cfloop
)
/cfquery
/cfloop
You'll probably need to add a little logic in that innermost CFLOOP for
dealing with different data types (possibly selecting one row in the 'get'
query to use for calculating the appropriate type), but that should be
reasonably close.
Cheers,
barneyb
-Original Message-
From: Richard Crawford [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 12:15 PM
To: CF-Talk
Subject: Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
uhh, Im not sure what ya mean.I guess you could have a random word
generator, and hope that it matches a table in your
database or something
like that.
:) seriously...tho', not sure what you mean, or where/why
it would come into
play...im sure you have a valid need...just wondering.
-Original Message-
From: Richard Crawford [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 2:42 PM
To: CF-Talk
Subject: Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
with cf?
cfquery name=get datasource=dsn1
select * from thisTable
/cfquery
cfloop query=get
cfquery name=set datasource=dsn2
insert into newTable
(column1, column2, column3)
values
('#get.value1#','#get.value2#','#get.value3#')
/cfquery
/cfloop
that's just one way...that I usually use!
Spiffy!
Is there a way to do this without specifically listing all
of the table
names?
Gonzo gave me a pretty good start, actually.
But the problem is this: I want to be able to specify a table at
runtime, and not have to worry about its structure.In the
script you
gave, you specifically give the column names: column1,
column2, etc.I
need a script where I don't have to write out the column names.
In other words, in my simple book database, I have two
tables: a table
for authors and a table for titles, both with different
structures.I
want to be able to run the same generic script on both tables without
having to make changes to the script itself.
--
Richard S. Crawford
Programmer III,
UC Davis Extension Distance Learning Group
(http://unexdlc.ucdavis.edu)
(916)327-7793 / [EMAIL PROTECTED]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Generic Table Copy from one DB to another
Is there a way to do this without specifically listing all of the table names? It depends on the database server youre running.I know with MySQL you can get a list of the tables by running the query: For MySQL: SHOW TABLE STATUS FROM database To get the list of fields in the table, you will need to execute: SHOW COLUMNS FROM tablename For MS SQL Server: select * from sysobjects WHERE xtype = 'U' AND type = 'U' Alternately you can run the following queries to get a recordset with the table name and column names: (I could only get this to work in MSSQL) select sysobjects.name as TableName, syscolumns.name AS ColumnName from sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.xtype = 'U' AND sysobjects.type = 'U' ORDER BY sysobjects.name, syscolumns.name Hatton [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generic Table Copy from one DB to another
We won't be using stored procedures for this particular task. The goal, for various reasons, is to stick with CF. I would strongly recommend looking into alternative (non-CF) methods for transferring your data. Using CF to do it will probably be the least efficient way you could find, and it'll divert resources from other HTTP requests that your CF server needs to process. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generic Table Copy from one DB to another
but I guess to me, that's the beauty of select into
you don't have to specify a single column name, the table
doesn't have to be created before hand, the select into creates
it, and fills it with the data from all of the columns, matching the
datatypes etc..
just my .02c
-Original Message-
From: Richard Crawford [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 3:15 PM
To: CF-Talk
Subject: Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
uhh, Im not sure what ya mean.I guess you could have a random word
generator, and hope that it matches a table in your database or
something like that.
:) seriously...tho', not sure what you mean, or where/why it would
come into play...im sure you have a valid need...just wondering.
-Original Message-
From: Richard Crawford [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 22, 2004 2:42 PM
To: CF-Talk
Subject: Re: Generic Table Copy from one DB to another
Tony Weeg wrote:
with cf?
cfquery name=get datasource=dsn1
select * from thisTable
/cfquery
cfloop query=get
cfquery name=set datasource=dsn2
insert into newTable
(column1, column2, column3)
values
('#get.value1#','#get.value2#','#get.value3#')
/cfquery
/cfloop
that's just one way...that I usually use!
Spiffy!
Is there a way to do this without specifically listing all of the
table names?
Gonzo gave me a pretty good start, actually.
But the problem is this: I want to be able to specify a table at runtime,
and not have to worry about its structure.In the script you gave, you
specifically give the column names: column1, column2, etc.I need a script
where I don't have to write out the column names.
In other words, in my simple book database, I have two tables: a table for
authors and a table for titles, both with different structures.I want to
be able to run the same generic script on both tables without having to make
changes to the script itself.
--
Richard S. Crawford
Programmer III,
UC Davis Extension Distance Learning Group (http://unexdlc.ucdavis.edu)
(916)327-7793 / [EMAIL PROTECTED]
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
