Bulk Insert space delimited and tab delimited files - Errors Received - Must declare scalar variable

[BxWill]

Have a 5000+ line sql script to load multiple text files that are either space delimited or tab delimited into multiple lookup tables in a 2012 database.


I receive two types of errors when running the sql script below in Sql Server 2012 Developer edition.

Errors Received;

1. Incorrect syntax near '\' (with the red tilde under the back slash to the left of t.) beginning with line 136

2. "Must declare scalar variables" @sQL, '@LOADDIR', @LOADDIR beginning with line 140

I have already declared the above-mentioned variables.


The multiple use of the line "EXEC(@SQL)" may be the source of the errors.


It appears that "EXEC(@SQL" is creating a new connection to the Sql Server which is not my current session. Therefore, it appears that the new connection does not see the variables - @sQL, '@LOADDIR', @LOADDIR.

So, the convenience of using the variables - @sQL, '@LOADDIR', @LOADDIR, appear to be overshadowed by the need to now modify each "EXEC(@SQL" statement.


Comtemplating on replacing the variables @sQL, '@LOADDIR', and @LOADDIR. Maybe just hard code the actual path and do away with the "EXEC(@SQL)" statements.


Any insight as to the specific cause of the errors and a resolution? About to replace the use of "EXEC(@SQL" statements with hard coding the path of the text file in each section. Prior to going this route, maybe there is a relatively "simple" solution...

Is there a more preferred method to initially load multiple space delimited and tab delimited text files into multiple tables in a Sql Server database?



----------------------------------------------------------------------------------------------------------------

1  		USE Equipment
2  
3  		--DECLARE @LOADDIR VARCHAR(255) = 'C:\Test\Equipment\Data';
4  		DECLARE @LOADDIR VARCHAR(255) = 'C:\Project\Data';
5  		DECLARE @SQL NVARCHAR(MAX);
6  
7  
8  		IF OBJECT_ID('Equipment..market') IS NOT NULL
9  		BEGIN
10 			DROP TABLE market
11 		END
12 		GO
13 
14 
15 		CREATE TABLE market (
16 			CampaignId int NOT NULL PRIMARY KEY,
17 			CampaignName varchar(50) NOT NULL,
18 			Channel varchar(50) NOT NULL,
19 			Discount int NOT NULL,
20 			FreeShppingFlag char(1) NOT NULL
21 		) ;
22 
23 		SET @SQL = '
24 		BULK INSERT Equipment..market
25 			FROM ''@LOADDIR\market.txt''
26 			WITH (FIRSTROW = 2, FIELDTERMINATOR = ''	'')
27 		';
28 
29 		SET @SQL = REPLACE(@SQL, '@LOADDIR', @LOADDIR)
30 
31 		EXEC(@SQL);
32 
33 
34 		IF OBJECT_ID('Equipment..Customers') IS NOT NULL
35 		BEGIN
36 			DROP TABLE Customers
37 		END
38 		GO
39 
40 
41 
42 		CREATE TABLE Customers (
43 			CustomerId int NOT NULL PRIMARY KEY,
44 			HouseholdId int NOT NULL,
45 			Gender varchar(50) NOT NULL,
46 			FirstName varchar(50) NOT NULL
47 		) ;
48 
49 		SET @SQL = '
50 		BULK INSERT Equipment..Customers
51 			FROM ''@LOADDIR\Customers.txt''
52 			WITH (FIRSTROW = 2, FIELDTERMINATOR = ''	'')
53 		';
54 
55 		SET @SQL = REPLACE(@SQL, '@LOADDIR', @LOADDIR)
56 
57 		EXEC(@SQL);
58 
59 
60 		IF OBJECT_ID('Equipment..order') IS NOT NULL
61 		BEGIN
62 			DROP TABLE order
63 		END
64 		GO
65 
66 
67 
68 
69 
70 		CREATE TABLE order (
71 			OrderLineId int NOT NULL PRIMARY KEY,
72 			OrderId int NOT NULL,
73 			ProductId int NULL,
74 			ShipDate date NOT NULL,
75 			BillDate date NOT NULL,
76 			UnitPrice varchar(255) NOT NULL,
77 			NumUnits int NOT NULL,
78 			TotalPrice money NOT NULL
79 		) ;
80 
81 		SET @SQL = '
82 		BULK INSERT Equipment..order
83 			FROM ''@LOADDIR\order.txt''
84 			WITH (FIRSTROW = 2, FIELDTERMINATOR = ''	'')
85 		';
86 
87 		SET @SQL = REPLACE(@SQL, '@LOADDIR', @LOADDIR)
88 
89 		EXEC(@SQL);
90 		.
91 		.
92 		.
93 		IF OBJECT_ID('Equipment..EquipDataLU_Geography') IS NOT NULL
94 		BEGIN
95 			DROP TABLE EquipDataLU_Geography
96 		END
97 		GO
98 
99 
100		CREATE TABLE [EquipDataLU_Geography](		
101				[Code] [varchar] (15) NULL,
102				[CodeDescription] [varchar] (100) NULL,
103				[AdditionalComments] [varchar] (100)
104			)	
105
106
107		SET @SQL = '
108		BULK INSERT Equipment..EquipDataLU_Geography
109			FROM ''@LOADDIR\Geography.txt''
110			--FROM 'C:\Project\Data\Geography.txt'
111			WITH (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n')
112		';
113
114
115		SET @SQL = REPLACE(@SQL, '@LOADDIR', @LOADDIR)
116
117		EXEC(@SQL);
118
119
120		IF OBJECT_ID('Equipment..EquipDataLU_Jurisdiction') IS NOT NULL
121		BEGIN
122			DROP TABLE EquipDataLU_Jurisdiction
123		END
124		GO
125			
126			
127		CREATE TABLE [EquipDataLU_Jurisdiction](		
128				[Code] [varchar] (15) NULL,
129				[CodeDescription] [varchar] (100) NULL,
130				[AdditionalComments] [varchar] (100)
131			)	
132
133		SET @SQL = '
134		BULK INSERT Equipment..EquipDataLU_Jurisdiction
135			FROM ''@LOADDIR\Jurisdiction.txt''
136			WITH (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n')
137		';
138
139
140		SET @SQL = REPLACE(@SQL, '@LOADDIR', @LOADDIR)
141
142		EXEC(@SQL);

 

[gmmastros]

Change the exec statements to print statements and then run each printed statement in a separate window to see where the errors are.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom