insert values in table - 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

General SQL Server Forums

New to SQL Server Programming

insert values in table

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 08:38:06

How to insert values to a table that already present ..
ie copy values from one table to another table that present

I tried this query..]

select * into tbl_amazon_merchant from tbl_master_merchant where merchant='Amazon.com' and updated_date='6/16/2010 12:00:00 AM'
first time it works perfectly.. That creates a table tbl_amazon_merchant next time i got this error

Msg 2714, Level 16, State 6, Line 1
There is already an object named 'tbl_amazon_merchant' in the database.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-17 : 08:39:20

INSERT into tbl_amazon_merchant select * from tbl_master_merchant where merchant='Amazon.com' and updated_date='6/16/2010 12:00:00 AM'

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-17 : 08:39:53

select * into creates table and inserts records both. It gives you error the 2nd time since the table is already created.
You'd need to run inserts using 'insert' syntax the 2nd time.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 08:42:29

I tried this also
INSERT into tbl_amazon_merchant select * from tbl_master_merchant where merchant='Amazon.com' and updated_date='6/16/2010 12:00:00 AM'
But i got this error

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'tbl_amazon_merchant' can only be specified when a column list is used and IDENTITY_INSERT is ON.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-17 : 08:45:43

http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-17 : 08:46:42

Also, Consider if you want to populate the identity column from your select or let it increment by its own.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-17 : 09:56:20

quote:
Originally posted by jafrywilson

I tried this also
INSERT into tbl_amazon_merchant select * from tbl_master_merchant where merchant='Amazon.com' and updated_date='6/16/2010 12:00:00 AM'
But i got this error

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'tbl_amazon_merchant' can only be specified when a column list is used and IDENTITY_INSERT is ON.

INSERT INTO DESTINATION_TABLE (non_identity_column1,non_identity_column2,non_identity_column3,non_identity_column4..upto reqd.no)
SELECT matchingcolumn_1,matchingcolumn_2,matchingcolumn_3,matchingcolumn_4,.. FROM SOURCE_TABLE

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 10:09:22

Tnx for response rohit ..

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 10:12:44

I finished by selecting non identity columns...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources