Simple INSERT INTO question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Simple INSERT INTO question

Author

Topic

Guilth
Starting Member

6 Posts

Posted - 2008-12-12 : 11:46:49

Hi everyone,

I have a "simple" question here.

I'm trying to use a query of this form:

INSERT INTO Table1
SELECT * FROM Table2

Everytime I execute it, I receive this message :

(100000 row(s) affected)

And this, even if the Table2 contains a lot more rows.

Is there a configuration or something in SQL Server 2005 that limits the number of rows to be inserted ? Is it only a limit for that kind of query ?

Is there a different type of query I can use to make it work ?

Thanks for your help, I can't seem to find any solution related to this problem.

Patrick.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-12-12 : 12:04:13

What happens if you do


INSERT INTO Table1
SELECT * FROM Table2

SELECT @@ROWCOUNT AS [Rows Added]

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Guilth
Starting Member

6 Posts

Posted - 2008-12-12 : 12:07:24

The second query returns 100000.

Edit: To give a bit more details, I can add that the 2 tables are really simple tables.

The "Table2" is a table containing 4 columns. The "Table1" is the same thing, but with an idendity column.

They are pretty simple, no connections to other tables, no index, no keys.

In facts its so simple that I don't understand why I can't insert all the rows in a single query. Do I have to do the insert in more than one query ?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-12 : 12:33:16

Can you try with Export/Import Wizard?

Guilth
Starting Member

6 Posts

Posted - 2008-12-12 : 12:45:19

That worked... All the rows were transfered.

Is there a way to see what query was used by the Export/Import Wizard ?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-12 : 12:49:04

It does bulk-copy not row-level insert.

Guilth
Starting Member

6 Posts

Posted - 2008-12-12 : 13:29:12

Ok, but is that of any help in my case ?

Ca we use it inside some Transact-SQL ?

I'm not familiar with bulk-copy, but I saw somewhere that we can use that operation while using a tool...

And i didn't tell it in the first place because all I wanted was to start with a working insert into, but my query include a "where" statement, so is it doable with a bulk-copy ?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-12 : 13:38:41

Yes you can use Query inside Export/Import Wizard and schedule it as SSIS Package.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources