asked on

Insert Into Syntax question

I am using Visual Basic 2008 to copy one table to another. Both tables contain an Identity column. I am trying to set the Identiy_Insert to ON but I seem to be having difficulty.

When I copy my code to a New Query Window in SSMS, add the GO statement, it executes fine.

But the VB code below does not execute. No errors are generated but no records are inserted.

Please take a look at my code and let me know what I am missing.

thanks,
pat

ParmString = "truncate table " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory"
                DestinationCmd.CommandText = ParmString
                NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery
                '
                ParmString = "Set Identity_Insert " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory ON "
                NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery

                '
                ParmString = "Insert Into " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory" & _
                "(IDNo, Category, ChildCategory,ParentIDNo,WSWebProductNameIDNo, WSWebPriceTableCellsItemNoIDNo, ItemNo, TopMostLevel) " & _
                "Select IDNo, Category, ChildCategory,ParentIDNo,WSWebProductNameIDNo, WSWebPriceTableCellsItemNoIDNo, ItemNo, TopMostLevel " & _
                "From WeberScientific.dbo.WSWebCategory"
                NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery
                '
                ParmString = "Set Identity_Insert " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory OFF "
                NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery

Open in new window

ASKER CERTIFIED SOLUTION

radcaesar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kaufmed

Don't call ExecuteNonQuery() after the "SET IDENTITY_INSERT" lines. Make the whole thing one query. For example:

cmd.CommandText = "SET IDENTITY_INSERT OFF INSERT INTO myTable VALUES (1,2,3) SET IDENTITY_INSERT OFF"
cmd.ExecuteNonQuery()

Open in new window

Kalyanum Deepak Kumar

Yes, make a consolidated query and run only one ExecuteNonquery(). Thanks.

Se below : Also a GO statement after every SQL statement woule givegood results:

ParmString = "truncate table " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory" &"Set Identity_Insert " &
PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory ON GO" &"Insert Into " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory" & _
"(IDNo, Category, ChildCategory,ParentIDNo,WSWebProductNameIDNo, WSWebPriceTableCellsItemNoIDNo, ItemNo, TopMostLevel) " & _
"Select IDNo, Category, ChildCategory,ParentIDNo,WSWebProductNameIDNo, WSWebPriceTableCellsItemNoIDNo, ItemNo, TopMostLevel " & _
"From WeberScientific.dbo.WSWebCategory GO" & "Set Identity_Insert " & PublicVariables.DestinationDBStringP & ".dbo.WSWebCategory OFF GO"
NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery
DestinationCmd.CommandText = ParmString
NoOfRecordsUpdatedLong = DestinationCmd.ExecuteNonQuery

mpdillon

ASKER

Thank you. The semi colon seperators were what I was missing.
For Others reading this post, I also FORGOT to set the CommandText Property:
DestinationCmd.CommandText = ParmString