create procedure - 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

create procedure

Author

Topic

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-05 : 03:43:25

declare @ab TABLE
(
Test nchar(10),Test1 nchar(9),Test2 nchar(4),Test3 nchar(6),Test4 nchar(2),
Test5 nchar(22),Test6 nchar(2),Test7 nchar(33),Test8 nchar(4),Test9 nchar(10),
Test10 nchar(4),Test11 nchar(12),Test12 nchar(2),Test13 nchar(1)
)
bulk insert @ab
from 'C:\Documents and Settings\?????????????\??????? ????\abh\abs-s070100.TXT'
with
(formatfile='c:\temp\ABSSC.fmt')
INSERT INTO test_yc(SertiNom,TelTarix,Mebleg,ASA)
SELECT CONVERT(decimal(18,0),Test),convert(smalldatetime,Test1),CONVERT(DECIMAL(7,2),Test9)/100,Test7
from @ab where Test13='a'

You cannot BULK INSERT into a table variable

Is what is causing the error

Line 7: Incorrect syntax near '@ab'

how make create procedure this query

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 03:56:56

The error message is obvious. you cant use @ tables in BULK INSERT. SO create temporary table (# table) and use it instead.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-05 : 05:02:35

thank you visakh

but i want to

1) delete from ats342.dbo.ABSS
2) bulk insert to ats342.dbo.ABSS
3) INSERT INTO test_yc select *from ats342.dbo.ABSS

I created procedure

but this procedure can not delete table ats342.dbo.ABSS

for example procedure
---------

create procedure absstest
@uuu varchar(1000)

AS
DECLARE @SQLD varchar(3000)
DECLARE @SQL varchar(3000)
SET @SQLD='DELETE FROM ats342.dbo.ABSS'
SET @SQL = '
bulk insert ats342.dbo.ABSS
from ''' + @uuu + '''
with
(formatfile=''c:\temp\ABSSC.fmt'')'
EXEC(@SQL)

INSERT INTO test_yc(SertiNom,TelTarix,Mebleg,ASA)
SELECT CONVERT(decimal(18,0),Test),convert(smalldatetime,test1),CONVERT(DECIMAL(7,2),Test9)/100,Test7
from ats342.dbo.ABSS where Test13='a'

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 05:09:29

you're not executing the delete. You just created a variable with DELETE statement but you never executed it!
Also there was no need for a variable in this case

it should be


create procedure absstest
@uuu varchar(1000)

AS

DECLARE @SQL varchar(3000)

DELETE FROM ats342.dbo.ABSS
SET @SQL = '
bulk insert ats342.dbo.ABSS
from ''' + @uuu + '''
with
(formatfile=''c:\temp\ABSSC.fmt'')'
EXEC(@SQL)

INSERT INTO test_yc(SertiNom,TelTarix,Mebleg,ASA)
SELECT CONVERT(decimal(18,0),Test),convert(smalldatetime,test1),CONVERT(DECIMAL(7,2),Test9)/100,Test7
from ats342.dbo.ABSS where Test13='a'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2013-07-05 : 05:16:27

THANK YOU VERY MUCH

kmkmmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 05:22:18

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources