EXPORT DES DONÉES D'UN SERVEUR MSSQL SERVER

<%@LANGUAGE="VBSCRIPT"%>
<% BaseTraitee = "yourdatabase" %>
-- <!--#include file="yourconnexionscript.asp" -->
-- sauvegarde du <%=date%>
-- 
-- 
-- Database: <%=BaseTraitee%>
-- 
-- --------------------------------------------------------
-- 
-- 
<%
Dim myArrayName()
Dim myArrayType()

' -----------------------------
' Requete de lecture des tables
' -----------------------------
sql="SELECT TABLES.name AS TableTraitee FROM dbo.sysobjects as TABLES WHERE TABLES.xtype = 'U'"
set rsrecup = OBJdbConnection.Execute(sql)

do while not rsrecup.eof
	TableTraitee=rsrecup("TableTraitee")
	response.write Vbcrlf & "-- Table traitée : " & TableTraitee & Vbcrlf
	iici=0
	jici=0
	Redim myArrayName(0)
	Redim myArrayType(0)
' ----------------------
' Traitement de la table
' ----------------------

' -----------------------------------------------------------------------------------------------

'response.write "EMPTY [" & BaseTraitee & "].[" & TableTraitee & "];" & Vbcrlf

' --------------------------------
' Requete de lecture des rubriques
' --------------------------------
sql="SELECT COLONNES.name AS C2, TYPES.name AS C3 FROM dbo.syscolumns as COLONNES INNER JOIN dbo.sysobjects as TABLES ON TABLES.ID = COLONNES.ID INNER JOIN dbo.systypes AS TYPES ON TYPES.xtype = COLONNES.xtype WHERE TABLES.xtype = 'U' and TYPES.name<>'sysname' and TYPES.name<>'image' and TABLES.name='" & TableTraitee & "'"
set rsrecupRub = OBJdbConnection.Execute(sql)

' ---------------------------------------------------------------------
' On charge les rubrique dans des tableau pour les lires plusieurs fois
' ---------------------------------------------------------------------
do while not rsrecupRub.eof
	jici=iici+1
	Redim PRESERVE myArrayName(jici)
	myArrayName(iici)=rsrecupRub("C2")
	Redim PRESERVE myArrayType(jici)
	myArrayType(iici)=rsrecupRub("C3")
	iici=jici
	rsrecupRub.movenext
loop
rsrecupRub.Close
NbRub=UBound(myArrayName)

' -----------------------
' Affichage des rubriques
' -----------------------
'response.write "-- Nombre de rubriques : " & NbRub & Vbcrlf
'For iici=0 to UBound(myArrayName)
'	response.write "-- " & myArrayName(iici) & "(" & myArrayType(iici) & ")"
'	if myArrayName(iici)="lvalue" then
'		response.write "(Rubrique non exportée !)"
'	end if
'	response.write Vbcrlf
'Next

' ------------------------------
' Requete de lecture des données
' ------------------------------

Limite=NbRub-1
sql="SELECT * FROM " & TableTraitee
set rsrecupVal = OBJdbConnection.Execute(sql)

' ------------------------------------------------
' Pour ne pas s'occuper des clef auto-incrementées
' ------------------------------------------------
response.write "SET IDENTITY_INSERT [" & BaseTraitee & "].[" & TableTraitee & "] ON;" & Vbcrlf

jici = 0
do while not rsrecupVal.eof
	strrub=""
	strval=""

' --------------------------------
' Création de la ligne d'insertion
' --------------------------------
	For iici=0 to Limite
		if myArrayType(iici)= "float" then
			valeur=Replace("" & rsrecupVal(myArrayName(iici)),",",".")
		elseif (myArrayType(iici)="datetime" and IsDate(rsrecupVal(myArrayName(iici)))) then
			' On convertie la date au format iso AAAA-MM-DD ...
			d=rsrecupVal(myArrayName(iici))
			valeur=Year(d)&"-"&Right(Cstr(Month(d)+100),2)&"-"&Right(Cstr(Day(d)+100),2) & Right(d,9)
			'merci à Guiliano Sauro dont je me suis inspiré pour cette ligne
		else
			valeur=Replace(Replace("" & rsrecupVal(myArrayName(iici)),Vbcrlf,"\n")," ' ", " ' ' ") 
		end if
		strrub=strrub & "[" & myArrayName(iici) & "]"
		strval=strval & "'" & valeur & "'"
		if iici<Limite then
			strrub=strrub & ","
			strval=strval & ","
		end if
	Next

' ---------------
' Tout ca pour ca
' ---------------
	str="INSERT INTO [" & BaseTraitee & "].[" & TableTraitee & "] (" & strrub & ") VALUES (" & strval & ");" & Vbcrlf
	jici=jici+1
	response.write str
	rsrecupVal.movenext
loop

' -------------------------------------------------
' On rétablie la gestion des clef auto-incrementées
' -------------------------------------------------
response.write "SET IDENTITY_INSERT [" & BaseTraitee & "].[" & TableTraitee & "] OFF;" & Vbcrlf

response.write "-- Nombre de lignes : " & jici & Vbcrlf
' -----------------------------------------------------------------------------------------------
' -------------------
' Changement de table
' -------------------
	rsrecup.movenext
loop
%>