DECLARE SYNTAX - 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

DECLARE SYNTAX

Author

Topic

sportsguy
Starting Member

39 Posts

Posted - 2012-11-02 : 17:47:00

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

1974 Posts

Posted - 2012-11-02 : 17:54:14

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-11-02 : 17:55:12

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-02 : 18:19:52

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-03 : 22:04:11

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/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-17 : 06:10:55

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources