SQL/GenerateInsertScript.sql at master · cjfaulkner/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
SETNOCOUNTON

DECLARE @TableName varchar(100), @WhereClause varchar(8000), @IDColumn bit

SET @TableName ='PortfolioDetails'
SET @WhereClause ='PortfolioID = 10719321'

DECLARE @InsertString varchar(8000), @LastCol int


IFEXISTS(SELECT1FROM	syscolumns c INNER JOIN sysobjects o ONc.id=o.idWHEREo.name= @TableName ANDc.ColStat=1)
SET @IDColumn =1
ELSE
SET @IDColumn =0

IF @IDColumn =1
SELECT @InsertString ='SELECT ''SET IDENTITY_INSERT '+ @TableName +' ON '+CHAR(13) +CHAR(10) +'INSERT INTO '+ @TableName +' ('
ELSE
SELECT @InsertString ='SELECT ''INSERT INTO '+ @TableName +' ('


SELECT @LastCol =Max(c.ColID)
FROM	syscolumns c
INNER JOIN sysobjects o
ONc.id=o.id
WHEREo.name= @TableName


SELECT @InsertString = @InsertString +c.name+Casec.ColId
WHEN @LastCol THEN')'
ELSE', '
End
FROM	syscolumns c
INNER JOIN sysobjects o
ONc.id=o.id
WHEREo.name= @TableName
ORDER BYc.ColId


SELECT @InsertString = @InsertString +' VALUES ('' + '

SELECT @InsertString = @InsertString +
CASE
WHENt.NameIN ('char', 'varchar', 'nchar', 'nvarchar') THEN'IsNull('+
CASE
WHENc.Length<2THEN'convert(varchar(4), '
ELSE''
END+
''''''''' + '+c.name+' + '''''''''+
CASE

WHENc.Length<2THEN')'
ELSE''
END+', ''NULL'')'
WHENt.nameIN ('binary', 'varbinary') THEN
'''convert('+t.name+'('+convert(varchar(5), c.Length) +'), '''''' + convert(varchar(255), '+c.name+') + '''''')'''
WHENt.nameIN ('datetime', 'smalldatetime') THEN
'IsNull('''''''' + convert(varchar(20), '+c.name+') + '''''''', ''NULL'')'
ELSE'IsNull(convert(varchar(50), '+c.name+'), ''NULL'')'
End
+Casec.ColId
WHEN @LastCol THEN'+ '')'''
ELSE'+ '', '' + '
End
FROM	syscolumns c
INNER JOIN systypes t
ONc.xtype=t.xtype
INNER JOIN sysobjects o
ONc.id=o.id
WHEREo.name= @TableName
ORDER By ColID


IF @IDColumn =1
SELECT @InsertString = @InsertString +' + CHAR(13) + CHAR(10) + ''SET IDENTITY_INSERT '+ @TableName +' OFF '''

SET @InsertString = @InsertString +' FROM '+ @TableName +IsNull(' WHERE '+ @WhereClause, '')

EXEC(@InsertString)

SETNOCOUNTOFF