Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Question Unanswered: data transfer proc

    I would like to create a proc that moves data from one database to another. I want to be able to have the target and source databases as parameters in the proc. However I am getting a sytax error.

    create proc NEWPROC
    @Target nvarchar(25),
    @Source nvarchar(25)
    as
    select * from @Source..TableName

    It does not seem to like the @Target paramter being used this way. Does any one have any ideas about how else I could accomplish this. Thanks for your help.

  2. #2
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    Have you taken a look at DTS or maybe replication ? DTS would be a place to start.

  3. #3
    Join Date
    Oct 2003
    Posts
    6
    I am trying to create column to column mapping between the databases. Only certain data will be transfer bepending on conditions. Also the mapping will change depending on conditions. This proc would be dynamic enough that it would handle all possible conditions and then make the correct column mapping and data transfers. I am not sure if you can do this with DTS.

    I might be able to do this with replication, but i thought the proc would be the best method since I am not trying to move data across servers, only across databases.

    I have created procs similiar to this using Unix scripts, between two sybase databases, however it seems SQL server does not like the syntax.

    Originally posted by mtracey
    Have you taken a look at DTS or maybe replication ? DTS would be a place to start.

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    How about
    Code:
     select @query = 'select '+ @fieldnames + ' into ' + @tablename + ' from ' + @server + '..' + @userid + '.' + @tablename 
    exec (@query)
    where the varaibles are defined in advance
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Oct 2003
    Posts
    6
    I had considered that method however I hoped there was an easier way. Thanks for your post

    Originally posted by Enigma
    How about
    Code:
     select @query = 'select '+ @fieldnames + ' into ' + @tablename + ' from ' + @server + '..' + @userid + '.' + @tablename 
    exec (@query)
    where the varaibles are defined in advance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •