Insert into using Select - 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 into using Select

Author

Topic

mannahboy
Starting Member

11 Posts

Posted - 2008-01-02 : 19:26:03

Can seem to get it right, can anybody spot what I'm missing?


INSERT INTO dbo.StockMasterUPDATE(GroupCode)
SELECT     GroupCode
FROM         dbo.StockMasterBAK
WHERE     (dbo.StockMasterBAK.StockCode = dbo.StockMasterUPDATE.StockCode)

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-02 : 19:34:02

You need to join to the StockMasterUPDATE table in your SELECT statement.

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

mannahboy
Starting Member

11 Posts

Posted - 2008-01-02 : 19:43:03

what would the syntax of the join be i tried:



INSERT INTO dbo.StockMasterUPDATE
                      (GroupCode)
SELECT     GroupCode
FROM         dbo.StockMasterBAK join  dbo.StockMasterUPDATE
WHERE     (StockCode = dbo.StockMasterUPDATE.StockCode)

but doesnt pass

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-02 : 19:45:47

INSERT INTO dbo.StockMasterUPDATE(GroupCode)
SELECT GroupCode
FROM dbo.StockMasterBAK b
JOIN dbo.StockMasterUPDATE u
ON b.StockCode = u.StockCode

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

mannahboy
Starting Member

11 Posts

Posted - 2008-01-02 : 19:54:39

SQL says that I cannot insert nulls into dbo.StockMasterUPDATE but I have looked in the modify view and allow nulls is checked.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-02 : 20:00:38

GroupCode must be set to NOT NULL in StockMasterUPDATE table. Switch it to NULL.

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

mannahboy
Starting Member

11 Posts

Posted - 2008-01-02 : 20:16:17

It actually looks like another table is related to it, is there a way so that the select part of the statement does not select the null values?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-02 : 20:18:42

Yes. But please show us a data example so that we can be sure to provide you with the correct statement.

Just from what I understand though, it would be WHERE b.GroupCode IS NOT NULL.

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

mannahboy
Starting Member

11 Posts

Posted - 2008-01-02 : 20:51:04

Have tried this:

INSERT INTO dbo.StockMasterUPDATE(GroupCode)
SELECT GroupCode
FROM dbo.StockMasterBAK b
JOIN dbo.StockMasterUPDATE u
ON b.StockCode = u.StockCode
WHERE b.GroupCode IS NOT NULL

But sql returns an error like this:

'Cannot insert the value NULL into column 'ClassInclude' table diabey_db.dbo.StockMasterUPDATE; column does not allow nulls', insert fails.

Below is some sample data.

dbo.StockMasterBAK Table
StockCode GroupCode
--------- ---------
CH001 NULL
CH002 NULL
CH003 NULL
CH004 NULL
CH005 GR34
CH006 GR34
CJ001 GR29
CJ002 GR29
CJ003 GR29
CJ004 GR29

dbo.StockMasterUPDATE Table
StockCode GroupCode
--------- ---------
CH001 NULL
CH002 NULL
CH003 NULL
CH004 NULL
CH005 NULL
CH006 NULL
CH007 NULL
CJ001 NULL
CJ002 NULL
CJ003 NULL
CJ004 NULL

Im trying to get values in StockMasterBAK that are not null to insert into StockMasterUPDATE.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-02 : 20:55:14

I think you want an UPDATE of your table rather than an INSERT:

UPDATE b
SET GroupCode = u.GroupCode
FROM dbo.StockMasterBAK b
JOIN dbo.StockMasterUPDATE u
ON b.StockCode = u.StockCode
WHERE b.GroupCode IS NOT NULL

Next time, please show us this detailed information so that we don't waste time on an INSERT when you really need an UPDATE.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources