SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 DECLARE SYNTAX
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sportsguy
Starting Member

USA
38 Posts

Posted - 11/02/2012 :  17:47:00  Show Profile  Reply with Quote
Read in Book, looked on line, not sure of the proper syntax.

Issue: appending data in decimal(15,0) into INT field

I am using the DECLARE a local INT variable, and assigning the value into the variable for the append query to work, if this is the best way,

Is the declare syntax like this?

DECLARE @i int
set @i = hdr.CUST_ACCOUNT_ID

INSERT INTO [SG_CRM_SA_HEADERS]

SELECT   hdr.ID AS CONTRACTID,
         hdr.CONTRACT_NUMBER AS intCONTRACT,
         @i 
         
FROM   SG_CRM_HDR hdr


or is the syntax like this?

DECLARE @i int
set @i = Select SG_CRM_HDR.CUST_ACCOUNT_ID from SG_CRM_HDR;

INSERT INTO [SG_CRM_SA_HEADERS]

SELECT   hdr.ID AS CONTRACTID,
         hdr.CONTRACT_NUMBER AS intCONTRACT,
         @i 
         
FROM   SG_CRM_HDR hdr


or is there another way? format is not currently working. . .

thanks

sportsguy




MS Access 20 years, SQL hack

chadmat
The Chadinator

USA
1974 Posts

Posted - 11/02/2012 :  17:54:14  Show Profile  Visit chadmat's Homepage  Reply with Quote
quote:
Originally posted by sportsguy

Read in Book, looked on line, not sure of the proper syntax.

Issue: appending data in decimal(15,0) into INT field

I am using the DECLARE a local INT variable, and assigning the value into the variable for the append query to work, if this is the best way,

Is the declare syntax like this?

DECLARE @i int
set @i = hdr.CUST_ACCOUNT_ID

INSERT INTO [SG_CRM_SA_HEADERS]

SELECT   hdr.ID AS CONTRACTID,
         hdr.CONTRACT_NUMBER AS intCONTRACT,
         @i 
         
FROM   SG_CRM_HDR hdr


or is the syntax like this?

DECLARE @i int
set @i = Select SG_CRM_HDR.CUST_ACCOUNT_ID from SG_CRM_HDR;
INSERT INTO [SG_CRM_SA_HEADERS]

SELECT   hdr.ID AS CONTRACTID,
         hdr.CONTRACT_NUMBER AS intCONTRACT,
         @i 
         
FROM   SG_CRM_HDR hdr


or is there another way? format is not currently working. . .

thanks

sportsguy




MS Access 20 years, SQL hack



This.

-Chad
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 11/02/2012 :  17:55:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
select @i = CUST_ACCOUNT_ID from SG_CRM_HDR where...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/02/2012 :  18:19:52  Show Profile  Reply with Quote
My preference is SET over SELECT (as Chad mentioned) because SET will throw an error if your query returns more than one row. Whereas SELECT will happily assign a value from a random row to your variable.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/03/2012 :  22:04:11  Show Profile  Reply with Quote
If it returns more than a row, then there's very little chance that it would be an expected scenario as it will result in randomly saving one of the values in table which might not be the intended result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/17/2012 :  06:10:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
My guess is that this is what you need because you are trying to append the data converted to INT datatype.

INSERT INTO [SG_CRM_SA_HEADERS]

SELECT   hdr.ID AS CONTRACTID,
         hdr.CONTRACT_NUMBER AS intCONTRACT,
         CAST(CUST_ACCOUNT_ID AS INT)
         
FROM   SG_CRM_HDR hdr


Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 11/17/2012 06:13:46
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000