February 2, 2011 at 2:28 pm
I am trying to insert records into a Pervasive database from SQL Server. In SQL Server I have a table containing data to be inserted, sched_repair. The destination in Pervasive is sched. The linked server is Test342011D.
This select works fine so I am sure the connection is good.
SELECT * FROM openquery(Test342011D, 'SELECT * from sched where loc_no=1001')
When I try to execute this insert I get an error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
DECLARE @sql1 VARCHAR(MAX)
SET @sql1 = 'INSERT openquery(Test342011D, ''SELECT * from sched'') VALUES (SELECT * FROM sched_repair) '
select @sql1
EXEC(@sql1)
Can anyone tell me where my problem is? I have inserted records into Pervasive before but the VALUES were always simple variables, this time I would like to insert the entire sched_repair table.
Thanks very much.
February 2, 2011 at 4:51 pm
DECLARE @sql1 VARCHAR(MAX)
SET @sql1 = 'INSERT openquery(Test342011D, ''SELECT * from sched'') SELECT * FROM sched_repair '
select @sql1
EXEC(@sql1)
"VALUES" is only used for one row of constants/variables.
I would also recommend that you specify the column names in both SELECT statements.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 3, 2011 at 6:09 am
Thank you, I guess I will just have to loop through the source one at a time.
February 3, 2011 at 6:31 am
Sorry I don't see why you need to Loop one row at a time.
I am sure if you explain what you are trying to do someone will come up with a set based answer (as you don't seem happy with the set based one I gave...)!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 3, 2011 at 11:26 am
Hi MM, thank you for your reply. I did try specifying all column names in both SELECTs but I got the same error. That's why I figured I would have to loop through the sched_repair table and insert all columns one row at a time into the Pervasive table.
For background, the application that uses the Pervasive database allows a user to delete a schedule and someone incorrectly deleted a schedule. Nightly, I refresh my development data and it contained the deleted schedule, all 53 rows, in the sched table. I pulled the development data out and saved it into a SQL Server table, sched_repair. I then wanted to insert all 53 rows of the SQL Server table, sched_repair, into production's Pervasive table, sched. In my few years here this is the first time I have had the need to insert more than one record at a time but it would be nice to have that ability.
February 3, 2011 at 11:32 am
Did you read the code I posted - that will do what you want....?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply