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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Inserting a select statement into linked server...
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Auric
Yak Posting Veteran

Canada
70 Posts

Posted - 12/16/2005 :  09:56:59  Show Profile  Reply with Quote
Hey All

I have 3 LARGE select statements in a stored procedure, they do quite a bit of data massaging and bashing to make it fit the format that one of our partners uses.

I want to insert the results from the selects into 3 tables on a linked server? How do i do that?

insert into Openquery(Link,'select * from wwwtest.Tablename') Values ( select stuff here ) doesn't work?

Select * from users where clue > 0

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 12/16/2005 :  10:01:52  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try

insert into Openquery(Link,'select * from wwwtest.Tablename') select_stuff_here

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Auric
Yak Posting Veteran

Canada
70 Posts

Posted - 12/16/2005 :  10:13:37  Show Profile  Reply with Quote
Insert Error: Column name or number of supplied values does not match table definition.

I get that error message, I checked all the column names and they are correct, the only column I am not sending a value to is an autoincrementing ID...

Select * from users where clue > 0
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 12/16/2005 :  11:38:28  Show Profile  Reply with Quote
Instead of asteric (*), try using the field list except the autoincrementing field in both tables.
Don't use the Values keyword

insert into Openquery(Link,'select b,c,d,e from wwwtest.Tablename')
select b,c,d,e from SourceTbl
Go to Top of Page

Auric
Yak Posting Veteran

Canada
70 Posts

Posted - 12/16/2005 :  14:19:50  Show Profile  Reply with Quote
Ended up getting the queries working, in a sense that I am no longer getting an error. but It is still not inserting properly. Stupid AS400

Select * from users where clue > 0
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000