use Books
--判断是否有存在表
if OBJECT_ID('dbo.books','U') is not null drop table dbo.books
--创建表存储
create table books(
id int identity(1,1) primary key,
name varchar(150) not null,
price float not null,
stock int not null
--添加数据
insert into books(name,price,stock)values
('Access入门实战',49.5,999),
('T-SQL性能调优秘笈',49.0,999),
('.NET MVC5 高级变成',79.8,999),
('Python 入门实战',89.00,999);
--检查添加数据
select * from books;
postgreSQL中添加同结构数据表
编写存储过程
use Books
if OBJECT_ID('Insert_Books','P') is not null drop procedure dbo.Insert_Books
--添加插入存储过程
CREATE PROCEDURE Insert_Books
@name varchar(100),@price float,@stock int
BEGIN
SET NOCOUNT ON;
insert openquery(POSTGRESQL,'select name,price,stock from books where 1=0')(name,price,stock) values
(@name,@price,@stock);
SET NOCOUNT ON;
添加触发器
--创建添加触发器
CREATE TRIGGER insert_trigger
ON Books.dbo.books
AFTER INSERT
BEGIN
declare @name varchar(150),@price float,@stock int
select @name=name,@price=price,@stock=stock from inserted
SET NOCOUNT ON;
exec LOCALHOSTSQL.[books].[dbo].[Insert_Books] @name,@price,@stock
-- Insert statements for trigger here
同步SQLServer 数据库中的 数据至PostgreSQL
insert openquery(POSTGRESQL,'select name,price,stock from books where 1=0')
select name,price,stock from books
postgresql数据中
测试添加数据
insert into books(name,price,stock) values('代码整洁之道',56.3,623)
select * from books
