asked on

T-SQL to copy a table across databases

Hello,

I would like to copy a table from one database to another. I am not sure how I can do the following:-

Example

select * from glf_ldg_acc_trans where ldg_name = '1213ACT' in database FINPROD and load it into a blank table glf_ldg_acc_trans in database fin_prod.

Thanks

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

dsacker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Matthew34

ASKER

Hi

I tried the following and got the error below

INSERT INTO glf_ldg_acct_trans
SELECT * FROM FIN_PROD.dbo.glf_ldg_acct_trans
WHERE ldg_name in ('1213ACT', '1112ACT', '1314ACT')

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'glf_ldg_acc_trans' can only be specified when a column list is used and IDENTITY_INSERT is ON.

SOLUTION

dsacker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Matthew34

ASKER

If I delete the table will it create all the indexes, and column properties?

dsacker

It will create only the columns. You'll have to add the indexes and defaults. However, check the table DDL afterwards. It may pick up some of them.

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

we need more background information to proerly answer your query... the following information would assist.

is this a one-off requirement , or continuing....?
how many rows are you expecting to transfer...?
(how much data ... GB?)

what do you want to do with the identity column values... (maintain the ones from the source table/database or generate new values?)

is the target table completely empty when you attempt this?

are the databases on the same server?

If its just a few rows
the Insert into targetdatabase.owner.tablename
(column list)
Select "column list" from sourcetable
where ....

is probably acceptable....

however for large numbers of rows then an IMPORT/EXPORT process using BCP is
probably safer and cheaper in the long run....

especially if you output the rows to a file in the optimum order for the load to the target table....

an ftp of the extract file to the target server ,,, may also help if its very large...

how many indexes will the target table have?

adding any additional desired indexes after the load may also be a more performant solution...

Matthew34

ASKER

Thanks

is this a one-off requirement , or continuing....? Yes once off
how many rows are you expecting to transfer...? 1.5 million
(how much data ... GB?) just over 1 gb

what do you want to do with the identity column values... (maintain the ones from the source table/database or generate new values?) s ame values is fine

is the target table completely empty when you attempt this? yes completely empty

are the databases on the same server? yes on same server

I will give the import/export a go.

Jim Horn

Thanks for the split. Good luck with your project. -Jim