moving data from one db to another

[Anti-Rich]

hi all,

i was just wondering how it is possible to move data from one table in a database to another table in another database. i am migrating my data from the original db as i have integrated the original db (as well as the application that uses it) into a larger db (and application) i am currently working on. this table only has about 6k rows.

this is what i have for the query so far:

[SIZE=2][COLOR=#0000ff]use[/COLOR][/SIZE][SIZE=2] twdbs[/SIZE]
[SIZE=2][COLOR=#0000ff]create [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]table[/COLOR][/SIZE][SIZE=2] tblWTBackups[/SIZE]
[SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
[SIZE=2]wDate [/SIZE][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]50[/SIZE][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[SIZE=2]VoucherNo [/SIZE][SIZE=2][COLOR=#0000ff]nvarchar[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]50[/SIZE][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[SIZE=2]itemCode [/SIZE][SIZE=2][COLOR=#0000ff]nvarchar[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]50[/SIZE][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[SIZE=2]Company [/SIZE][SIZE=2][COLOR=#0000ff]nvarchar[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]50[/SIZE][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[SIZE=2]PickedUp [/SIZE][SIZE=2][COLOR=#0000ff]nvarchar[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]50[/SIZE][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[SIZE=2]TimeIn [/SIZE][SIZE=2][COLOR=#0000ff]nchar[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]10[/SIZE][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[SIZE=2]TimeOut [/SIZE][SIZE=2][COLOR=#0000ff]nchar[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]10[/SIZE][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE]
[SIZE=2]Qty [/SIZE][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[SIZE=2]QtyPacks [/SIZE][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]insert [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]into[/COLOR][/SIZE][SIZE=2] [tblWTBackups][/SIZE]
[SIZE=2][COLOR=#0000ff]select [/COLOR][/SIZE][SIZE=2][COLOR=#808080]* [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][SIZE=2] tblWT[/SIZE]
 
[SIZE=2][COLOR=#008000]--now, i shall hopefully grab all data from tblWTBackup and put it into the tblWT table on [/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]--tvms[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]use[/COLOR][/SIZE][SIZE=2] tvms[/SIZE]
[SIZE=2][COLOR=#0000ff]insert [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]into[/COLOR][/SIZE][SIZE=2] tblWT[/SIZE]
[SIZE=2][COLOR=#0000ff]select [/COLOR][/SIZE][SIZE=2][COLOR=#808080]* [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][SIZE=2] c04438[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2][TWDBS][/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2][tblWTBackups][/SIZE]

as you can see, the second part is where i get stumped. i thought using dbo.twdbs.tblWTBackups would work... obviously i am missing something. if i can get some input from someone that would be great

cheers
adam

 

[cjard]

are both these databases running within the same instance of sql server?

 

[Anti-Rich]

hi cjard,

yes they are... does this make it easier?

 

[cjard]

Hopefully!

Gimme one second to double check the process

 

[cjard]

connect to the destination user. user must have privs to access other db also

SELECT * INTO [destServer].[destDatabase].dbo.destinationTableName FROM [sourceServer].[sourceDatabase].dbo.sourceTableName

So i I have 2 databases, srcdb and dstdb, on the same server. getting data from srctbl and putting into dsttbl

SELECT * INTO dstdb.dbo.dsttbl FROM srcdb.dbo.srctbl

server is omitted because it is the same..

Note your thread title said "move" which means I would then:

DELETE FROM srcdb.dbo.srctbl

 

[Anti-Rich]

hi cjard

ah excellent cheers dude, much appreciated. i will try it out first thing on monday.

haha, and today i discovered the wonders of stored procedures! i manipulated like 20000 rows in a second or two!! amazing!!

have a good weekend

adam