BULK INSERT from variable Date Filename - ERROR
1
I am trying to do an insert of a text file that contain datetime in filename.
declare @V_SQL varchar(100)
set @V_SQL = (select REPLACE(REPLACE(CONVERT(VARCHAR,getdate()-1,106), ' ',''), ',',''))
BULK INSERT [dbo].[test] FROM '"E:test_"+ @V_SQL +".txt"'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '0x0a'
)
GO
When I run the above I get following message - BULK INSERT [dbo].[test] FROM '"E:test_"+ @V_SQL +".txt"'
sql
sql-server tsql sql-server-2008 sql-server-2012 edited Nov 13 '18 at 14:07
ked2liz
601210
asked Nov 13 '18 at 13:46
Hung NguyenHung Nguyen
115
add a comment |
1
I am trying to do an insert of a text file that contain datetime in filename.
declare @V_SQL varchar(100)
set @V_SQL = (select REPLACE(REPLACE(CONVERT(VARCHAR,getdate()-1,106), ' ',''), ',',''))
BULK INSERT [dbo].[test] FROM '"E:test_"+ @V_SQL +".txt"'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '0x0a'
)
GO
When I run the above I get following message - BULK INSERT [dbo].[test] FROM '"E:test_"+ @V_SQL +".txt"'
sql
sql-server tsql sql-server-2008 sql-server-2012 edited Nov 13 '18 at 14:07
ked2liz
601210
asked Nov 13 '18 at 13:46
Hung NguyenHung Nguyen
115
1
FROMonly accepts a literal, not an expression (and your syntax isn't correct for that anyway). If you want a variable file name, you'll have to build the wholeBULK INSERTstatement dynamically (REPLACE('BULK INSERT [dbo].[test] FROM $f WITH...', '$f', QUOTENAME('E:test_' + @V_SQL + '.txt', ''''))) andEXECit.
– Jeroen Mostert
Nov 13 '18 at 13:52
I've tried like:. but with delimiter is |. It seems not to work
– Hung Nguyen
Nov 14 '18 at 1:36
add a comment |
1
1
1
I am trying to do an insert of a text file that contain datetime in filename.
declare @V_SQL varchar(100)
set @V_SQL = (select REPLACE(REPLACE(CONVERT(VARCHAR,getdate()-1,106), ' ',''), ',',''))
BULK INSERT [dbo].[test] FROM '"E:test_"+ @V_SQL +".txt"'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '0x0a'
)
GO
When I run the above I get following message - BULK INSERT [dbo].[test] FROM '"E:test_"+ @V_SQL +".txt"'
sql
sql-server tsql sql-server-2008 sql-server-2012 edited Nov 13 '18 at 14:07
ked2liz
601210
asked Nov 13 '18 at 13:46
Hung NguyenHung Nguyen
115
I am trying to do an insert of a text file that contain datetime in filename.
declare @V_SQL varchar(100)
set @V_SQL = (select REPLACE(REPLACE(CONVERT(VARCHAR,getdate()-1,106), ' ',''), ',',''))
BULK INSERT [dbo].[test] FROM '"E:test_"+ @V_SQL +".txt"'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '0x0a'
)
GO
When I run the above I get following message - BULK INSERT [dbo].[test] FROM '"E:test_"+ @V_SQL +".txt"'
sql
sql-server tsql sql-server-2008 sql-server-2012 sql
sql-server tsql sql-server-2008 sql-server-2012 edited Nov 13 '18 at 14:07
ked2liz
601210
asked Nov 13 '18 at 13:46
Hung NguyenHung Nguyen
115
edited Nov 13 '18 at 14:07
ked2liz
601210
asked Nov 13 '18 at 13:46
Hung NguyenHung Nguyen
115
edited Nov 13 '18 at 14:07
ked2liz
601210
edited Nov 13 '18 at 14:07
ked2liz
601210
edited Nov 13 '18 at 14:07
ked2liz
601210
601210
asked Nov 13 '18 at 13:46
Hung NguyenHung Nguyen
115
asked Nov 13 '18 at 13:46
Hung NguyenHung Nguyen
115
asked Nov 13 '18 at 13:46
Hung NguyenHung Nguyen
115
115
1
FROMonly accepts a literal, not an expression (and your syntax isn't correct for that anyway). If you want a variable file name, you'll have to build the wholeBULK INSERTstatement dynamically (REPLACE('BULK INSERT [dbo].[test] FROM $f WITH...', '$f', QUOTENAME('E:test_' + @V_SQL + '.txt', ''''))) andEXECit.
– Jeroen Mostert
Nov 13 '18 at 13:52
I've tried like:. but with delimiter is |. It seems not to work
– Hung Nguyen
Nov 14 '18 at 1:36
add a comment |
1
FROMonly accepts a literal, not an expression (and your syntax isn't correct for that anyway). If you want a variable file name, you'll have to build the wholeBULK INSERTstatement dynamically (REPLACE('BULK INSERT [dbo].[test] FROM $f WITH...', '$f', QUOTENAME('E:test_' + @V_SQL + '.txt', ''''))) andEXECit.
– Jeroen Mostert
Nov 13 '18 at 13:52
I've tried like:. but with delimiter is |. It seems not to work
– Hung Nguyen
Nov 14 '18 at 1:36
1
1
FROM only accepts a literal, not an expression (and your syntax isn't correct for that anyway). If you want a variable file name, you'll have to build the whole BULK INSERT statement dynamically (REPLACE('BULK INSERT [dbo].[test] FROM $f WITH...', '$f', QUOTENAME('E:test_' + @V_SQL + '.txt', ''''))) and EXEC it.– Jeroen Mostert
Nov 13 '18 at 13:52
FROM only accepts a literal, not an expression (and your syntax isn't correct for that anyway). If you want a variable file name, you'll have to build the whole BULK INSERT statement dynamically (REPLACE('BULK INSERT [dbo].[test] FROM $f WITH...', '$f', QUOTENAME('E:test_' + @V_SQL + '.txt', ''''))) and EXEC it.– Jeroen Mostert
Nov 13 '18 at 13:52
I've tried like:. but with delimiter is |. It seems not to work
– Hung Nguyen
Nov 14 '18 at 1:36
I've tried like:. but with delimiter is |. It seems not to work
– Hung Nguyen
Nov 14 '18 at 1:36
add a comment |
1 Answer
1
active
oldest
votes
3
You can't put a variable or an expression there. You'll need to use dynamic SQL.
DECLARE @sql nvarchar(max) = N'BULK INSERT dbo.test FROM '''
+ 'c:test_'
+ REPLACE(CONVERT(char(11), DATEADD(DAY,-1,GETDATE()), 13),' ','')
+ ''' WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''0x0a''
);';
PRINT @sql;
--EXEC sys.sp_executesql @sql;
I strongly recommend:
not using shorthand for date operations (e.g.GETDATE()-1)
always declaring lengths for variable data types likevarchar.
answered Nov 13 '18 at 13:57
Aaron BertrandAaron Bertrand
208k27363406
Yes. It's work. Thanks. If you have any basic and advance tutorials for Dynamic SQL. Pls send me.
– Hung Nguyen
Nov 14 '18 at 1:34
@HungNguyen I have a good example of how to use dynamic SQL effectively here, and a debugging tip here, but you should also be concerned about SQL injection (see this and this).
– Aaron Bertrand
Nov 14 '18 at 1:38
I really appreciate this. Thanks
– Hung Nguyen
Nov 14 '18 at 2:41
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53282433%2fbulk-insert-from-variable-date-filename-error%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
You can't put a variable or an expression there. You'll need to use dynamic SQL.
DECLARE @sql nvarchar(max) = N'BULK INSERT dbo.test FROM '''
+ 'c:test_'
+ REPLACE(CONVERT(char(11), DATEADD(DAY,-1,GETDATE()), 13),' ','')
+ ''' WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''0x0a''
);';
PRINT @sql;
--EXEC sys.sp_executesql @sql;
I strongly recommend:
not using shorthand for date operations (e.g.GETDATE()-1)
always declaring lengths for variable data types likevarchar.
answered Nov 13 '18 at 13:57
Aaron BertrandAaron Bertrand
208k27363406
Yes. It's work. Thanks. If you have any basic and advance tutorials for Dynamic SQL. Pls send me.
– Hung Nguyen
Nov 14 '18 at 1:34
@HungNguyen I have a good example of how to use dynamic SQL effectively here, and a debugging tip here, but you should also be concerned about SQL injection (see this and this).
– Aaron Bertrand
Nov 14 '18 at 1:38
I really appreciate this. Thanks
– Hung Nguyen
Nov 14 '18 at 2:41
add a comment |
3
You can't put a variable or an expression there. You'll need to use dynamic SQL.
DECLARE @sql nvarchar(max) = N'BULK INSERT dbo.test FROM '''
+ 'c:test_'
+ REPLACE(CONVERT(char(11), DATEADD(DAY,-1,GETDATE()), 13),' ','')
+ ''' WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''0x0a''
);';
PRINT @sql;
--EXEC sys.sp_executesql @sql;
I strongly recommend:
not using shorthand for date operations (e.g.GETDATE()-1)
always declaring lengths for variable data types likevarchar.
answered Nov 13 '18 at 13:57
Aaron BertrandAaron Bertrand
208k27363406
Yes. It's work. Thanks. If you have any basic and advance tutorials for Dynamic SQL. Pls send me.
– Hung Nguyen
Nov 14 '18 at 1:34
@HungNguyen I have a good example of how to use dynamic SQL effectively here, and a debugging tip here, but you should also be concerned about SQL injection (see this and this).
– Aaron Bertrand
Nov 14 '18 at 1:38
I really appreciate this. Thanks
– Hung Nguyen
Nov 14 '18 at 2:41
add a comment |
3
3
3
You can't put a variable or an expression there. You'll need to use dynamic SQL.
DECLARE @sql nvarchar(max) = N'BULK INSERT dbo.test FROM '''
+ 'c:test_'
+ REPLACE(CONVERT(char(11), DATEADD(DAY,-1,GETDATE()), 13),' ','')
+ ''' WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''0x0a''
);';
PRINT @sql;
--EXEC sys.sp_executesql @sql;
I strongly recommend:
not using shorthand for date operations (e.g.GETDATE()-1)
always declaring lengths for variable data types likevarchar.
answered Nov 13 '18 at 13:57
Aaron BertrandAaron Bertrand
208k27363406
You can't put a variable or an expression there. You'll need to use dynamic SQL.
DECLARE @sql nvarchar(max) = N'BULK INSERT dbo.test FROM '''
+ 'c:test_'
+ REPLACE(CONVERT(char(11), DATEADD(DAY,-1,GETDATE()), 13),' ','')
+ ''' WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''0x0a''
);';
PRINT @sql;
--EXEC sys.sp_executesql @sql;
I strongly recommend:
not using shorthand for date operations (e.g.GETDATE()-1)
always declaring lengths for variable data types likevarchar.
answered Nov 13 '18 at 13:57
Aaron BertrandAaron Bertrand
208k27363406
edited Nov 13 '18 at 14:02
answered Nov 13 '18 at 13:57
Aaron BertrandAaron Bertrand
208k27363406
answered Nov 13 '18 at 13:57
Aaron BertrandAaron Bertrand
208k27363406
answered Nov 13 '18 at 13:57
Aaron BertrandAaron Bertrand
208k27363406
208k27363406
Yes. It's work. Thanks. If you have any basic and advance tutorials for Dynamic SQL. Pls send me.
– Hung Nguyen
Nov 14 '18 at 1:34
@HungNguyen I have a good example of how to use dynamic SQL effectively here, and a debugging tip here, but you should also be concerned about SQL injection (see this and this).
– Aaron Bertrand
Nov 14 '18 at 1:38
I really appreciate this. Thanks
– Hung Nguyen
Nov 14 '18 at 2:41
add a comment |
Yes. It's work. Thanks. If you have any basic and advance tutorials for Dynamic SQL. Pls send me.
– Hung Nguyen
Nov 14 '18 at 1:34
@HungNguyen I have a good example of how to use dynamic SQL effectively here, and a debugging tip here, but you should also be concerned about SQL injection (see this and this).
– Aaron Bertrand
Nov 14 '18 at 1:38
I really appreciate this. Thanks
– Hung Nguyen
Nov 14 '18 at 2:41
Yes. It's work. Thanks. If you have any basic and advance tutorials for Dynamic SQL. Pls send me.
– Hung Nguyen
Nov 14 '18 at 1:34
Yes. It's work. Thanks. If you have any basic and advance tutorials for Dynamic SQL. Pls send me.
– Hung Nguyen
Nov 14 '18 at 1:34
@HungNguyen I have a good example of how to use dynamic SQL effectively here, and a debugging tip here, but you should also be concerned about SQL injection (see this and this).
– Aaron Bertrand
Nov 14 '18 at 1:38
@HungNguyen I have a good example of how to use dynamic SQL effectively here, and a debugging tip here, but you should also be concerned about SQL injection (see this and this).
– Aaron Bertrand
Nov 14 '18 at 1:38
I really appreciate this. Thanks
– Hung Nguyen
Nov 14 '18 at 2:41
I really appreciate this. Thanks
– Hung Nguyen
Nov 14 '18 at 2:41
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53282433%2fbulk-insert-from-variable-date-filename-error%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
FROMonly accepts a literal, not an expression (and your syntax isn't correct for that anyway). If you want a variable file name, you'll have to build the wholeBULK INSERTstatement dynamically (REPLACE('BULK INSERT [dbo].[test] FROM $f WITH...', '$f', QUOTENAME('E:test_' + @V_SQL + '.txt', ''''))) andEXECit.– Jeroen Mostert
Nov 13 '18 at 13:52
I've tried like:. but with delimiter is |. It seems not to work
– Hung Nguyen
Nov 14 '18 at 1:36