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
Matthew34Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
INSERT INTO fin_prod..glf_ldg_acc_trans
SELECT * FROM FINPROD..glf_ldg_acc_trans
WHERE ldg_name = '1213ACT' 

Open in new window

Assumptions

1.

The schemas for both are dbo.  If anything different, add it between the two dots

2.

The columns and column data types of the two tables are EXACTLY the same.  If not, you'll have to replace * with the column names.
0
dsackerContract ERP Admin/ConsultantCommented:
Position yourself in the target database. I'm assuming your schema is "dbo".

If both databases reside on the same server, this will work:

If table does NOT exist:

SELECT *
INTO glf_ldg_acct_trans
FROM FINPROD.dbo.glf_ldg_acc_trans
WHERE ldg_name = '1213ACT'

If you already created the table:

INSERT INTO glf_ldg_acct_trans
SELECT * FROM FINPROD.dbo.glf_ldg_acct_trans
WHERE ldg_name = '1213ACT'

If the databases reside on separate servers, set up a server link to the source database in your target database server, then do as the above, except change your FROM as follows:

FROM [SourceDatabaseServer].FINPROD.dbo.glf_ldg_acct_trans
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matthew34Author Commented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

dsackerContract ERP Admin/ConsultantCommented:
If you want to have a identity column, you'll have to explicitly list all the other columns:

INSERT INTO gldf_ldg_acct_trans (column2, column3, etc, etc)
SELECT column2, column3, etc, etc

So that you can increment that identity column automatically.

Or

Delete that table and do a SELECT * INTO glf_ldg_acct_trans
0
Matthew34Author Commented:
If I delete the table will it create all the indexes, and column properties?
0
dsackerContract ERP Admin/ConsultantCommented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<slightly redundant with above comments, sorry guys>

Three choices:

1.

SET IDENTITY_INSERT ON, then do your insert, then SET IDENTITY_INSERT OFF

2.

Change the target ID column to just int (no identity), do your insert, then change it to identity.

3.

If the ID values don't really matter, just insert all columns EXCEPT for the ID, and those values will automatically be generated.>If I delete the table will it create all the indexes, and column properties?
If you can pull it off, it would execute quicker to delete target indexes, insert, then add them back.
0
LowfatspreadCommented:
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...
0
Matthew34Author Commented:
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.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.