transferring or replacing data from excel into sql table

Question

Post reply

transferring or replacing data from excel into sql table

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

mfowler12 SSC Veteran Points: 246 More actions · Answer 1

I've resolved Line 32.

I still don't see what is wrong with Line 16???

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Pull the comma off the end of this line:

[STATE] = xl.[STATE],

Should be:

[STATE] = xl.[STATE]

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mfowler12 SSC Veteran Points: 246 More actions · Answer 3

Line 15 had an extra comma so that resolved Line 16's error.

Now I'm getting a Msg 4104 on line 27, "The multi-part identifier "TestTable.ID" could not be bound." This error happens twice. I added brackets to TestTable on line 27.

SET IDENTITY_INSERT dbo.TestTable ON; -- Line 26

INSERT INTO dbo.[TestTable] (

[ID], [BENCHMARK], [YOUR_STATES_JOB_TITLE], [Number_of_INC],

[AVG_SALARY], [PAY_RANGE_MIN], [PAY_RANGE_MIDPT],

[PAY_RANGE_MAX], [PERCENTILE_25TH], [MEDIAN],

[PERCENTILE_75TH], [MATCH_LEVEL], [CLASS_LEVEL],

[STATE] )

SELECT Sheet1$.[ID], Sheet1$.[BENCHMARK], Sheet1$.[YOUR_STATES_JOB_TITLE],

Sheet1$.[Number_of_INC], Sheet1$.[AVG_SALARY], Sheet1$.[PAY_RANGE_MIN],

Sheet1$.[PAY_RANGE_MIDPT], Sheet1$.[PAY_RANGE_MAX], Sheet1$.[PERCENTILE_25TH],

Sheet1$.[MEDIAN], Sheet1$.[PERCENTILE_75TH], Sheet1$.[MATCH_LEVEL],

Sheet1$.[CLASS_LEVEL], Sheet1$.[STATE]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Please post the FROM clauses, it sounds like the old version of:

FROM dbo.TestTable survey

JOIN dbo.[Sheet1$] xl

ON survey.ID = xl.ID

Turned into something like:

FROM dbo.TestTable survey

JOIN dbo.[Sheet1$] xl

ON TestTable.ID = xl.ID

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mfowler12 SSC Veteran Points: 246 More actions · Answer 5

Craig Farrell (9/2/2010)
Please post the FROM clauses, it sounds like the old version of:
FROM dbo.TestTable survey
JOIN dbo.[Sheet1$] xl
ON survey.ID = xl.ID
Turned into something like:
FROM dbo.TestTable survey
JOIN dbo.[Sheet1$] xl
ON TestTable.ID = xl.ID

My current query.

-- update the existing entries based on the ID column.

UPDATE TestTable

SET [BENCHMARK] = Sheet1$.[BENCHMARK],

[YOUR_STATES_JOB_TITLE] = Sheet1$.[YOUR_STATES_JOB_TITLE],

[Number_of_INC] = Sheet1$.[Number_of_INC],

[AVG_SALARY] = Sheet1$.[AVG_SALARY],

[PAY_RANGE_MIN] = Sheet1$.[PAY_RANGE_MIN],

[PAY_RANGE_MIDPT] = Sheet1$.[PAY_RANGE_MIDPT],

[PAY_RANGE_MAX] = Sheet1$.[PAY_RANGE_MAX],

[PERCENTILE_25TH] = CONVERT(money, Sheet1$.[PERCENTILE_25TH]),

[MEDIAN] = CONVERT(money, Sheet1$.[MEDIAN]),

[PERCENTILE_75TH] = CONVERT(money, Sheet1$.[PERCENTILE_75TH]),

[MATCH_LEVEL] = Sheet1$.[MATCH_LEVEL],

[CLASS_LEVEL] = Sheet1$.[CLASS_LEVEL],

[STATE] = Sheet1$.[STATE]

FROM dbo.[TestTable]

JOIN dbo.[Sheet1$]

ON TestTable.ID = Sheet1$.ID;

-- INSERT data not already in the table, based on the ID column.

-- if you want to NOT use the ID column in the spreadsheet:

-- 1. remark out both of the SET IDENTITY_INSERT statements.

-- 2. remove the ID column from the insert column list.

-- 3. remove the ID column from the select column list.

SET IDENTITY_INSERT dbo.TestTable ON;

INSERT INTO dbo.[TestTable] (

[ID], [BENCHMARK], [YOUR_STATES_JOB_TITLE], [Number_of_INC],

[AVG_SALARY], [PAY_RANGE_MIN], [PAY_RANGE_MIDPT],

[PAY_RANGE_MAX], [PERCENTILE_25TH], [MEDIAN],

[PERCENTILE_75TH], [MATCH_LEVEL], [CLASS_LEVEL],

[STATE] )

SELECT Sheet1$.[ID], Sheet1$.[BENCHMARK], Sheet1$.[YOUR_STATES_JOB_TITLE],

Sheet1$.[Number_of_INC], Sheet1$.[AVG_SALARY], Sheet1$.[PAY_RANGE_MIN],

Sheet1$.[PAY_RANGE_MIDPT], Sheet1$.[PAY_RANGE_MAX], Sheet1$.[PERCENTILE_25TH],

Sheet1$.[MEDIAN], Sheet1$.[PERCENTILE_75TH], Sheet1$.[MATCH_LEVEL],

Sheet1$.[CLASS_LEVEL], Sheet1$.[STATE]

FROM dbo.[Sheet1$]

LEFT JOIN dbo.TestTable survey

ON Sheet1$.ID = TestTable.ID

WHERE TestTable.ID IS NULL;

SET IDENTITY_INSERT dbo.TestTable OFF;

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Near the end:

WHERE TestTable.ID IS NULL;

Should be:

WHERE survey.ID IS NULL;

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mfowler12 SSC Veteran Points: 246 More actions · Answer 7

Craig Farrell (9/2/2010)
Near the end:
WHERE TestTable.ID IS NULL;
Should be:
WHERE survey.ID IS NULL;

I bet I've messed up the code then because I changed a lot of the survey's to TestTable and the xl's to Sheet1$

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

Quite possible. Here's the rule:

If you remove the alias in the FROM clause (it looks like [Sheet$1] AS xl or just [Sheet$1] xl) then you need to reference the table name everywhere. If it is aliased, you need to reference the alias everywhere. So if you pull the xl part off in the FROM clause, everywhere there's xl.<field> needs to become [Sheet$1].<field>. If you add the xl, reverse that.

Edit:

Also just noticed:

FROM dbo.[Sheet1$]

LEFT JOIN dbo.TestTable survey

ON Sheet1$.ID = TestTable.ID

Needs to Be:

FROM dbo.[Sheet1$]

LEFT JOIN dbo.TestTable survey

ON Sheet1$.ID = survey.ID

Rest looked alright when I scanned it.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mfowler12 SSC Veteran Points: 246 More actions · Answer 9

That's what thought I did. I double checked and it went through easily. Updated the 261 records. Awesome!

Thanks Wayne and Craig for your help and patience. This is my first production SQL server to admin so I appreciate your guidance!

WayneS SSC Guru Points: 95460 More actions · Answer 10

Glad you got it working... and I'm sorry that I had to disappear (time to drive home to avoid the hurricane).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 11

Rick.Cornell.SME

SSCrazy Eights

Points: 8597

September 3, 2010 at 8:53 am

#1216212

I am also trying to avoid the Cane this weekend too...