Issue with Insert - 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

Issue with Insert

Author

Topic

ashgupta
Starting Member

17 Posts

Posted - 2009-01-14 : 19:21:44

Hi All I having huge issues with inserting data from one table into another, both the tables are of same structure.
But only half of data is inserted into new table from old table.

any reason why .

When i do a slect on the original table it gives all the records, how ever when i inset from original table to new table it inserts random amount of records

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-14 : 19:22:37

You haven't provided the code that you used to insert the data nor any sample data, so it's hard to provide any reason for your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ashgupta
Starting Member

17 Posts

Posted - 2009-01-14 : 19:37:27

INSERT INTO [Tbl_New]

SELECT [Tbl_Original].* FROM [Tbl_Original] LEFT JOIN [Tbl_New]
ON [Tbl_Original].[Number] = [Tbl_New].[Number]
WHERE [Tbl_New].[Number] IS NULL AND [Tbl_Original].[Number] = '1'

there few thousand of records for each [Tbl_New].[Number]

issue is it might do it for '1' or '2' but when it come to '3' it could fail with insert

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-14 : 19:52:34

If there are missing rows in Tbl_New, then your SELECT query is wrong. You'll need to show us sample data in order for us to help further.

I would probably just do it this way:

INSERT INTO [Tbl_New]
SELECT *
FROM [Tbl_Original] t1
WHERE NOT EXISTS (SELECT * FROM [Tbl_New] t2 WHERE t1.[Number] = t2.[Number])

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-14 : 19:55:43

Either records exist in other table
or try this:


INSERT INTO [Tbl_New]

SELECT [Tbl_Original].* FROM [Tbl_Original] LEFT JOIN [Tbl_New] 
ON [Tbl_Original].[Number] = [Tbl_New].[Number] AND [Tbl_Original].[Number] in(1,2,3)
WHERE [Tbl_New].[Number] IS NULL

quote:
Originally posted by ashgupta

INSERT INTO [Tbl_New]

SELECT [Tbl_Original].* FROM [Tbl_Original] LEFT JOIN [Tbl_New]
ON [Tbl_Original].[Number] = [Tbl_New].[Number]
WHERE [Tbl_New].[Number] IS NULL AND [Tbl_Original].[Number] = '1'

there few thousand of records for each [Tbl_New].[Number]

issue is it might do it for '1' or '2' but when it come to '3' it could fail with insert

ashgupta
Starting Member

17 Posts

Posted - 2009-01-14 : 20:37:23

Thanks heaps.
I will modify my query and do a test on it

divyagr
Starting Member

29 Posts

Posted - 2009-01-15 : 04:14:44

hi all,
im not getting why '[tbl_new].[number ]' is used in this code.....

with regards,
Divya

divyagr
Starting Member

29 Posts

Posted - 2009-01-15 : 04:22:08

hi,

we can do like this .....if the table structure is small

insert into [tblnew] select col1,col2 from [tblold]

with regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 07:09:06

quote:
Originally posted by divyagr

hi all,
im not getting why '[tbl_new].[number ]' is used in this code.....

with regards,
Divya

because you want to avoid records present in [tbl_new].[number ]. The left join along with condition [Tbl_New].[Number] IS NULL does that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources