CSV Import into SQL Server table(s)
0
I have a spreadsheet which is laid out
ITEM NAME TAG1 TAG2 TAG3
Hats X X
Socks X X
Pants X X
and I have 3 DB tables
- Items - PK_ItemId
- Tags - PK_TagId
- ItemTags - PK_ItemId_TagId
How can I import the data from the spreadsheet into the correct mapping in ItemTags where the column has an X?
sql-server excel edited Nov 12 '18 at 5:07
marc_s
577k12911141259
asked Nov 12 '18 at 0:26
jonahpupjonahpup
5010
|
show 1 more comment
0
I have a spreadsheet which is laid out
ITEM NAME TAG1 TAG2 TAG3
Hats X X
Socks X X
Pants X X
and I have 3 DB tables
- Items - PK_ItemId
- Tags - PK_TagId
- ItemTags - PK_ItemId_TagId
How can I import the data from the spreadsheet into the correct mapping in ItemTags where the column has an X?
sql-server excel edited Nov 12 '18 at 5:07
marc_s
577k12911141259
asked Nov 12 '18 at 0:26
jonahpupjonahpup
5010
How many possible TAG columns are there? Where does the data come from - is it manually entered into the spreadsheet? What have you tried so far? What platform are you comfortable developing in?
– Nick.McDermaid
Nov 12 '18 at 0:33
All the data is manually entered into the spreadsheet (as in the X in the appropriate column)... The columns, they come from the DB and are a fixed number of columns/tag (12). Likewise the item names are from the db also. I tried importing the csv into a temp table including the first row as headers, which worked fine, but the only way I can see to get the data into the relevant tables, is with a really long ugly query with a bunch of case statements for each column, but would like something I can re-use later preferably, so need to be able to do it on the fly, looking up the ID's as we go
– jonahpup
Nov 12 '18 at 0:37
I could do this easily in C#, but was hoping to do it all SqlServer side
– jonahpup
Nov 12 '18 at 0:39
1
That is exactly what I would recommend - import into a staging table then update from that. In coding terms it's no uglier than the C# code that you would need to do it. The main risk is when a new tag appears.In this case C# probably has a bit of an edge.
– Nick.McDermaid
Nov 12 '18 at 0:51
Thanks @Nick.McDermaid I was just hoping there was a nicer way to do it
– jonahpup
Nov 12 '18 at 0:58
|
show 1 more comment
0
0
0
I have a spreadsheet which is laid out
ITEM NAME TAG1 TAG2 TAG3
Hats X X
Socks X X
Pants X X
and I have 3 DB tables
- Items - PK_ItemId
- Tags - PK_TagId
- ItemTags - PK_ItemId_TagId
How can I import the data from the spreadsheet into the correct mapping in ItemTags where the column has an X?
sql-server excel edited Nov 12 '18 at 5:07
marc_s
577k12911141259
asked Nov 12 '18 at 0:26
jonahpupjonahpup
5010
I have a spreadsheet which is laid out
ITEM NAME TAG1 TAG2 TAG3
Hats X X
Socks X X
Pants X X
and I have 3 DB tables
- Items - PK_ItemId
- Tags - PK_TagId
- ItemTags - PK_ItemId_TagId
How can I import the data from the spreadsheet into the correct mapping in ItemTags where the column has an X?
sql-server excel sql-server excel edited Nov 12 '18 at 5:07
marc_s
577k12911141259
asked Nov 12 '18 at 0:26
jonahpupjonahpup
5010
edited Nov 12 '18 at 5:07
marc_s
577k12911141259
asked Nov 12 '18 at 0:26
jonahpupjonahpup
5010
edited Nov 12 '18 at 5:07
marc_s
577k12911141259
edited Nov 12 '18 at 5:07
marc_s
577k12911141259
edited Nov 12 '18 at 5:07
marc_s
577k12911141259
577k12911141259
asked Nov 12 '18 at 0:26
jonahpupjonahpup
5010
asked Nov 12 '18 at 0:26
jonahpupjonahpup
5010
asked Nov 12 '18 at 0:26
jonahpupjonahpup
5010
5010
How many possible TAG columns are there? Where does the data come from - is it manually entered into the spreadsheet? What have you tried so far? What platform are you comfortable developing in?
– Nick.McDermaid
Nov 12 '18 at 0:33
All the data is manually entered into the spreadsheet (as in the X in the appropriate column)... The columns, they come from the DB and are a fixed number of columns/tag (12). Likewise the item names are from the db also. I tried importing the csv into a temp table including the first row as headers, which worked fine, but the only way I can see to get the data into the relevant tables, is with a really long ugly query with a bunch of case statements for each column, but would like something I can re-use later preferably, so need to be able to do it on the fly, looking up the ID's as we go
– jonahpup
Nov 12 '18 at 0:37
I could do this easily in C#, but was hoping to do it all SqlServer side
– jonahpup
Nov 12 '18 at 0:39
1
That is exactly what I would recommend - import into a staging table then update from that. In coding terms it's no uglier than the C# code that you would need to do it. The main risk is when a new tag appears.In this case C# probably has a bit of an edge.
– Nick.McDermaid
Nov 12 '18 at 0:51
Thanks @Nick.McDermaid I was just hoping there was a nicer way to do it
– jonahpup
Nov 12 '18 at 0:58
|
show 1 more comment
How many possible TAG columns are there? Where does the data come from - is it manually entered into the spreadsheet? What have you tried so far? What platform are you comfortable developing in?
– Nick.McDermaid
Nov 12 '18 at 0:33
All the data is manually entered into the spreadsheet (as in the X in the appropriate column)... The columns, they come from the DB and are a fixed number of columns/tag (12). Likewise the item names are from the db also. I tried importing the csv into a temp table including the first row as headers, which worked fine, but the only way I can see to get the data into the relevant tables, is with a really long ugly query with a bunch of case statements for each column, but would like something I can re-use later preferably, so need to be able to do it on the fly, looking up the ID's as we go
– jonahpup
Nov 12 '18 at 0:37
I could do this easily in C#, but was hoping to do it all SqlServer side
– jonahpup
Nov 12 '18 at 0:39
1
That is exactly what I would recommend - import into a staging table then update from that. In coding terms it's no uglier than the C# code that you would need to do it. The main risk is when a new tag appears.In this case C# probably has a bit of an edge.
– Nick.McDermaid
Nov 12 '18 at 0:51
Thanks @Nick.McDermaid I was just hoping there was a nicer way to do it
– jonahpup
Nov 12 '18 at 0:58
How many possible TAG columns are there? Where does the data come from - is it manually entered into the spreadsheet? What have you tried so far? What platform are you comfortable developing in?
– Nick.McDermaid
Nov 12 '18 at 0:33
How many possible TAG columns are there? Where does the data come from - is it manually entered into the spreadsheet? What have you tried so far? What platform are you comfortable developing in?
– Nick.McDermaid
Nov 12 '18 at 0:33
All the data is manually entered into the spreadsheet (as in the X in the appropriate column)... The columns, they come from the DB and are a fixed number of columns/tag (12). Likewise the item names are from the db also. I tried importing the csv into a temp table including the first row as headers, which worked fine, but the only way I can see to get the data into the relevant tables, is with a really long ugly query with a bunch of case statements for each column, but would like something I can re-use later preferably, so need to be able to do it on the fly, looking up the ID's as we go
– jonahpup
Nov 12 '18 at 0:37
All the data is manually entered into the spreadsheet (as in the X in the appropriate column)... The columns, they come from the DB and are a fixed number of columns/tag (12). Likewise the item names are from the db also. I tried importing the csv into a temp table including the first row as headers, which worked fine, but the only way I can see to get the data into the relevant tables, is with a really long ugly query with a bunch of case statements for each column, but would like something I can re-use later preferably, so need to be able to do it on the fly, looking up the ID's as we go
– jonahpup
Nov 12 '18 at 0:37
I could do this easily in C#, but was hoping to do it all SqlServer side
– jonahpup
Nov 12 '18 at 0:39
I could do this easily in C#, but was hoping to do it all SqlServer side
– jonahpup
Nov 12 '18 at 0:39
1
1
That is exactly what I would recommend - import into a staging table then update from that. In coding terms it's no uglier than the C# code that you would need to do it. The main risk is when a new tag appears.In this case C# probably has a bit of an edge.
– Nick.McDermaid
Nov 12 '18 at 0:51
That is exactly what I would recommend - import into a staging table then update from that. In coding terms it's no uglier than the C# code that you would need to do it. The main risk is when a new tag appears.In this case C# probably has a bit of an edge.
– Nick.McDermaid
Nov 12 '18 at 0:51
Thanks @Nick.McDermaid I was just hoping there was a nicer way to do it
– jonahpup
Nov 12 '18 at 0:58
Thanks @Nick.McDermaid I was just hoping there was a nicer way to do it
– jonahpup
Nov 12 '18 at 0:58
|
show 1 more comment
0
active
oldest
votes
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%2f53254603%2fcsv-import-into-sql-server-tables%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53254603%2fcsv-import-into-sql-server-tables%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
How many possible TAG columns are there? Where does the data come from - is it manually entered into the spreadsheet? What have you tried so far? What platform are you comfortable developing in?
– Nick.McDermaid
Nov 12 '18 at 0:33
All the data is manually entered into the spreadsheet (as in the X in the appropriate column)... The columns, they come from the DB and are a fixed number of columns/tag (12). Likewise the item names are from the db also. I tried importing the csv into a temp table including the first row as headers, which worked fine, but the only way I can see to get the data into the relevant tables, is with a really long ugly query with a bunch of case statements for each column, but would like something I can re-use later preferably, so need to be able to do it on the fly, looking up the ID's as we go
– jonahpup
Nov 12 '18 at 0:37
I could do this easily in C#, but was hoping to do it all SqlServer side
– jonahpup
Nov 12 '18 at 0:39
1
That is exactly what I would recommend - import into a staging table then update from that. In coding terms it's no uglier than the C# code that you would need to do it. The main risk is when a new tag appears.In this case C# probably has a bit of an edge.
– Nick.McDermaid
Nov 12 '18 at 0:51
Thanks @Nick.McDermaid I was just hoping there was a nicer way to do it
– jonahpup
Nov 12 '18 at 0:58