mc3SftZw

1IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gs_author_pub]') AND type in (N'U'))
2    DROP TABLE [dbo].[gs_author_pub] 
3GO
4
5IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gs_author]') AND type in (N'U'))
6    DROP TABLE [dbo].[gs_author]
7GO
8
9IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[gs_publication]') AND type in (N'U'))
10    DROP TABLE [dbo].[gs_publication]
11GO
12
13IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stage_author]') AND type in (N'U'))
14    DROP TABLE [dbo].[stage_author]
15GO
16
17CREATE TABLE [gs_publication] (
18    [pub_id] numeric(20,0) NOT NULL PRIMARY KEY,
19    [Title] nvarchar(1000),
20    [URL] nvarchar(1000),
21    [NoOfCitings] int,
22    [Venue_Name] nvarchar(1000),
23    [Venue_Hostname] nvarchar(1000),
24    [Venue_Year] int
25)
26
27CREATE TABLE [gs_author] (
28    [author_id] numeric(20,0) NOT NULL PRIMARY KEY,
29    [author_name] nvarchar(1000),
30)
31
32CREATE TABLE [gs_author_pub] (
33    [author_id] numeric(20,0) NOT NULL,
34    [pub_id] numeric(20,0) NOT NULL 
35    CONSTRAINT fk_gs_author_publication1 FOREIGN KEY(pub_id) REFERENCES [gs_publication](pub_id),
36    CONSTRAINT fk_gs_author_publication2 FOREIGN KEY(author_id) REFERENCES [gs_author](author_id)
37)
38
39CREATE TABLE stage_author
40(   [author_id] numeric(20,0) IDENTITY(1,1) NOT NULL,
41    [publication_id] numeric(20,0),
42    [author] nvarchar(1000)
43)
44
45declare @field1 numeric(20,0)
46declare @field2 nvarchar(1000)
47declare cur CURSOR LOCAL for
48    select [id], [Author] from [dwhprak12].[dbo].[OLE DB-Ziel GS Author]
49
50open cur
51
52fetch next from cur into @field1, @field2
53
54
55while @@FETCH_STATUS = 0 BEGIN
56
57		INSERT INTO stage_author SELECT @field1, * FROM dbo.splitstring((SELECT [Author] as col1 FROM [dwhprak12].[dbo].[OLE DB-Ziel GS Author] WHERE [id]= @field1))
58
59
60    fetch next from cur into @field1, @field2
61END
62
63close cur
64deallocate cur
65
66INSERT INTO [gs_author] SELECT [author_id],[author] FROM [stage_author] 
67
68INSERT INTO [gs_publication] SELECT [id],[Title],[URL],[NoOfCitings], [Venue_Name], [Venue_Hostname], [Venue_Year] FROM [OLE DB-Ziel GS Pub Additional] 
69
70INSERT INTO [gs_author_pub] SELECT [author_id], [publication_id] FROM [stage_author]