sql script - 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

General SQL Server Forums

New to SQL Server Programming

sql script

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-06 : 04:16:47

hi all
i need to write a script that fill in a table from another table that is joined to another. this is what i have so far but is wrong and was wondering could anyone point me in the right direction

ALTER	PROCEDURE
[dbo].[uspBankNoteInsertTest]
(	@bankNoteID		Uniqueidentifier   ,
	@bankAccountID	Uniqueidentifier   ,
	@userID			Uniqueidentifier   ,
	@bankNote		nvarchar(MAX)      ,
	@bankNoteDate	Datetime           )

AS

INSERT INTO dbo.BankNote
(	BankAccountID	,
	UserID			,
	BankNote		,
	BankNoteDate	)
VALUES	
(	@bankAccountID	,
	'ronan'			,
	'test'			,
	GETDATE()		)

SELECT	ba.BankAccountID ,
		ba.BankNumber	 ,
		ba.CurrencyID	  
FROM dbo.BankAccount ba
INNER
JOIN	[HARVEST-SRV-1\MAES].Maes_harvest.dbo.Cash_Balances mcb
ON		mcb.Account_No	=	ba.BankNumber
AND		mcb.Currency	=	ba.CurrencyID

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-06 : 04:25:04

some thing like this. Try in this direction..

Alter proc procname
(
@param1 datatype
)
as
set nocount on
begin

insert into targettable
(
param1 , col2,col3,col4
)
select @param1, s.col2,s.col3,s.col4
from source s
inner join source1 s1 on < join conditions>

end
set nocount off

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-06 : 04:27:20

im not following that would you be able to explain it a bit more what the @parm thing about

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-06 : 04:35:20

@param1 is a parameter which you are passing to insert into target table . It is optional. If you want insert some value from sp instead of getting from other tables then u can use parameters like the above else just get columns from source table and insert in the same order in the target table....

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 04:36:36

what is the use of @bankNoteID,@userID,@bankNote,@bankNoteDate as input parameters

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-06 : 04:37:57

ok so from my above code what would i put in at the @param1 or does it matter

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-06 : 05:08:26

dont no if i did it right but tried it your way and it didnt work here is the code i tried

( @bankAccountID	Uniqueidentifier    )

as
set nocount on
begin

INSERT INTO dbo.BankNote
(	BankAccountID	,
	UserID			,
	BankNote		,
	BankNoteDate	)
SELECT @bankAccountID, 
		ba.BankAccountID ,
		ba.BankNumber	 ,
		ba.CurrencyID	  
FROM dbo.BankAccount ba
INNER
JOIN	[HARVEST-SRV-1\MAES].Maes_harvest.dbo.Cash_Balances mcb
ON		mcb.Account_No	=	ba.BankNumber
AND		mcb.Currency	=	ba.CurrencyID		

end
set nocount off

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-06 : 05:28:20

You haven't explained the problem yet. What is wrong with the above query ?

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-06 : 05:32:46

sorry forgot to say. its not fillin in the table its still blank

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-06 : 05:49:42

Try finding out why your select is not retrieving any data. Run the select in steps and find out which of the joins do not give you a result set.
Also check if the 2 tables are populated or not.

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-06 : 05:54:08

the select pulls the info for me and the 2 table are populated. thats what im finding strange

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-06 : 05:57:23

You get any error when you run your sp ?

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-06 : 06:00:10

no get Command(s) completed successfully.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-06 : 06:00:23

First Make sure Which Column in target table is Going to be populated with which column in source table and also datatypes should be homogeneous...

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-06 : 06:06:13

ok well bankaccountid is going to be populated from the backaccount table thats inner joined to the other one. banknotes will be populated from banknotes in the inner join table but i didnt do that yet cause i want to get the insert work. so im doint that manulaay for now the date will be auto dated from the GETDATE() part and userid i will have to put in myself manually

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-06 : 06:10:05

ALTER PROCEDURE
[dbo].[uspBankNoteInsertTest]
( @bankNoteID Uniqueidentifier ,
@userID Uniqueidentifier )
AS
INSERT INTO dbo.BankNote
( BankAccountID ,
UserID ,
BankNote ,
BankNoteDate )
SELECT ba.BankAccountID ,@userid,
@bankNoteID ,GETDATE()
FROM dbo.BankAccount ba
INNER
JOIN [HARVEST-SRV-1\MAES].Maes_harvest.dbo.Cash_Balances mcb
ON mcb.Account_No = ba.BankNumber
AND mcb.Currency = ba.CurrencyID
try like this

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-06 : 06:13:06

ok i will try that thanks everyone for all the help. and i will lett ye no what happens

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-06 : 06:13:49

Hi,

Iam posting nearby solution to your problem with the Information provided to us

ALTER PROCEDURE
[dbo].[uspBankNoteInsertTest]
(
@userID Uniqueidentifier
)

AS

INSERT INTO dbo.BankNote
( BankAccountID ,
UserID ,
BankNote ,
BankNoteDate )
SELECT ba.BankAccountID ,
@userID ,
-- i didn't understood to put which column from your previous description,
Getdate()
FROM dbo.BankAccount ba
INNER
JOIN [HARVEST-SRV-1\MAES].Maes_harvest.dbo.Cash_Balances mcb
ON mcb.Account_No = ba.BankNumber
AND mcb.Currency = ba.CurrencyID

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-06 : 06:19:09

no same thing ran fine but table still empty

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-01-06 : 06:23:34

so the select statement on it's own returns data?
if so, does the insert statement insert data outside of the stored proc?
again... if so, how are you executing the proc?

Em

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2009-01-06 : 06:29:29

no the insert does not seem to insert the data outside tof the proc

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources