select into - SQL Server Forums

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)

select into

Author

Topic

joe77s
Starting Member

10 Posts

Posted - 2002-08-30 : 00:05:49

i want to write this in store proc, but it can't....

create procedure zp_insert_data
@newtable nvarchar(250),
@oldtable nvarchar(250)
as
select * into @newtable from @oldtable

and it from 2 database but still in one server..
eg :
@newtable = database name is "GOODSDATA"
@oldtable = database name is "MERCHANDATA"
how if from 2 server ?
does someone know, why ?

DavidD
Yak Posting Veteran

73 Posts

Posted - 2002-08-30 : 02:14:08

Use dynamic sql

ie.

create proc zp_insert_data @newtable navrchar(250), @oldtable nvarchar(250)
as
declare @sql varchar(1000)
set @sql = 'select * into goodsdata..' + @newtable + ' from merchandata..' + @oldtable
exec(@sql)

Regards
David

joe77s
Starting Member

10 Posts

Posted - 2002-09-02 : 02:15:43

okay this work...thanks David

i have 2 kind of tables
1. master table
2.transaction table

for master table i already use your code,

and now for my transaction table....

this i try with query analyzer...and always have an error

Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@fromMonth'.

declare @fromMonth as datetime
declare @duemonth as datetime
declare @strsql varchar(1000)
declare @cmd varchar(1000)
set @frommonth = '08/02/2002'
set @duemonth = '09/02/2002'
set @strsql = 'from MERCHANDATA.dbo.dtcontractr where contractno in
(select contractno from MERCHANDATA.dbo.dtcontracth where month(contractdate) between
month(@fromMonth) and month(@duemonth) and year(contractdate) between
year(@fromMonth) and year(@duemonth))'

set @cmd = 'select * into GOODSDATA.dbo.dtcontractr ' + @strsql
exec(@cmd)

how to solve this problem ?

thanks, for your attention.....
joe

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-02 : 08:56:51

set @strsql = 'from MERCHANDATA.dbo.dtcontractr where contractno in
(select contractno from MERCHANDATA.dbo.dtcontracth where month(contractdate) between
month(@fromMonth) and month(@duemonth) and year(contractdate) between
year(@fromMonth) and year(@duemonth))'

s.b.
... between ' + convert(varchar(10), month(@fromMonth)) + ' and ' convert(varchar(10), month(@duemonth)) ...

The parameters are not available to the exec'd batch.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

joe77s
Starting Member

10 Posts

Posted - 2002-09-04 : 02:40:20

okay....

...it's work....

this solve my problem...

thanks to David and nr...

joe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources