Inserting into an Table with Identity Column from n number of tables

Question

Post reply

Inserting into an Table with Identity Column from n number of tables

Hakuna Matata

SSCertifiable

Points: 5577
More actions
June 10, 2011 at 6:18 am

#239718

Hi,
The issue I have is that I have created a Table with Identity option set for the first column. I need to populate this table from a query that fetches data from 'n' number of tables. How can I do this .
When I tried to insert data I got the following error:
"Msg 120, Level 15, State 1, Line 2
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
"
Regards,
Nithin

Viewing 14 posts - 1 through 13 (of 13 total)

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

calvo SSChampion Points: 12930 More actions · Answer 1

That error is pretty self explanatory.

How about posting the script you're trying to run?

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 2

Thanks for the help provided. I figured out the problem. There was data type mismatch , hence the issue.

I have another issue:

I want to update only those values from the query that does not match the data in the table.

For example if the query returns Invoices 1-100 & corresponding details for those invoices. And the Table is populated upto invoice no 50. I need to update only data for invoices ranging from 50-100(sort of incremental updating).

How can I do this?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

EXCEPT or WHERE NOT IN or WHERE NOT EXISTS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 4

kr.nithin (6/10/2011)
Thanks for the help provided. I figured out the problem. There was data type mismatch , hence the issue.
I have another issue:
I want to update only those values from the query that does not match the data in the table.
For example if the query returns Invoices 1-100 & corresponding details for those invoices. And the Table is populated upto invoice no 50. I need to update only data for invoices ranging from 50-100(sort of incremental updating).
How can I do this?

In addition to the ways that Jeff mentioned, you can also use the new MERGE statement - it can insert, update and delete all in one pass.

Also, are you mixing update and insert?

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

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 5

Hakuna Matata

SSCertifiable

Points: 5577

June 11, 2011 at 5:59 am

#1337368

Thank you Guys for the timely advise.

:-):-):-):-)

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 6

I have another problem.

I need to do this task on different servers. The query fetches the data from my Production Server & I need to update the Table created on my Report Server. How can I do this.

Both the servers are on the same network.

I need to create a job that will automate this process everyday.

Regards,

Nithin

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

kr.nithin (6/11/2011)
I have another problem.
I need to do this task on different servers. The query fetches the data from my Production Server & I need to update the Table created on my Report Server. How can I do this.
Both the servers are on the same network.
I need to create a job that will automate this process everyday.
Regards,
Nithin

~~You might want to look into "Replication" then.~~

{edit} Nah... scratch that idea. You said you want to get data from multiple tables and insert into just one. You may want to consider building a "partitioned view" as a "source" and then using one of the "merge" methods that's already been suggested. Once you have that down pat as a stored procedure, just create a job to execute the stored procedure daily.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Btw... if the "partitioned view" ended up containing ALL of the rows you want, I wouldn't waste my time doing a "merge". I'd build another table from the view with ALL the rows and, once it's been verified as correct, I'd just do a "cascade rename" to replace the table. The renaming will take only milliseconds.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 9

The multiple tables are present on one server & the reporting table is on a different server. Can I update from one server to another. Both the servers are on the same network.

Jayanth Kurup SSC Eights! Points: 829 More actions · Answer 10

Since you performing two different types of activities here you might want to consider the following.

The report needs to be real time ? then you answer is Replication , log shipping , even linked server if its not a customer facing db.

The report directly queries off prod tables or are u performing ETL to a DW.

Can you use SSIS or do you need to write some custom code like bcp via batch files etc.

For obvious reasons you should try never to use an OLTP database back end as the data source for your reports.

The effort put into performing good ETL will save you the trouble in future when more reports are required and the underlying system is going thru significant change. The ability to clean data is also important for most reports.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

JAYANTH KURUP (6/13/2011)
For obvious reasons you should try never to use an OLTP database back end as the data source for your reports.

"Never" is a pretty strong word here. I say "It Depends" on what the report is, how much data it hits, who wrote the report query, etc, etc. Done properly, there's usually no reason why reporting queries can't or shouldn't be executed against OLTP systems.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 12

Jayanth_Kurup

SSC-Insane

Points: 22967

June 16, 2011 at 12:04 am

#1339477

Your right 😀 , it depends.

Jayanth Kurup[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Ok... will the real "Jayanth_Kurup" please stand up? Why two separate logins or are there really two people with the same name on this thread?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)