How to insert into one DB from data in another DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to insert into one DB from data in another DB

Author

Topic

jej1216
Starting Member

27 Posts

Posted - 2007-06-01 : 14:29:09

I have TEST DB that has a table (DIST_LN) and I need to insert rows from that table into the same table in my PROD DB.

I tried this:
insert into PROD.dbo.DIST_LN
(select * from TEST.dbo.DIST_LN
where TEST.dbo.BUSINESS_UNIT = '20030'
and TEST.dbo.ASSET_ID = '000000000280'
and TEST.dbo.TRANS_TYPE = 'DPR'
and TEST.dbo.DTTM_STAMP = '2007-04-09 10:47:08.930')

and I get this error:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

TIA,

jej1216

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-01 : 15:43:09

"TEST.dbo.BUSINESS_UNIT " indicates a table, not a column.

e4 d5 xd5 Nf6

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-01 : 15:45:22

Try this:


insert into PROD.dbo.DIST_LN 
select	*
from	TEST.dbo.DIST_LN
where	BUSINESS_UNIT = '20030'
	ASSET_ID = '000000000280'
	TRANS_TYPE = 'DPR'
	DTTM_STAMP = '2007-04-09 10:47:08.930'

...though it is poor programming practice to use SELECT * like this. You should properly enumerate your column list.

e4 d5 xd5 Nf6

jej1216
Starting Member

27 Posts

Posted - 2007-06-01 : 17:15:44

Thanks - that worked. I got lazy with the select * (there are 35 columns in the table). No excuse, I know.

Kristen
Test

22859 Posts

Posted - 2007-06-01 : 17:19:12

"I got lazy with the select *"

Its fine for a one-off job.

However, lots of good reasons for not doing it code that is executed regularly

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-01 : 18:23:43

in SSMS, if you select the "columns" node under a table and drag it into the query editor, it will give you the column list.

Don't know if there's a similar feature in EM though.

www.elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-02 : 02:32:42

quote:
Originally posted by Kristen

"I got lazy with the select *"

Its fine for a one-off job.

However, lots of good reasons for not doing it code that is executed regularly

Yes. Problem will happen if columns are added/removed from that table

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-02 : 02:33:41

quote:
Originally posted by jezemine

in SSMS, if you select the "columns" node under a table and drag it into the query editor, it will give you the column list.

Don't know if there's a similar feature in EM though.

www.elsasoft.org

Use query

Select column_name+',' from information_schema.columns
where table_name='your_table'

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-02 : 04:01:21

well, I can drag an icon into the query window faster than I can type all that

www.elsasoft.org

Kristen
Test

22859 Posts

Posted - 2007-06-05 : 04:56:30

We have an Sproc that delivers that, and is set up on a F-key ... so we get it formatted to our liking!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources